Randombard
Active Member
- Joined
- Jun 30, 2008
- Messages
- 392
Hi All,
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com
ffice
ffice" /><o
></o
>
I have not got a problem (this time), I use this site a lot and decided that I would give something back.
<o
></o
>
This is a formula that I have worked out for when you need to show a percentage increase/decrease between 2 cells.
<o
></o
>
An example of this would be if you needed to see what percentage diference you have made in sales that year/month.
<o
></o
>
This formula ensures that if you don’t encounter the #DIV/0! error, and normalises the data for when you have no data/sales for one year/month/day but you have data/sales in the current year (or whatever). If you have no date for either of your targets then it will tell you “No Data To Compare”.
<o
></o
>
In the case of this formula A2 is the most recent year and A1 is the previous year, the result (once the cell has been formatted to percentage) will be the increase or decrees.
<o
></o
>
I hope that I have made this clear, I am sure that many of you have resolved this issue yourselves I just thought that some people might find this useful and it did not hurt to give back a little.
<o
></o
>
<o
>The Formula:</o
>
<o
></o
>
=IF(A2+A1=0,"No Data To Compare",IF(ISERROR(A2/A1),(A2+1)/(A1+1)-100%,IF(A2=0,(A2+1)/(A1+1)-100%,(A2/A1)-100%)))
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com




I have not got a problem (this time), I use this site a lot and decided that I would give something back.
<o


This is a formula that I have worked out for when you need to show a percentage increase/decrease between 2 cells.
<o


An example of this would be if you needed to see what percentage diference you have made in sales that year/month.
<o


This formula ensures that if you don’t encounter the #DIV/0! error, and normalises the data for when you have no data/sales for one year/month/day but you have data/sales in the current year (or whatever). If you have no date for either of your targets then it will tell you “No Data To Compare”.
<o


In the case of this formula A2 is the most recent year and A1 is the previous year, the result (once the cell has been formatted to percentage) will be the increase or decrees.
<o


I hope that I have made this clear, I am sure that many of you have resolved this issue yourselves I just thought that some people might find this useful and it did not hurt to give back a little.
<o


<o


<o


=IF(A2+A1=0,"No Data To Compare",IF(ISERROR(A2/A1),(A2+1)/(A1+1)-100%,IF(A2=0,(A2+1)/(A1+1)-100%,(A2/A1)-100%)))