morinia

asked on

# Calculating percentage change when going from 0 to a positive number

Experts,

I have a spreadsheet that looks like this.

A1 B1

0 155

I am trying to calculate the percentage change between a1 and b1

Normally, I just do;

(B1- A1)/A1

Since A1 is 0, I cannot divide by A1. What would be the formula to use that would give me the correct answer. I believe it would be 100% (I think)

I have a spreadsheet that looks like this.

A1 B1

0 155

I am trying to calculate the percentage change between a1 and b1

Normally, I just do;

(B1- A1)/A1

Since A1 is 0, I cannot divide by A1. What would be the formula to use that would give me the correct answer. I believe it would be 100% (I think)

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

It is an increase of 155 but you can't express that as a percentage increase. A decrease from 155 to 0 would be 100%, (155/155)*100

The answer cannot be measured. 100%, 500% or a million percent of zero is still zero.

The answer would be 100% only if the amounts doubled (because you are measuring the

For example:

A1 B1

100 200

(B1 - A1)/A1 = (200 - 100)/100 = 1 = 100%

The answer would be 100% only if the amounts doubled (because you are measuring the

**increase**.)For example:

A1 B1

100 200

(B1 - A1)/A1 = (200 - 100)/100 = 1 = 100%

To Summerize, Your problem isn't an Excel one but a mathematical one.

What I'd do is add an if clause like so:

=if(A1=0,"NaN",(B1-A1)/A1)

If A1 is 0 then the Result is NaN (Not a Number) otherwise, you get the percentage.

What I'd do is add an if clause like so:

=if(A1=0,"NaN",(B1-A1)/A1)

If A1 is 0 then the Result is NaN (Not a Number) otherwise, you get the percentage.

Another way...

=IFERROR((B1-A1)/A1,B1/100)

=IFERROR((B1-A1)/A1,B1/100

Since A1 is 0, I cannot divide by A1. What would be the formula to use that would give me the correct answer. I believe it would be 100% (I think)

It can't be 100%.. a 100% increase is the same as doubling the value by 2 (IE. a 100% increase on 5 is 10) so basically, a 100% increase on 0 is the same as 0x2 which is 0.

Another way...

=IFERROR((B1-A1)/A1,B1/100)

I fail to see how would 'B1/100' be useful if the original function would be evaluated is a function. if anything I'd display 'B1-A1' to show the increase in plain figures and not percent of the original.

If A1 is 0 (B1-A1)/A1 will produce #DIV/0! error and then control will be passed to B1/100.

So if A1=0 and B1=155, =IFERROR((B1-A1)/A1,B1/100) will return 1.55. While the formula cell is formatted as Percentage, it will show 155%.

So if A1=0 and B1=155, =IFERROR((B1-A1)/A1,B1/100

f A1 is 0 (B1-A1)/A1 will produce #DIV/0! error and then control will be passed to B1/100.

So if A1=0 and B1=155, =IFERROR((B1-A1)/A1,B1/100) will return 1.55. While the formula cell is formatted as Percentage, it will show 155%.

Agreed, however a change from 0 to 155 can't be evaluated in percentage - It's mathematically wrong,

There are only two options as for the result in case A1 is 0. You either display a text saying that the value can't be calculated OR show the increase as a plain number instead of percentage.

Whether he uses an if clause or an iferror evaluation is not the point.

Though I'll admit, iferror might be better as it catches a rather wide array of errors and not only '#DIV/0'

Yes you have a point if we see it in the perspective of mathematics.