Formula returns #DIV/0 error, please help me fix it

[cdc_979]

Hi, I'm working on updating a spreadsheet someone else created, and see there are many #DIV/0 erros.
The formula is =IF(H7<B39*(I7/(I7+I8+I9+I10)),H7,B39*(I7/(I7+I8+I9+I10)))

When (I7+I8+I9+I10) returns a result of "0" that's when I get the #DIV/0 error. Can someone help me update the formula so if the result is an error, that it's either blank or "0"?

Thanks, I've looked through forums and tried different suggestions, but I can't seem to figure it out.
cdc

 

[purpleozzie]

Try the following

<b39*(i7 (i7+i8+i9+i10)),h7,b39*(i7="" (i7+i8+i9+i10))),="" "")

=IFERROR(IF(H7< B39*(I7/(I7+I8+I9+I10)),H7,B39*(I7/(I7+I8+I9+I10))), "")

Should work<b39*(i7 (i7+i8+i9+i10)),h7,b39*(i7="" (i7+i8+i9+i10))),="" "")<="" html=""></b39*(i7></b39*(i7>

 

[MrTeeny]

You can wrap your formula in =IFERROR(A2/A3,0)



=IFERROR(IF(H7< B39*(I7/(I7+I8+I9+I10)),H7,B39*(I7/(I7+I8+I9+I10))),0)
or =IFERROR(IF(H7< B39*(I7/(I7+I8+I9+I10)),H7,B39*(I7/(I7+I8+I9+I10))),"") if you want a blank displayed

 

[purpleozzie]

Hi MrTeeny,

Just for your info it took me ages to post as there's a bug with the forum.. If you have "<" and then a letter it cuts the formula off.. You need to leave a space between these two characters..

 

[Jonmo1]

First, we can simplify the original formula signifigantly..

Instead of
If(H7 < calculation,H7,calculation)<calculation,h7,calculation)
You can do
=MIN(H7,calculation)

Much simpler, and more importantly, more efficient as it only does the calculation once.

Then we just need to add iferror

Try
=IFERROR(MIN(H7,B39*(I7/(I7+I8+I9+I10))),"")</calculation,h7,calculation)

 

[cdc_979]

Try the following

<b39*(i7 (i7+i8+i9+i10)),h7,b39*(i7="" (i7+i8+i9+i10))),="" "")

=IFERROR(IF(H7< B39*(I7/(I7+I8+I9+I10)),H7,B39*(I7/(I7+I8+I9+I10))), "")

Should work<b39*(i7 (i7+i8+i9+i10)),h7,b39*(i7="" (i7+i8+i9+i10))),="" "")<="" html=""></b39*(i7></b39*(i7>

Thank you! It worked. I wasn't sure where to put the IFERROR, been a while since I've done this.

 

[cdc_979]

Thanks for simplifying the formula! So far so good

 

[Peter_SSs]

If any of the input cells themselves contain an error, wrapping any formula in IFERROR will mask that error. That may may not be possible with your data or "" may be what you want returned in that instance anyway.
However, your question was about how to deal with the #DIV/0! error. This is how I would address that error without interfering with other types of errors that you may still want to know about.

=IF(SUM(I7:I10),MIN(H7,B39*I7/SUM(I7:I10)),"")