IF Formula

[Guest]

Hi,

By using the formula below i am trying to stop the cell in my spreadsheet
displaying #DIV/0! message. could someone offer me a suggestion to tweak it
so that it doesn't as i am stuck!

=IF(F4/H4*100>0,F4/H4*100,"")

Many thanks,
Bryan

 

[Guest]

You may try with:
=IF(H4<>0,F4/H4*100,"")

Miguel.

 

[Guest]

You've got to detect and prevent the division by 0:
=if(h4=0,"",f4/h4*100)

 

[Mark Lincoln]

Try this:

=IF(H4<>0,IF(F4/H4*100>0,F4/H4*100,""),"")

 

[Guest]

Try something like this

=IF(ISERR(F4/H4),"",F4/H4)

This should check to see if the division produces an error (Div by 0
included) and it so it will put a blank, other wise it will do the
calculation.

 

[Guest]

Thanks Everyone thats great all of the formulas worked well.

regards,
Bryan

 

[Dana DeLouis]

F4/H4*100>0

As a very small side note...you could reduce this to: F4/H4 > 0 if you wish.

 

[Guest]

The Problem with your formula is that you used a calculation that divides by
zero in order to find out if it devides by zero. When you run into an error
a small box with an exclimation mark appears. If you click on that and press
Show calculation steps you can see what the computer is doing each step.
Your calculation looks like this (I used 1 as F4 and 0 As H4)

=IF(1/0*100>0,1/0*100,"")
=IF(#DIV/0*100>0,1/0*100,"")
=IF(#DIV/0>0,1/0*100,"")
#DIV/0

The division by zero occured before it could find out if it would happen or
not. So the Error ate right through the formula. Miguels formula (
=IF(H4=0,F4/H4*100,"") ) Works well because it sees if H4 is a zero before it
does any division. Hopefully this explination will help you when you run
into similar Errors in the future.

 

[Guest]

Pardon. When I wrote =IF(H4=0,F4/H4*100,"") it was ment to be
=IF(H4<>0,F4/H4*100,"")