Excel IF Function

[phatboyslim46]

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?

Thanks in advance for your advice, it'll surely helps!

[#2 · Accepted solution]

@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.

[#3]

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!!