Forum Discussion

jhassi's avatar
jhassi
Copper Contributor
Jul 05, 2019

Not sure what type of excel formula to use

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?

excel
Formulas and Functions

3 Replies

  • Twifoo's avatar
    Twifoo
    Silver Contributor
    Jul 05, 2019

    jhassi 

    Your formula seems that it can be reduced to this: 

    =IF(L20,

    Z15-L20,

    N20*15%)

    • jhassi's avatar
      jhassi
      Copper Contributor
      Jul 05, 2019
      This doesn't address the logical test for N20
      • Twifoo's avatar
        Twifoo
        Silver Contributor
        Jul 05, 2019
        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.

Resources