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

40 Power Query Editor features in Power BI

40 Power Query Editor features in Power BI along with examples: 1. Filter Rows: Remove rows based on conditions. Example: Remove rows with a null value in the "CustomerName" column. 2. Remove Duplicates: Eliminate duplicate rows. Example: Remove duplicate entries based on the "OrderID" column. 3. Sort Rows: Arrange rows in ascending or descending order. Example: Sort data by "Date" column in descending order. 4. Replace Values: Substitute one value with another. Example: Replace "N/A" with "Unknown" in the "Status" column. 5. Split Columns: Divide a column into multiple columns. Example: Split "FullName" into "FirstName" and "LastName." 6. Merge Queries: Combine data from multiple sources. Example: Merge customer and order data based on the "CustomerID." 7. Group By: Aggregate data based on a specific column. Example: Group sales data by "ProductCategory" and calculate the sum ...
Post a Comment
Read more

Indirect Function

INDIRECT  is pretty cool for grabbing a value from a cell. Can  INDIRECT  point to a multi-cell range and be used in a  VLOOKUP  or  SUMIF  function?  You can build an  INDIRECT  function that points to a range. The range might be used as the lookup table in a  VLOOKUP  or as a range in  SUMIF  or  COUNTIF . In  Figure , the formula pulls data from the worksheets specified in row 4. The second argument in the  SUMIF  function looks for records that match a certain date from column A. Note:  Because each worksheet might have a different number of records, I chose to have each range extend to 300. This is a number that is sufficiently larger than the number of transactions on any sheet. The formula in cell B5 is: =SUMIF(INDIRECT(B$4&"!A2:A300"), $A5, INDIRECT(B$4&"!C2:C300")) Summary:  You can use  INDIRECT  to grab data from a multi-cell range.
Post a Comment
Read more

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