| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Computing percent changes in Excel between two values
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Computing percent changes in Excel between two values
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. -- David Biddulph "Lance in California" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Computing percent changes in Excel between two values
One way:
=IF(A1=0,"N/A",A2/A1-1) In article , Lance in California wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How can I change all values in a spreadsheet by a certain percent | Excel Discussion (Misc queries) | |||
| Computing averages excluding values with no value | Excel Worksheet Functions | |||
| Formatting a number to look like a Percent without a percent sign | Excel Discussion (Misc queries) | |||
| Algorithm for computing Excel PercentRank | Excel Worksheet Functions | |||
| Is there a way to have two values (percent and number) in a label. | Charts and Charting in Excel | |||
Linear Mode
