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?

 

[xld]

Try

=IF(OR(F1=0,G1=0),0,(F1-G1)/G1)

 

[a massive zebra]

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?

Not sure if this is what you want but the following will give you 100% when g = 0:

=if(iserror((F1-g1)/g1),1,(F1-g1)/g1)

 

[djacob69]

Both of these examples work, just not the way I thought they would.

What would be the percentage change going from .5966 to .00000.

I am running into a problem when using this as the values. The very next set of values are .00000 to 1.41555

One way it works and then the next time I get the #div/0 error. How can I get to actually calculate percentage using .0000?

 

[xld]

How? As I see it, both cater for zero and never return #DIV/0!

 

[bobsan42]

Both of these examples work, just not the way I thought they would.

What would be the percentage change going from .5966 to .00000.

I am running into a problem when using this as the values. The very next set of values are .00000 to 1.41555

One way it works and then the next time I get the #div/0 error. How can I get to actually calculate percentage using .0000?

If we talk about mathematics here - NO F... WAY

 

[Expiry]

The percentage change from ANY number to zero is 100% (or -100%). ie. you had $10 and you now have no money. you've spent 100% of your money.

You have to make a rule for yourself how you will treat a percentage change from 0 (ie. you always want 0 to X to be shown as 0 or 100%).

There's no getting away from that. It's not a formula problem, it's maths, as bobsan F... put it.

 

[djacob69]

Yep. I understand the concept....It is like digging a hole. How long does it take to dig a hole? Once shovel load.

Thank you for everyone's help...

 

[xlfan80c]

Yep. I understand the concept....It is like digging a hole. How long does it take to dig a hole? Once shovel load.

Thank you for everyone's help...

djacob69: I understand what you want to achieve and I have a solution that worked for me. But I hope you have found your solution already since this is an old thread. Anyway, to those who need it, here you go:

Given values:
Cell A1 (July) = 12
Cell A2 (June) = 0

The formula I used to show 100% change is =IF(AND(A2=0,(A1-A2)<>0),1, (A1-A2)/A2)

This also works the other way around if you want to show -100% change.

Cheers and continue paying it forward!

 

[mk824196]

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))