IFERROR and Custom Cell Formatting
Introduction
This page has the objective of introducing the new Excel 2007 function =IFERROR(...). The page has the secondary objective of exploring the ideas concerning custom cell formatting. Both IFERROR and custom formatting are explained and developed by way of specific examples.
=IFERROR(...)
We explain the new IFERROR function within a basic budgeting question: the kind of question to be found in an introductory or intermediate level management accounting text. As far as Excel 2007 is concerned, however, the question gives rise to a very nice discussion concerning the IFERROR(...) function. The IFERROR function is new to Excel 2007 and it replaces the nearest equivalent nested =IF(ISERROR(A1), "An error occurred.", A1 * 2) formulation.
IFERROR(...) is much more concise that its predecessors because it only needs the following structure: IFERROR(value,value_if_error). On the one hand the new function is a time and memory saver but when something even slightly complex appears the function doesn’t necessarily appear to be quite so user friendly.
With the help of a few people on the Excel-G email based discussion list I have been able to provide four different solutions to the problem arising from the budgeting question that follows. In the end, however, by incorporating some custom cell formatting in the range of cells I was using I was able to condense
=IF(ISERROR(D23-D21),"?",if(D21>D23,"?",D23-D21)))
Into this:
=IFERROR(D23-D21,"?")
Read on for the question and a full explanation of what I did.
The Budgeting Question
Morlin Company makes a single product. Data appear below.
For every unit if product, the raw material requirements are: 2 kilogrammes at $3/Kg.
Stock (inventory) requirements: for finished goods, stocks equal to the following two months’ sales; for raw materials stocks must be equal to the following month’s production needs.
| Sasles Forecast (in units): |
Month |
Units |
January |
1,000 |
February |
1,200 |
March |
1,300 |
April |
1,500 |
May |
1,400 |
June |
1,600 |
The firm began January with 1,800 units of finished goods; 2,300 kg of raw materials.
Required
- Prepare production budgets for months January to April.
- Prepare budgets of purchases of raw materials, both in kg and in values, for as many months as you can. Explain why you had to stop where you did.
Setting out the Problem
The problem was to program a Production Budget as follows:
Production Budget |
J |
F |
M |
A |
M |
J |
Opening Stock |
1,800 |
2,500 |
2,800 |
2,900 |
3,000 |
? |
Production |
1,700 |
1,500 |
1,400 |
1,600 |
? |
? |
|
3,500 |
4,000 |
4,200 |
4,500 |
1,400 |
1,600 |
Closing Stock |
2,500 |
2,800 |
2,900 |
3,000 |
? |
? |
Sales |
1,000 |
1,200 |
1,300 |
1,500 |
1,400 |
1,600 |
The constraints here include the closing stock requirement to be equal to the sum of the succeeding two months’ sales: January closing stock, then is 1,200 + 1,300 ie February + March Sales.
Since there was insufficient data to complete all of the six months shown above, I wanted to show question marks where I couldn’t evaluate a cell. The question marks are purely cosmetic and not entirely necessary but they help us with this IFERROR explanation.
The Layout of the Spreadsheet
The spreadsheet I prepared is as follows. Note that the first section of the work sheet is the input section and contain a text box containing a few notes of guidance.
This spreadsheet is not provided as part of this page.
What this means is that the Production budget we are about to discuss begins in cell C21 with the row heading Opening Stock.
Closing Stock Calculation
There should be no controversy with the following calculation: for the closing stock calculations, the formula I used is, beginning with January:
=IF(AND(E25>0,F25>0),SUM(E25:F25),"")
Production Calculations: alternative solutions
For the Production calculations, I started to use IFERROR(...) but found that it couldn’t cope alone: I felt I needed to nest it in some way. This proved to be more problematic than I thought it would be and Bob Umlas, via the Excel-G discussion, list suggested I revert to the earlier solution:
=IF(ISERROR(D23-D21),"?",if(D21>D23,"?",D23-D21)))
Bob’s solution worked but I really wanted to use IFERROR(...) since I was hoping to demonstrate it as an upgrade in Excel 2007. I thought about this for a few more seconds and came up with this solution:
=IFERROR(IF(D23-D21<0,"?",IFERROR((D23-D21),"?")),"?")
That was more complex than I wanted but, again, it works and I was satisfied.
Then via the same discussion list Wyatt Lemmings suggested as follows:
I'd just go: =IF(ISERROR(D23-D21),"?",D23-D21) and format as 0;"?";0;"?"
Wyatt’s solution also did what I wanted it to do but since Wyatt was unsure of the IFERROR(...) function I decided to try his suggestion but to adapt it to work with IFERROR(...) too. It worked!
Final Version
In conclusion, then, I have now got the following solution to the Production row in the Production Budget, beginning with January’s calculation and then copied across to June:
=IFERROR(D23-D21,"?") with the cells CUSTOM FORMATTED as 0;"?";0;"?"
For me the most interesting part of Wyatt's solution, having resolved the IFERROR function aspect is the 0;"?";0;"?" part of the solution. The following section explains what this means.
Notes on Custom Formatting
For reference, the following notes come from Excel 2007’s Help Files on custom formatting and are very useful here: they explain why Wyatt’s solution is so elegant!
To create a custom number format, you start by selecting one of the built in number formats as a starting point. You can then change any one of the code sections of that format to create your own custom number format.
A number format can have up to four sections of code, separated by semicolons. These code sections define the format for positive numbers, negative numbers, zero values and text, in that order.
<POSITIVE>;<NEGATIVE>;<ZERO>;<TEXT>
For example, you can use these code sections to create the following custom format:
[Blue]#,##0.00_);[Red](#,##0.00);0.00;"sales "@
You do not have to include all code sections in your custom number format. If you specify only two code sections for your custom number format, the first section is used for positive numbers and zeros, and the second section is used for negative numbers. If you specify only one code section, it is used for all numbers. If you want to skip a code section and include a code section that follows it, you must include the ending semicolon for the section that you skip.
- Include a section for text entry If included, a text section is always the last section in the number format. Include an "at" character (@) in the section where you want to display any text that you type in the cell. If the @ character is omitted from the text section, text that you type will not be displayed. If you want to always display specific text characters with the typed text, enclose the additional text in double quotation marks (" "). For example, "gross receipts for "@
- If the format does not include a text section, any nonnumeric value that you type in a cell with that format applied is not affected by the format. In addition, the entire cell is converted to text.
- Add spaces To create a space that is the width of a character in a number format, include an underscore character (_), followed by the character that you want to use. For example, when you follow an underscore with a right parenthesis, such as _), positive numbers line up correctly with negative numbers that are enclosed in parentheses.
- Repeat characters To repeat the next character in the format to fill the column width, include an asterisk (*) in the number format. For example, type 0*- to include enough dashes after a number to fill the cell, or type *0 before any format to include leading zeros.
What this means for this formatting, 0;"?";0;"?", is that if you enter or calculate a positive number, the cell will return that positive number. If you enter or calculate a negative number, Excel 2007 will enter ? in that cell. Enter or calculate a zero and Excel 2007 will show you 0. Finally, if you were to enter or evaluate the response to be text, Excel 2007 will enter ? for you.
© where appropriate Duncan Williamson
March 2009
Download this page in PDF format
