Percentage with a zero involved

[tankerman]

A1 is this month total
B1 is last months total
C1 is the difference, =A1-B1
This much I got
D1 is the difference in percentage, I use =(A1-B1)/B1 and I get my % in
black or (red) which is fine but now the problem

There are times we don't always handle a certain item so some months will
have a zero in either this month or last months calc and my % is all messed
up i get this #DIV/0 I think this means that you can not divide by zero but
how do I get around this.

 

[driller]

tankerman,

D1 is the difference in percentage, I use =(A1-B1)/B1 and I get my % in
black or (red) which is fine but now the problem
how do I get around this.

try on D1 with a get around remarks.

=IF(B1=0,"item not handled this month",IF(A1=0,"item not handled last
month",(A1-B1)/B1))

 

[Mike Rogers]

tankerman

A few options here:

1. =if(iserror(your formula),"",your formula)

2. =if(A1="", "", B1/A1)

3. =IF(B1=0,0,A1/B1)

4. =IF(B1=0,"n/a",A1/B1)

5. =IF(ISBLANK(A1),"",B1/A1)

6. =IF(A1,B1/A1,"")

These are just a few that I found from previous posts by searching for #DIV/0.

Hope this helps

Mike Rgers

 

[joeu2004]

A1 is this month total
B1 is last months total
C1 is the difference, =A1-B1
[...] D1 is the difference in percentage,
I use =(A1-B1)/B1

Of course, you can simplify D1 to: =C1/B1

There are times we don't always handle
a certain item so some months will have
a zero in either this month or last months
calc and my % is all messed up i get this
#DIV/0

This is a problem only when B1 is zero. When A1 is zero, your formula will
correctly return -100%.

There is no mathematically correct percentage difference when B1 is zero.
So you need to implement an arbitrary result.

Since going from "n" to zero is -100%, it might seem reasonable to say that
going from zero to "n" is a 100% change. If that is what you want, then:

=if(B1=0,1,C1/B1)

Alternatively, you might simply what to leave D1 blank in that case. If so,
then:

=if(B1 = 0,"",C1/B1)

Caveat: Normally, B1*(1+D1) will equal A1. But that is not the case when
B1 is zero, no matter what choice you make.

 

[Shane Devenshire]

Hi Joe,

But if B1 = 0 then he gets the #Div/0! error.

=IF(B1,C1/B1,"")

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire

A1 is this month total
B1 is last months total
C1 is the difference, =A1-B1
[...] D1 is the difference in percentage,
I use =(A1-B1)/B1

Of course, you can simplify D1 to: =C1/B1

There are times we don't always handle
a certain item so some months will have
a zero in either this month or last months
calc and my % is all messed up i get this
#DIV/0

This is a problem only when B1 is zero. When A1 is zero, your formula will
correctly return -100%.

There is no mathematically correct percentage difference when B1 is zero.
So you need to implement an arbitrary result.

Since going from "n" to zero is -100%, it might seem reasonable to say that
going from zero to "n" is a 100% change. If that is what you want, then:

=if(B1=0,1,C1/B1)

Alternatively, you might simply what to leave D1 blank in that case. If so,
then:

=if(B1 = 0,"",C1/B1)

Caveat: Normally, B1*(1+D1) will equal A1. But that is not the case when
B1 is zero, no matter what choice you make.

 

[Shane Devenshire]

Hi Mike,

1. All of your formula need to be revised either to C1/B1 or to (A1-B1)/B1

2. =if(A1="", "", B1/A1) revised to =IF(B1="","",C1/B1) will not cathc B1=0

3. =IF(ISBLANK(A1),"",B1/A1) same comment as above. Note OP said "some
months will have a zero"

 

[Shane Devenshire]

Hi,

One method not suggested was

=IFERROR(C1/B1,"")

The will only work in 2007.

 

[joeu2004]

Hi Joe,
But if B1 = 0 then he gets the #Div/0! error.

So what's your point?

I wrote:

=if(B1=0,1,C1/B1)
and
=if(B1=0,"",C1/B1)

Works just fine when B1=0. Perhaps you should try it before posting
criticism.

Be sure to post back with the results of your experiment with my formula.

 

[joeu2004]

PS....

=if(B1=0,1,C1/B1)
and
=if(B1=0,"",C1/B1)

Works just fine when B1=0.

I should have said: works just fine in Excel 2003.

If there is a version of Excel in which that does not work, I'd like to know
about. But I'd be very surprised because the paradigm above is so very
common in Excel.


----- original posting -----