Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
#DIV/0! in variance/percentage formula
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
#DIV/0! in variance/percentage formula
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. Annette wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
#DIV/0! in variance/percentage formula
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 ----- "Annette" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
#DIV/0! in variance/percentage formula
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! -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Annette" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Tables - Variance and Variance % | Excel Discussion (Misc queries) | |||
Percent Variance Formula | Excel Discussion (Misc queries) | |||
Formula for calculating Variance | Excel Discussion (Misc queries) | |||
Excel % Variance Formula........ | Excel Worksheet Functions | |||
Pivot Tables - Variance and % Variance fields | Excel Discussion (Misc queries) |