#DIV/0! error

[Simon Plumb]

Dear All

I am sure this problem I have can be easily resolved however, I have tried
all the online help without success.

I want to work out the percentage of two numbers however, to be able include
zeros as part of the template. Example C1/B1=D1 as a percentage. Obviously
when zero's are entered into the cells it generates the '#DIV/0!'.

I have tried entering '=IF(C1=0,"",C1/B1) but it still comes back with the
error. Additionally, can you write a conditional format that changes what
is displayed when you get an error message? IE. =IF(D1=#DIV/0!,"100")?
This doesn't work either.

Any help would be appreciated.

Thank you.

 

[Niek Otten]

Hi Simon,

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

It's the B1 that causes the trouble, not the C1

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[John Wilson]

Simon,

You're still getting the error because you're testing the wrong cell.
That error is a Div "by" Zero error so your formula should read:

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

If you want to check if both are zero, you could use the following:

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

John

 

[Simon Plumb]

I have tried both of the replies posted and they don't want to work.

Can you clarify how the sum should look in the cell. The cells that I enter
the numbers in are B4 and C4 and the answer should be in D4. So I am
correct in saying that the D4 should contain the following sum in it?
=SUM(C4/B4%) Then I add the conditional formatting formula
=IF(C1*B1=0,"",C1/B1) to check that the numbers entered in either B4 or D4
or both could be a zero.

Have I understood this right or am I having an out of body experience?

Thanks again

 

[Niek Otten]

Hi Simon,

No conditional formatting, no need for SUM. Just enter the formula

=IF(B1=0,"",C1/B1) or =IF(B1=0,"",C1/B1%)
--

Kind Regards,

Niek Otten

Microsoft MVP - Excel


and format as required.

 

[Simon Plumb]

A BIG Thank you

Hi Simon,

No conditional formatting, no need for SUM. Just enter the formula

=IF(B1=0,"",C1/B1) or =IF(B1=0,"",C1/B1%)
--

Kind Regards,

Niek Otten

Microsoft MVP - Excel


and format as required.

 

[Simon Plumb]

One final question, if I enter a number in the cell C1 it remains blank. It
is only when I add a number to B1 that anything is visible.

I know I am a pain but it really is appreciated!!!!

 

[Niek Otten]

That is exactly what the formula is supposed to do. If there is noting in
B1, then the division is not valid.
Although from a math point if view incorrect, you could of course change
your formula to

=IF(B1=0,0,C1/B1), so that it shows zero as long as there is nothing in B1.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[Simon Plumb]

Hi Niek

Another good example which I will save for the future, however, if I explain
what it is for then it might be clearer.

Please accept my apologies for not being clearer from the beginning.

The idea of this spreadsheet is to track reviews that managers have
completed. IF column B is planned reviews and column C is completed
reviews, column D will be the % that they have completed.

Your fomula is great until someone completes a review that has not been
planned, technically they should have a percentage completed as 200%.

Does this make sense?

 

[Niek Otten]

Hi Simon,

I wouldn't dare to say this doesn't make sense! And of course you can change
the formula to show "200%".
But I don't think you'll find a lot of support among math teachers for the
technical correctness!

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[AlfD]

Simon:
The fact is, Excel will generally do what you ask it (or tell it) a
long as you ask (or tell) it nicely. (Remember when the word "nice
meant "precise"?)

For example, you could put something like =IF(B4=0,"",IF(C4>B4,"100
and " & C4-B4 & " extra reviews done",C4/B4))
in D4 if you want to draw attention to the situatioin of a
"over-producer". Watch the spaces among the concatenated bits.

Al