Dividing by zero with nested IF's

[Scott Krause]

I'm working with excel 2003, I've got one column (E) being divided by another (H). If column E is 0 I get #DIV/0!. Here's the formula I used, what can I do to have 0 entered instead of the error?

=IF(H6/E6<0.5,0,IF(H6/E6<0.67,1,IF(H6/E6<0.83,2,IF(H6/E6<1,3,IF(H6/E6<1.17,4,5)))))

Thanks again

 

[mikerickson]

Try
=IF(E6=0, 0 , MATCH(H6/E6,{-9.9E-199,0.5,0.67,0.83,1,1,17})-1 )

 

[edclarke]

Or =IF(E6="", 0, IF(E6=0,0, IF(H6/E6<0.5,0,IF(H6/E6<0.67,1,IF(H6/E6<0.83,2,IF(H6/E6<1,3,IF(H6/E6<1.17,4,5)))))))

Which probably isn't as efficient as Mikes

 

[Scott Krause]

it entered a zero but also said it was an inconsistent formula.

 

[edclarke]

Inconsistent with the formula around it?? This would be because you haven't changed them all to the new formula yet