#DIV/0! and sum function

[mimmo_gsr]

Hi all,

I have a spreadsheet with some #DIV/0! on it due to some numbers being 0/0. The 0/0 is correct, but how can I get excel to return a zero answer instead of the #DIV/0!, as when I go to sum up all the number it gives a final answer of #DIV/0!.

I have done a bit of a search to for past questions like this and one used an ISERROR function to try and resolve the issue.

My formula that I am using that returns the #DIV/0! error is as follows

=IF(G11/H11>=1.05,180,IF(G11/H11>=1,140,IF(G11/H11>=0.9,100,0)))

When G11 and H11 = zero it causes a problem.

Is there anyway to modify that formula to return a zero value instead of a #DIV/0! error value?

Or is it possible to modify the sum function to ignor the #DIV/0! or class it as a zero? It is just the sum of a range, ie =SUM(M1:M30)

Any thoughts?

Thanks

Mimmo

 

[masim]

like this ????

=IF(ISERROR(IF(G11/H11>=1.05,180,IF(G11/H11>=1,140,IF(G11/H11>=0.9,100,0)))),0,(IF(G11/H11>=1.05,180,IF(G11/H11>=1,140,IF(G11/H11>=0.9,100,0)))))

Regards
Asim

 

[Scott Huish]

Try this:

=IF(H11=0,0,LOOKUP(G11/H11,{-9.99999999999999E+307,0;0.9,100;1,140;1.05,180}))

 

[mimmo_gsr]

Hi Asim and Hotpepper,

Thanks for your help.

I ended up using =IF(h11<>0,IF(G11/H11>=1.05,180,IF(G11/H11>=1,140,IF(G11/H11>=0.9,100,0))),0)

It has fixed my problem

Regards

Mimmo