Formula Fridays #20
Formula Fridays are short reads, spotlighting a single formula that you can start using straight away.
This Formula Friday post outlines how to use IFERROR and ISERROR Functions. A well placed IFERROR or ISERROR, can make a performance summary easier to read. Instead of a table with random cells containing #DIV/0!, #REF! or #VALUE! Distracting people from the overall message. These cells will be replaced with a value of your choosing for a more coherent message.
These two Functions are important for any Merch or Ecom Manager to master. This post will outline the differences between the two Functions and how to use them.
IFERROR and ISERROR. What are They, and Why Do They Matter?
IFERROR and ISERROR are functions with predefined formulas that are used to handle errors in a formula. Errors in formulas need to be managed to control how data is represented.
What’s the difference between IFERROR and ISERROR
IFERROR and ISERROR each handle errors in formulas, but with a slight difference. One allows you to tell Excel what to do if your formula results in an error – IFERROR. And the other will tell you if the formula will result in an error – ISERROR.
IFERROR is used to evaluate if a formula calculation will return an error. If so, it will present a specified value of your choosing. If the calculations are not evaluated to return an error, the answer of the calculations will be displayed.
ISERROR is used to represent when an error is present. If an error is present it will return true and when an error is not present it will return false.
How to use IFERROR
IFERROR is the function to use when errors in formulas need to be managed. Being able to handle where errors appear in performance summaries can reduce disruption to the overall message. And ensure the data is cleansed to show the most relevant information needed to inform conclusions.
=IFERROR(VLOOKUP($X11,‘DAILY CHANNEL’!$A:$J,Y$2,0)/1000,0)
How to use ISERROR
ISERROR is used to test if a calculation will result in an error. The returned result of an ISERROR function is true or false. If there is an error, it will return true otherwise it will return false. In order to use it to cleanse and transform data, it will need to be used in conjunction with another function.
Unlike IFERROR, ISERROR needs additional instructions for what values to display when a calculation is true or false.
ISERROR, is frequently nested inside of an IF statement like this
=IF(ISERROR(SUM(Y6:Y11)),0,SUM(Y6:Y11))
I use IFERROR most often. It’s easier to understand and looks neater, in my opinion. The most important thing to remember is what you are asking Excel.
With IFERROR the assumption is that the formula will work. So in instances when it does not work, standardised results help to cleanse the data. And make it possible to discover useful information to support decision making. When using ISERROR, the assumption is that the formula will cause an error. In an instance when it does not result in an error, input a standardised result.
Finding an exact match for your needs on the internet can, at times, feel like trying to find the missing pair to your odd sock. You know it’s in the drawer somewhere, but you just can’t see it for love nor money. That’s where I can help. Think of me as your Data Analysis Agony Aunt. Send in your lonely heart letters and I will find your perfect match.
Like this post? You should see my newsletter.
Subscribe to the Excel & ME newsletter to get updates that will empower you to inform your business decisions with data.
