#DIV/0! in variance/percentage formula

[Annette]

I have tried to figure out how to write this formula so it will produce
either the correct result or show nothing.

Here is an example

2009 2008 % -/+
50.00 100.00 -0.5%
100 0 0%
0 100.00 -100%

I can write =if(sum(cell 1 - cell 2)/cell 2 = "", "", sum(cell 1 - cell
2)/cell 2))
The second row will display #DIV/0!

I would like the second row to display 0 ...

Can someone tell me how to write that formula? Thanks much!

Annette

 

[Dave Peterson]

First, you don't need that =sum() function in your formula:

=if(b2=0,"",(a2-b2)/b2)

As long as those cells are numeric or empty.

 

[JoeU2004]

You say you want 0% if you have 0 in 2008 and 100 in 2009. That wouldn't be
my choice; but the choice is arbitrary.

Assuming that 50 is in A2 and 100 is B2, the formula for C2 (-50%, not -0.5%
by the way):

=if(B2=0, 0, (A2 - B2) / B2)

formatted as Percentage. Copy the formula down.


----- original message -----

 

[Shane Devenshire]

Hi,

A few unrelated comment don't write formula which some single numbers:

=SUM(A1-B1) wastes typing time, and computer power and makes your
spreadsheet larger.

Suppose A1 is 50 and B1 is 25, the first thing Excel does in calculate 50-25
which is 25. Then you ask Excel to SUM 25, well the some on any single
number is itself!