Not sure what type of excel formula to use

%3CLINGO-SUB%20id%3D%22lingo-sub-738904%22%20slang%3D%22en-US%22%3ENot%20sure%20what%20type%20of%20excel%20formula%20to%20use%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-738904%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20one%20cell%20(lets%20say%20it%20is%20cell%20Z20)%20in%20which%20I%20need%20several%20things%20evaluated%20and%20different%20things%20done%20based%20upon%20the%20answer.%26nbsp%3B%20This%20is%20based%20upon%20dollar%20amounts%20in%20columns%20N%20and%20L.%26nbsp%3B%20Both%20columns%20COULD%20have%200%20in%20them%2C%20but%20BOTH%20columns%20will%20never%20have%20an%20amount%20over%200%20(one%20column%20will%20always%20be%200)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAmount%20in%20cell%20Z15%20is%20a%20subtotal%20of%20column%20Z.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20N20%20is%20not%200%26nbsp%3B%20then%20multiply%20N20%20by%2015%25%20(result%20in%20Z20)%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B----%20I%20had%20%3DIF(n20%26lt%3B%26gt%3B0%2CN20*15%25%2C%20%22%20%22)%3C%2FP%3E%3CP%3EIf%20L20%20is%20not%200%2C%20then%20amount%20in%20cell%20Z15%20minus%20L20%20(result%20in%20Z20)%26nbsp%3B%20---%20I%20had%26nbsp%3B%3DIF(L20%26lt%3B%26gt%3B0%2CZ15-L20%2C%20%22%20%22)%3C%2FP%3E%3CP%3EIf%20the%20result%20if%20false%20in%20both%20tests%20do%20nothing%20(leave%20blank)%20or%20put%20a%20zero%20in%20it.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20full%20statment%20was%20this%3A%26nbsp%3B%26nbsp%3B%3DIF(N20%26lt%3B%26gt%3B0%2C(N20*15%25)%2CIF(L20%26lt%3B%26gt%3B0%2C(Z15-L20)%2C%22%20%22))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20someone%20help%20me%20with%20this%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-738904%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-738985%22%20slang%3D%22en-US%22%3ERe%3A%20Not%20sure%20what%20type%20of%20excel%20formula%20to%20use%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-738985%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F371377%22%20target%3D%22_blank%22%3E%40jhassi%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYour%20formula%20seems%20that%20it%20can%20be%20reduced%20to%20this%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DIF(L20%2C%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3EZ15-L20%2C%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3EN20*15%25)%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-739911%22%20slang%3D%22en-US%22%3ERe%3A%20Not%20sure%20what%20type%20of%20excel%20formula%20to%20use%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-739911%22%20slang%3D%22en-US%22%3EThis%20doesn't%20address%20the%20logical%20test%20for%20N20%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-739930%22%20slang%3D%22en-US%22%3ERe%3A%20Not%20sure%20what%20type%20of%20excel%20formula%20to%20use%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-739930%22%20slang%3D%22en-US%22%3EMy%20suggested%20formula%20is%20premised%20on%20your%20declaration%20of%20a%20fact%20that%20whenever%20L20%20is%200%2C%20N20%20is%20not%200%2C%20and%20whenever%20N20%20is%200%2C%20L20%20is%20not%200.%20If%20my%20suggested%20formula%20returns%20a%20result%20you%20didn%E2%80%99t%20expect%2C%20it%20presupposes%20that%20you%20may%20have%20to%20modify%20your%20premise.%20The%20logic%20of%20any%20formula%20always%20depends%20upon%20the%20premise%20you%20declare.%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

I have one cell (lets say it is cell Z20) in which I need several things evaluated and different things done based upon the answer.  This is based upon dollar amounts in columns N and L.  Both columns COULD have 0 in them, but BOTH columns will never have an amount over 0 (one column will always be 0)

 

Amount in cell Z15 is a subtotal of column Z.

 

If N20 is not 0  then multiply N20 by 15% (result in Z20)     ---- I had =IF(n20<>0,N20*15%, " ")

If L20 is not 0, then amount in cell Z15 minus L20 (result in Z20)  --- I had =IF(L20<>0,Z15-L20, " ")

If the result if false in both tests do nothing (leave blank) or put a zero in it. 

 

My full statment was this:  =IF(N20<>0,(N20*15%),IF(L20<>0,(Z15-L20)," "))

 

Can someone help me with this?

3 Replies
Highlighted

@jhassi 

Your formula seems that it can be reduced to this: 

=IF(L20,

Z15-L20,

N20*15%)

Highlighted
This doesn't address the logical test for N20
Highlighted
My suggested formula is premised on your declaration of a fact that whenever L20 is 0, N20 is not 0, and whenever N20 is 0, L20 is not 0. If my suggested formula returns a result you didn’t expect, it presupposes that you may have to modify your premise. The logic of any formula always depends upon the premise you declare.