Percentage with a zero involved

  • Thread starter Thread starter tankerman
  • Start date Start date
T

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.
 
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.
Click to expand...

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))
 
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
 
tankerman said:
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
Click to expand...

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
Click to expand...

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


[email protected] said:
tankerman said:
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
Click to expand...

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
Click to expand...

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.
Click to expand...
 
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"
 
Hi,

One method not suggested was

=IFERROR(C1/B1,"")

The will only work in 2007.
 
Shane Devenshire said:
Hi Joe,
But if B1 = 0 then he gets the #Div/0! error.
Click to expand...

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.
 
PS....
=if(B1=0,1,C1/B1)
and
=if(B1=0,"",C1/B1)

Works just fine when B1=0.
Click to expand...

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 -----
 
Back
Top