View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
lstreet lstreet is offline
external usenet poster
 
Posts: 3
Default How do I find a % difference between two numbers (comparing 20

Ok but what happens then if you are trying to use the data to predict future
data...let's say for sales projections based on prior year percents of change
and then using the average of those percents of change? If you suddenly have
300% growth this will throw all other years out of alignment and the average
will be way too high.

" wrote:

"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%).