#DIV/0! Error and percentage difference solution.

[Randombard]

Hi All,
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><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%)))

 

[Scott Huish]

Just a couple of thoughts:

You shouldn't need ISERROR, all you would have to do is check if A1 was 0. and since your using the same formula for A2 being 0, you can combine them.

100%=1

Your initial comparison assumes no negative numbers.

In other words if A1 was 7 and A2 was -7, your formula would return "No Data to Compare", even though there is.

I believe that formula can be shortened to:

=IF(COUNT(A1:A2),IF(OR(A1=0,A2=0),(A2+1)/(A1+1)-1,(A2/A1)-1),"No Data to compare")

 

[Randombard]

Hi HOTPEPPER,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Thanks for your suggestion.<o></o>
<o></o>
The reason that I did not consider the possibility of negative figures is because I designed this formula for comparing sales results, on a month to month basis. <o></o>
<o></o>
So there should never be a number below 0 to compare.<o></o>
<o></o>
However as I started this thread just as a "hi all you might find this interesting/useful" your input is fantastic <?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" /><v:shapetype id=_x0000_t75 stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" oreferrelative="t" o:spt="75" coordsize="21600,21600"><v:stroke joinstyle="miter"></v:stroke><v:formulas><v:f eqn="if lineDrawn pixelLineWidth 0"></v:f><v:f eqn="sum @0 1 0"></v:f><v:f eqn="sum 0 0 @1"></v:f><v:f eqn="prod @2 1 2"></v:f><v:f eqn="prod @3 21600 pixelWidth"></v:f><v:f eqn="prod @3 21600 pixelHeight"></v:f><v:f eqn="sum @0 0 1"></v:f><v:f eqn="prod @6 1 2"></v:f><v:f eqn="prod @7 21600 pixelWidth"></v:f><v:f eqn="sum @8 21600 0"></v:f><v:f eqn="prod @7 21600 pixelHeight"></v:f><v:f eqn="sum @10 21600 0"></v:f></v:formulas><vath o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></vath><o:lock aspectratio="t" v:ext="edit"></o:lock></v:shapetype><v:shape id=_x0000_i1025 style="WIDTH: 11.25pt; HEIGHT: 11.25pt" type="#_x0000_t75" alt="0"><v:imagedata o:href="http://www.mrexcel.com/forum/images/smilies/icon_smile.gif" src="file:///C:\DOCUME~1\meadr\LOCALS~1\Temp\msohtml1\02\clip_image001.gif"></v:imagedata></v:shape><o></o>