 SOLVED

# Excel IF Function

Hi everyone, I'm kinda stuck with this equation and it keep showing '#DIV/0!'. For you reference, this is the IF Equation I'm dealing with,

=IF(SUM(L20:N20)/SUM(L\$18:N\$18)=0,"",SUM(L20:N20)/SUM(L\$18:N\$18)*O\$18)

Don't know where the problems is, I'm trying to return cell blank if there is no value set in. Or, do I have to use IFERROR instead?

2 Replies
best response confirmed by phatboyslim46 (New Contributor)
Solution

# Re: Excel IF Function

@phatboyslim46 Dividing something by zero will return the #DIV/0! error. Dividing or multiplying zero by anything will return zero. The easiest way to deal with this is to use the formula does the calculation you want.

=SUM(L20:N20)/SUM(L\$18:N\$18)*O\$18

and, indeed wrap it in IFERROR like so:

=IFERROR(SUM(L20:N20)/SUM(L\$18:N\$18)*O\$18,"")

Now, if you also want to suppress a zero in cases where the sum of L20:N20 or the value in O18 equals zero, you can use custom formatting ( 0;0; )to display zeros as blanks.

# Re: Excel IF Function

Thanks Mr Riny_van_Eekelen, your response surely helped me a lot! I'm a little confused earlier whether to use IF, IFS, IFNA or IFERROR. Now I get it. Thanks again! Cheers!!