If IFERROR() were to be a person, I would hug her so hard that Jo (my wife) would get in to a cat fight with her. I know many a woman (and man) who get in to a fight with Excel formulas often. But thankfully, we avoid that as IFERROR is not a real person. It is, however a darned useful formula.
Since I cannot hug a formula anymore than I can get my son to sit tight, I will go ahead and sing an ode to her, in my style – by writing about how useful and powerful IFERROR formula is.
What is IFERROR() Formula & How does it work?
Introduced since Excel 2007, IFERROR() formula checks a formula (or expression) and returns the value of formula if there is no error, otherwise a custom formula.
For eg:
=IFERROR(1/0,"Try splitting an atom instead!")
will give the message Try splitting an atom instead! because the expression 1/0 returns an error (DIV/0 error)
Where as,
=IFERROR(0/1,"Try splitting an atom instead!")
will give the value 0 since 0 divided by 1 is 0.
How does IFERROR help me?
Archimedes once said, “Give me enough data and a spreadsheet, I can make any formula return an error.”
May be he was too confident, but errors are everywhere. And that is why IFERROR is useful. It provides an elegant and simple way to tackle the errors in your workbook.
Several common uses of IFERROR are,
- 1. While writing lookup formulas like VLOOKUP, INDEX+MATCH it is common to search for values that do not exist in your data. You can wrap such formulas in IFERROR for peace of mind.
Ex:=IFERROR(VLOOKUP(...),"Not found") - 2. While using reference formulas like INDEX, OFFSET, frequently, we try to fetch the data that is not in the list of values. This returns #REF errors. You can fix them with IFERROR easily.
Ex:=IFERROR(INDEX(...),"") - 3. While using arithmetic, numeric expressions, usually we end up dividing by 0. You can fix such things by using IFERROR.
Ex:=IFERROR(AVERAGE(...),"0")— Returns 0 when the list has zero values.
Things to keep in mind while IFERRORing:
Please note that IFERROR is oblivious to the type of error. That means, no matter what the error is (DIV/0, #NAME, #N/A, #REF… etc.), IFERROR treats all of them equally and shows the same value. In other words, IFERROR is like “Catch all” in programming world.
How to handle errors if you are using Excel 2003 or below?
In earlier versions of Excel, we have a formula called as ISERROR() that can check an expression or formula for error and return TRUE if so. This formula is not same as IFERROR, but we can use it along with IF() formula to get the same result. For eg.
=IF(ISERROR(VLOOKUP(...)),"Not found",VLOOKUP(...))
works same as, =IFERROR(VLOOKUP(...),"Not found")
Notice that the ISERROR approach evaluates VLOOKUP formula twice!.
Do you IFERROR?
To err is human, to IFERROR is awesome.
Ever since discovering the IFERROR feature in Excel 2007, I have been using it so often. I use it to keep my output sheets clean and my formulas simple.
What about you? Do you use IFERROR? What is your experience like? Would you also give it a hug? If so, would your spouse get in to a cat fight with it? If so can you post some pics of it on our facebook page?
Please share your experiences and tips on using IFERROR() thru comments.
Related awesomeness:
1. How to understand and handle Excel formula errors
2. Excel formulas not working? What to do?
3. Handling errors and 5 other tips for writing better VLOOKUP formulas
17 Responses to “Budget vs. Actual Profit Loss Report using Pivot Tables”
Good Work, Yogesh & Chandoo! Thanks.
Hi everybody,
first sorry I am late to say something about this topic;actually I was waiting last part
second I am not accountant I am an Engineer
third """"Very Important""" the idea is not about Loss but I am sure it is profit
Based on third it shows:
1- How to use EXCEL
2- How to use pivot TABLES
3- How to collect and arrange DATA
4- How to make reports
Many Thanks
Hi Yogesh and Chandoo,
Thank you for sharing your knowledge!
You guys are great!
thanks chandoo and yogesh, thanks for you lessons, are great!....i have a idea for a budget. I try to do it..... thanks for all
Thanks a lot for sharing the most powerful tool worldwide "knowledge"
Warm greetings from Peru
Hi -
This is a really great article because it's a simple and common thing you'd want to do with a pivot table but not at all obvious how to do it! So - muchas gracias to Chandoo and Yogesh!
One thing - I couldn't get past the group error in the sample file. I would click on ungroup but it didn't seem to have any effect. I'd appreciate it if anybody has any pointers here.
-Juanito
Hi Chandoo
I am also having the group error. Can't seem to ungroup? Appreciate if you explain further on the steps required in order to get to calculated items.
Many thanks and keep up the great work.
Cheers
Adam
Hi Chandoo,
I'm struggling resolving the problem depicted below:
I have a set of data, with (among others) a "Region" field (can be APJ, EMEA, or AMS), and a "Country" field.
Unfortunately, I need to group data by the following 4 Regions: APeJ, Japan, EMEA and AMS.
I first tried to make a pivot with Region and Country in the rows (or columns), and then group Country data as per the above.
Alas, as soon as I have a new Country that appear in my data set, my groupings are broken, and I have to redo the job of ungrouping, grouping etc.
I thought I could try to use calculated item, by adding first a new column to my dataset concatenating Region_Country, and create an "APeJ" calculated item that would sum all the "APJ_*" and substract the "APJ_Japan", but again, no clue, as I can't find a way to use any wild card in those formulas.
Given that I already found extremely helpful tips and tricks in your site that helped me manage that bunch of data, I'm pretty sure you'll have a bright idea on how I can solve that one!
Thanks in advance for your lights!
Hi Catherine...
In such cases, I advice using an additional column in the data itself. You can set-up a grouping table else where with country in first column, region in second column. And then in the data, you can add an extra column and use VLOOKUP to fetch the region based on the country.
Then feed this entire data (with extra column) to pivot table and use the extra column to group the data.
Hi Chandoo,
Thank you for your prompt answer.
I finally came to the same conclusion - after a rest 🙂 . I was probably too tired Friday evening (it was rather late), having spent hours in manipulating all my surveys data so as to pull rolling averages, make nice graphs and so on, and was trying to find a complex solution when there was a simple one.
Thanks again,
Catherine
Hey,
Great post!
I for example have different database structure with the following fields :
Date, Expense, Income, Sum (Income - Expense), Category (Sales, Cost of Goods and etc).
Creating a P&L report for the whole year works great. Including gross margin % and etc.
Though, creating P&L report by QTR/Month is becoming impossible since i get the following error : “This PivotTable report field is grouped. You cannot add calculated item to grouped filed.”
Is there a solution for this kind of problem?
Like Adam and Juanito, I also cannot ungroup.
Would appreciate it if you can add a few more lines and a screenshot or two on where to put the mouse cursor to ungroup.
Hi, I have figured out the ungrouping problem. One of the earlier steps was to group by month, if you pull the month back down to the column then right click and then select ungroup, then pull the month back up so you end up with just data source and budget/actual as the headings, then you can continue on.
To solve the ungroup problem, my method is:
Copy the "data" sheet to a whole new Excel workbook
and directly work on Part 6.
And since it is a fresh copy, Excel don't show me the "can't ungroup" problem. Hope this help.
Thank you Yogesh for this wonderful tutorial.
Kent, Malaysia
Just when i thought pivots were awesome i learn about inserting the calculated fields and that makes them more awesome. chandoo where have you been all my life.
Hello - your P&L pivot version has really impressed my boss and would like to use it. I have applied it for a actual vs budget vs forecast model I have created. One problem. In your variance above the operating profit percent % variance shows 33.8% but I want it to show (0.01) point or the true diff from prior budget.
I know I can add calculation to the side but boss would like to see it in pivot table.
Please help
Thanks
I have a further query which may solve my above dilemma. Is it possible to add a column that calculates percent increase. So in the example above a new column would be added to show variance %.
Any help would be appreciated.
Thanks