You should all remember from math class that you can’t divide a number by zero. When you try to do it in Excel, the result of your formula will be #DIV/0!. In some cases this is inevitable. For example if your spreadsheet calculates percentage change in annual sales of inventory items, new items will produce a #DIV/0! error because prior year sales are zero.
|
A |
B |
C |
D |
E |
Column E Formula | |
|
1 |
Year 1 Sales |
Year 2 Sales |
$ Change |
% Change |
||
|
2 |
Item 1 | 10,000 | 11,000 | 1,000 | 10% | =D2/B2 |
|
3 |
Item 2 | 0 | 7,000 | 7,000 | #DIV/0! | =D3/B3 |
|
4 |
Item 3 | 15,000 | 12,000 | (3,000) | -20% | =D4/B4 |
As expected, the % Change for Item 2 shows a divide by zero error because it had no sales in year 1. Excel does not have an option to suppress divide by zero errors, but it’s easily by done using the IF function. If you haven’t used IF before, it may help to read my previous tip on that subject. The formula below tells Excel, if the prior year sales (for Item 2 that’s cell B3) is zero, then display the text between the quotation marks (in this case nothing), if not, divide the $ Change by Year 1 Sales. The result is that the % Change appears blank for item 2 but for items 1 and 3 it looks the same as with the original formula.
|
A |
B |
C |
D |
E |
Column E Formula | |
|
1 |
Year 1 Sales |
Year 2 Sales |
$ Change |
% Change |
||
|
2 |
Item 1 | 10,000 | 11,000 | 1,000 | 10% | =IF(B2=0,””,D2/B2) |
|
3 |
Item 2 | 0 | 7,000 | 7,000 | =IF(B3=0,””,D3/B3) | |
|
4 |
Item 3 | 15,000 | 12,000 | (3,000) | -20% | =IF(B4=0,””,D4/B4) |
I made the % Change blank if Year 1 Sales = 0, but you could show whatever you want.
If you want to display text when Year 1 Sales = 0 type whatever you want between the quotation marks. In this example you might use =IF(B3=0,”New”,D3/B3) to make the word New appear as the % Change for any item with no Year 1 Sales.
If you want to display a number, leave out the quotation marks and put the number after the =, as in =IF(B3=0,0,D3/B3) to have a zero displayed when Year 1 Sales=0.
To display the contents of another cell when Year 1 Sales are 0, enter the cell reference after the = in your formula, as in =IF(B3=0,G10,D3/B3). Don’t use quotation marks or Excel will display the cell reference (G10) instead of the contents of that cell.
