Search code examples
excelexcel-formulanestedifs

Multiple IFs in same formula

Please can someone help me combine the following:

  1. If P20="1" and F27="" then the value should be 0.00

  2. If P20="2" then, if B22 is greater than or equal to 1000:

    (B22-1000)*0.06+200

    but if B22 is less than 1000:

    B22*0.2

  3. If P20="3" then:

    B22*F27*E3

The below bring back a value of FALSE

 =IF(AND(P20="1",F27=""),0,IF(P20="2",IFS(B22>1000,(B22-1000)*0.06+200,B22<1001,B22*0.2),IF(P20="3",B22*F27*E3)))
Solution

  • Try this:

    =IF(AND(P20=1,F27=""),0,IF(P20=2,IF(B22>=1000,(B22-1000)*0.06+200,B22*0.2),IF(P20=3,B22*F27*E3)))
    1. I dropped the IFS, because normal IF contains a THEN and an ELSE part.
    2. I work with P20=1 instead of P20="1".
    3. Not mentioned in the formula: I've modified "cell formatting" to "number".