Skip to main content

Error Handling

 Formula errors are common. If you have a data set with hundreds of records, a divide-by-zero and an #N/A errors are bound to pop up now and then.

In the past, preventing errors required Herculean efforts. Nod your head knowingly if you’ve ever knocked out =IF(ISNA(VLOOKUP(A2,Table,2,0),"Not Found",VLOOKUP(A2,Table,2,0)). Besides being really long to type, that solution requires twice as many VLOOKUPs. First, you do a VLOOKUP to see if the VLOOKUP is going to produce an error. Then you do the same VLOOKUP again to get the non-error result.

Excel 2010 introduced the greatly improved =IFERROR(Formula,Value If Error). I know that IFERROR sounds like the old ISERROR, ISERR, and ISNA functions, but it is completely different.

This is a brilliant function: =IFERROR(VLOOKUP(A2,Table,2,0),"Not Found"). If you have 1,000 VLOOKUPs and only 5 return #N/A, then the 995 that worked require only a single VLOOKUP. Only the 5 VLOOKUPs returned #N/A that need to move on to the second argument of IFERROR.

Oddly, Excel 2013 added the IFNA() function. It is just like IFERROR but only looks for #N/A errors. One might imagine a strange situation where the value in the lookup table is found, but the resulting answer is a division by 0. If you want to preserve the divide-by-zero error for some reason, you can use IFNA() to do this.

A formula of =IFNA(VLOOKUP(),"Not Found") makes sure that you never see a #N/A error.

Of course, the person who built the lookup table should have used IFERROR to prevent the division by zero in the first place. In the figure below, the "n.m." is a former manager’s code for “not meaningful.”

The #DIV/0 error is changed to "n.m." by using =IFERROR(F9/E9,"n.m.")
Labels:

Comments

Post a Comment

Popular posts from this blog

Year Over Year calculation using Pivot Table

Instead of creating a formula outside of the pivot table, you can do this inside the pivot table. Start from the image with column D empty. Drag Revenue a second time to the Values area. Look in the Columns section of the Pivot Table Fields panel. You will see a tile called Values that appears below Date. Drag that tile so it is below the Date field. Your pivot table should look like this: Double-click the Sum of Revenue2 heading in D4 to display the Value Field Settings dialog. Click on the tab for Show Values As. Change the drop-down menu to % Difference From. Change the Base Field to Date. Change the Base Item to (Previous Item). Type a better name than Sum of Revenue2 - perhaps % Change. Click OK. You will have a mostly blank column D (because the pivot table can't calculate a percentage change for the first year. Right-click the D and choose Hide.
Post a Comment
Read more

Data Analytics

Introduction to Data Analytics What is Data Analytics? Data Analytics is the process of exploring and analyzing large data sets to help data driven decision making.                         Analyze Data        Decision Making Definition Data when suitably filtered and analysed along with other related Data Sources and a suitable Analytics applied can provide valuable information to various organizations, industries, business, etc. in the form of prediction, recommendation, decision and the like. Applications of Data Analytics Finance & Accounting, Business analytics, Fraud , Healthcare, Information Technology, Insurance, Taxation , Internal Audit, Digital forensic, Transportation, Food, Delivery, FMCG, Planning of cities, Expenditure, Risk management, Risk detection, Security, Travelling, Managing Energy, Internet searching, Digital advertisement , etc. Real life examples of Data Analytics 1. ...
Post a Comment
Read more