Home

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

@jhassi 

Your formula seems that it can be reduced to this: 

=IF(L20,

Z15-L20,

N20*15%)

This doesn't address the logical test for N20
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.
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies