SOLVED

Excel IF Function

%3CLINGO-SUB%20id%3D%22lingo-sub-2830598%22%20slang%3D%22en-US%22%3EExcel%20IF%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2830598%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20everyone%2C%20I'm%20kinda%20stuck%20with%20this%20equation%20and%20it%20keep%20showing%20%3CSTRONG%3E'%23DIV%2F0!'%3C%2FSTRONG%3E.%20For%20you%20reference%2C%20this%20is%20the%20%3CSTRONG%3EIF%3C%2FSTRONG%3E%20Equation%20I'm%20dealing%20with%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DIF(SUM(L20%3AN20)%2FSUM(L%2418%3AN%2418)%3D0%2C%22%22%2CSUM(L20%3AN20)%2FSUM(L%2418%3AN%2418)*O%2418)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDon't%20know%20where%20the%20problems%20is%2C%20I'm%20trying%20to%20return%20cell%20blank%20if%20there%20is%20no%20value%20set%20in.%20Or%2C%20do%20I%20have%20to%20use%3CSTRONG%3E%20IFERROR%3C%2FSTRONG%3E%20instead%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance%20for%20your%20advice%2C%20it'll%20surely%20helps!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2830598%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2830677%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20IF%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2830677%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1180919%22%20target%3D%22_blank%22%3E%40phatboyslim46%3C%2FA%3E%26nbsp%3BDividing%20something%20by%20zero%20will%20return%20the%20%23DIV%2F0!%20error.%20Dividing%20or%20multiplying%20zero%20by%20anything%20will%20return%20zero.%20The%20easiest%20way%20to%20deal%20with%20this%20is%20to%20use%20the%20formula%20does%20the%20calculation%20you%20want.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DSUM(L20%3AN20)%2FSUM(L%2418%3AN%2418)*O%2418%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eand%2C%20indeed%20wrap%20it%20in%20IFERROR%20like%20so%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DIFERROR(SUM(L20%3AN20)%2FSUM(L%2418%3AN%2418)*O%2418%2C%22%22)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%2C%20if%20you%20also%20want%20to%20suppress%20a%20zero%20in%20cases%20where%20the%20sum%20of%20L20%3AN20%20or%20the%20value%20in%20O18%20equals%20zero%2C%20you%20can%20use%20custom%20formatting%20(%20%3CSTRONG%3E0%3B0%3B%3C%2FSTRONG%3E%20)to%20display%20zeros%20as%20blanks.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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 Replies
best response confirmed by phatboyslim46 (New Contributor)
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.

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