#4 in the List of Top 5 Functions: IFERROR
Q: What do an acrobat and Excel’s IFERROR function have in common?
A: An acrobat and an Excel user who employs IFERROR are both expressing:
it’s risky but I know what I’m doing and I do it with elegance
IFERROR is in my Top 5 Excel Functions to Know because responsible Data Management requires anticipation of errors and clear understanding of what errors mean. The screencast in this post demonstrates a way to not only determine who did not attend training but also list them as having not attended; otherwise, they would just show up as a #N/A error.
The risk in using IFERROR is because we re-assign the error message and could mask a true error if we’re querying poor quality data. (More about Error Handling after the screencast)
THE HISTORY & REASON FOR USE
IFERROR showed up for the first time in Excel 2007. Prior to 2007 if you wrote a formula that bombed out for any number of reasons, you’d get errors like #N/A, #DIV/0 or, #REF. However, there are times when we know that an error is going to come up or, we purposefully want to identify the errors. Thus, a result of #N/A is no surprise. So, it’d be nice to have Excel tell us something other than “you have an error.”
- We have a list of 100 people who were scheduled for training, and their addresses
- Another list has the people who attended training and whether they passed or failed
- We run our VLOOKUP to compare the 2 lists
- We know that the #N/A is going to show
- NO! Those aren’t errors, those are people who were AWOL!
We have to write a formula that says:
- Look for person X on list the Attendee list and bring back their Pass/Fail.
- If there is no Pass/Fail, list them as: Did Not Attend.
Poor Data Quality Poses a Risk for IFERROR Are we sure that there isn’t someone on the attendee list who wasn’t scheduled? Are we confident that the names do match? If Dan was scheduled and he attended as Danny then, Dan will show up as AWOL. Be careful of crap data. Careless use of IFERROR can hide an error that you need to know about.
SYNTAX
=IFERROR(value, value if result is an error)
In English:
=You asked me to do something. What should I do if it can’t be done?
Example:
Let’s divide cell B2 by C2
=B2/C2
Where C2 = 0, the result is #DIV/o
Let’s use IFERROR:
=IFERROR(B2/C2, “INVESTIGATE”)
Where C2 = 0, the result will be INVESTIGATE
LIVE ACTION Demo of IFERROR
CONCLUSION
With those “oh damn!” moments first we troubleshoot our work. If our work is right, the error is revealing something in the underlying data that we need to pay attention to. The error may make sense.The error may expose deeper unforeseen problems that need to be investigated.
One time, I was reconciling thousands of transactions against the physical inventory, and strange errors came up. They made no sense. Deeper investigation showed that someone fell so far behind in their work that they processed 170 transactions and expected to send the invoices later. Unfortunately, “later” never came.
Effective error handling showed us a problem and gave clues on where to start asking questions.
As you work with data, keep error-handling in mind, and in Excel, IFERROR is your friend. Be the acrobat! Be elegant! You know what you’re doing.
Handstand photo credit: euze via photopin cc
Acrobat credit: dirkjanranzijn via photopin cc
