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.

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

--
regards

"tankerman" wrote:

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

A few options he

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" wrote:

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" wrote:
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.

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


" wrote:

"tankerman" wrote:
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.

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"

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

Cheers,
Shane Devenshire


"Mike Rogers" wrote:

tankerman

A few options he

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" wrote:

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.

Hi,

One method not suggested was

=IFERROR(C1/B1,"")

The will only work in 2007.

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

Cheers,
Shane Devenshire


"tankerman" wrote:

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.

"Shane Devenshire" wrote:
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.

PS....

I wrote:
=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 -----

" wrote:

"Shane Devenshire" wrote:
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.