Calculating percentage change with 0 values?

[djacob69]

Ok. Here is the situation. I have two columns that I need to calculate the percentage change..

Column G is year 2010
Column F is year 2011
Column I is percentage change from G to F (cell is formatting for percentage)

The values in G and F can range from .5966 to 1.56, including a value of 0.000

If I use the forumula (F1-g1)/g1. F1=1.4155 and G1 = 0.000 I will receive a #div/0 error.

If the values for F1=0.000 and G1 = 1.4155. The result will be 100%

I need the forumula to work both ways with the possibility of 0 being used in either column...

Any ideas?

 

[mk824196]

I forgot to add in the AND on the first explanation Code
=IF(AND(OLD VALUE=0,New Value>0),1,IF(MIN(OLD VALUE,NEW VALUE)<=0,(NEW VALUE-OLD VALUE)/ABS(OLD VALUE),(NEW VALUE/OLD VALUE)-1))

 

[clc10301981]

I use this code


=IF(OLD VALUE=0,1,IF(MIN(OLD VALUE,NEW VALUE)<=0,(NEW VALUE-OLD VALUE)/ABS(OLD VALUE),(NEW VALUE/OLD VALUE)-1))

So an example would be in column A we have purchases from 2018 and in column B we would have purchases from 2019. This formula would go in Column C to show the percent change. What it means is; If the value in 2018 = 0 and the value in B2 is greater than zero then the answer is 100%. If this is not true, then it moves on to the next argument which is; if the minimum value in B2 and A2 is less than 0 then you subtract B2 from A2 and the divide it by A2 using the ABS formula. If that is still false then the fail safe is dividing B2 by A2 and subtracting 1 (which is 100%) to show the true growth rate.

=IF(AND(A2=0,B2>0),1,IF(MIN(A2,B2)<=0,(B2-A2)/ABS(A2),(B2/A2)-1))

This formula is awesome and worked for so many percentages I needed, however when the old value is zero and the new value is zero the first formula gave me 100% and the second formula gave me #DIV/0!. Is there another IF statement within this formula that would work for that scenario?

 

[LikeGlue]

I have just run into the same issue as you, and successfully used the below:

=IF(AND(OLD VALUE CELL=0,NEW VALUE CELL>0),100%,(IFERROR(((NEW VALUE CELL-OLD VALUE CELL)/OLD VALUE CELL),0)))

In practice using E11 (old value) and F11 (new value), this is the formula:

=IF(AND(E11=0,F11>0),100%,(IFERROR(((F11-E11)/E11),0)))