Learn to build secure applications from the mindset of the hacker and avoid being exploited.

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)

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get this solution by purchasing an Individual license!
Start your 7-day free trial.

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trialThe 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%

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.

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.

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'

Microsoft Excel

From novice to tech pro — start learning today.

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get this solution by purchasing an Individual license!
Start your 7-day free trial.

You can't get a meaningful result.