Computing percent changes in Excel between two values

[Lance in California]

I work at a police department and we track #'s of certain crimes and then
monthly report the percentage of change from, for example, January 2006
murders vs January 2007 murders. I'm looking for the formula to enter into
the cell that will compute the percentage change taking into account that Jan
2006 could be 0 (zero) and/or Jan 2007 could be 0 (zero). Is it as simple as
(A2/A1)-1 ??? Should the formula include more in depth with "if/then"
statement to account for the possibility of a 0 in either A1 or A2? I'm new
to the job with no on the job trainer and the current formula in the "change"
column is probably over 100 characters in length with six "If" included...
and it doesn't work. Thanks for your help

 

[David Biddulph]

Yes, the formula is (A2/A1)-1. The case of A2 being zero isn't a problem,
as in that case you've got a percentage change of -100%. The more
problematic case is if A1 is zero, as then you get a divide by zero error.
If you want to trap for this, you could use =IF(A1=0,"infinite
increase",A2/A1-1). It is, of course, of rather limited value to show
percentage changes if the sample size is so small that values of zero are
likely.

 

[JE McGimpsey]

One way:

=IF(A1=0,"N/A",A2/A1-1)