Forum Discussion
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?
Thanks in advance for your advice, it'll surely helps!
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.
2 Replies
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.
- 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!!
