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

How to create a Waterfall Chart

Download Example Waterfall chart file from below link https://drive.google.com/file/d/17OKYxHKzT8NxWM0FuPqEb26ntzQqa29_/view?usp=sharing How to Create a Waterfall Chart in Excel If you want to build a waterfall chart of your own, we’ve got the step-by-step instructions for you. Although Excel 2016 includes a waterfall chart type within the chart options, if you’re working with any version older than that, you will need to construct the waterfall chart from scratch.  Step 1: Create a data table Let’s start with a simple table like annual sales numbers for the current year. You will see in the table below that the sales amounts vary for each month. Some months will have positive sales growth, while others will be negative.     Insert three additional columns to your Excel table to represent the movement of the columns on the waterfall chart. The base column will represent the starting point for the fall and rise of the chart. You will input all the negative numbers fro...
Post a Comment
Read more

See All Formulas in a Excel Sheet at Once

You have a spreadsheet from a former co-worker and you need to figure out how the calculations work. You could visit each cell, one at a time, and look at the formula in the formula bar. Or you could quickly toggle between pressing  F2  and  Esc  to see the formula right in the cell. But there is a faster way. On most U.S. keyboards, just below the  Esc  key is a key with two accent characters: the tilde from Spanish and the grave accent from French. It is an odd key. I don’t know how I would ever use this key to actually type piñata or frère . If you hold down  Ctrl  and this key, you toggle into something called Show Formulas mode. Each column gets wider, and you see all of the formulas. This gives you a view of all the formulas at once. It is great for spotting “plug” numbers (B9) or when someone added the totals with a calculator and typed the number instead of using  =SUM() . You can see that the co-worker left RANDBETWEEN functions in t...
Post a Comment
Read more

Introduction to Power BI and Power BI Desktop

  Introduction to Power BI and Power BI Desktop 1. What is Power BI 2. Power BI Desktop Installation 3. Power BI Desktop User Interface Power BI Desktop Installation Link https://www.microsoft.com/en-us/download/details.aspx?id=58494 Please Subscribe the Channel for Updates on new Tutorial videos https://www.youtube.com/channel/UCW_euuHC79CPXuwUDoqT5Rg Books https://www.amazon.in/Punit-Prabhu/e/ ... Business/Consulting/Corporate /Individual Training solutionsformso@gmail.com
Post a Comment
Read more