#DIV/0! Problem

[Michael Keene]

I'm sure there is a simply fix but I don't know what it is. My formula
reads: =SUM(B4:W4)/SUM(B3:W3). If I have no values entered within the
specified range I get the #DIV/0! error. How should I write the
formula to have the cell show a 0 (zero) until values are entered?

Thanks

 

[Niek Otten]

=IF(SUM(B3:W3)=0,0,SUM(B4:W4)/SUM(B3:W3))

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[Anders Silven]

One way,

=IF(SUM(B3:W3)=0,0,SUM(B4:W4)/SUM(B3:W3))

HTH
Anders Silven

 

[EARTHWALKER]

Hello m8. Not being an expert this may not help yo, but is is a
solution. It's one I use in one of my many spreadsheets.

M2 contains a value or a DIV/0
N2 has this =IF(ISERROR(M2),"0",M2)

So basically I have Column N hidden and M shows the values as true
values or replaces the div0's with 0's

 

[EARTHWALKER]

I tried doing it the way Niek Otten posted, but as the cells M2 wa
looking at were formulas themselves and not actual values, it went
bit pear shaped :

 

[Soo Cheon Jheong]

Michael Keene,


=IF(ISERROR(B3:W4),0,IF(SUM(B3:W3)=0,0,SUM(B4:F4)/SUM(B3:F3)))


--
Regards,
Soo Cheon Jheong
Seoul, Korea
_ _
^¢¯^
--

 

[Harlan Grove]

M2 contains a value or a DIV/0
N2 has this =IF(ISERROR(M2),"0",M2)

....

ISERROR catches too much. OP only wants to catch divide by zero, and that's
most easily done by comparing the denominator term to zero. Also, why "0"
rather than just 0?

 

[Harlan Grove]

=IF(ISERROR(B3:W4),0,IF(SUM(B3:W3)=0,0,SUM(B4:F4)/SUM(B3:F3)))

Very bad idea. Errors generally are useful diagnostics, and should be
trapped only under limited circumstances, such as comparing denominators to
zero. So your first IF is a bad idea.

 

[Soo Cheon Jheong]

Thanks Harlan for the help.