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*1000,F4/H4*100,"")

Many thanks,
Bryan

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

Miguel.

"Bryan" wrote:

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*1000,F4/H4*100,"")

Many thanks,
Bryan

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

"Bryan" wrote:

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*1000,F4/H4*100,"")

Many thanks,
Bryan

Try this:

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

Thanks Everyone thats great all of the formulas worked well.

regards,
Bryan

"Mark Lincoln" wrote:

Try this:

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

F4/H4*1000

As a very small side note...you could reduce this to: F4/H4 0 if you wish.
--
HTH. :)
Dana DeLouis

"Bryan" wrote in message
...
Thanks Everyone thats great all of the formulas worked well.

regards,
Bryan

"Mark Lincoln" wrote:

Try this:

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

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*1000,1/0*100,"")
=IF(#DIV/0*1000,1/0*100,"")
=IF(#DIV/00,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.

"Bryan" wrote:

Thanks Everyone thats great all of the formulas worked well.

regards,
Bryan

"Mark Lincoln" wrote:

Try this:

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

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

"Abode" wrote:

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*1000,1/0*100,"")
=IF(#DIV/0*1000,1/0*100,"")
=IF(#DIV/00,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.

"Bryan" wrote:

Thanks Everyone thats great all of the formulas worked well.

regards,
Bryan

"Mark Lincoln" wrote:

Try this:

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

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.


"Bryan" wrote:

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*1000,F4/H4*100,"")

Many thanks,
Bryan