View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
 
Posts: n/a
Default How do I find a % difference between two numbers (comparing 2005 .

"Officer Steve" wrote:
How do I find the percent difference between two numbers.


=(A1-B1)/B1

where column B is "before" (old) and column A is "after" (new).
Remember to format the cell as Percentage if you want "%".

Sometimes I have a zero which throws the forumlas
I've used off.


=IF(B1 = 0, "something", (A1-B1)/B1)

The question only you can answer is: what is "something"?
I like the following:

=IF(B1 = 0, A1, (A1-B1)/B1)

Thus, if B1 is 0 and A1 is 3, the result will be 300%. Purists
will tell you that is wrong: you cannot have a percentage
increase over 0; and mathematically, they are correct. But
the alternative is to display something that is inconsistent
with all other cells where B1 is not zero. If you are okay
with that, fine. I prefer consistency, and I think 300% is
not unreasonable; compare with B1=1 (200%).