0

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)))
Nene
  • 55
  • 1
  • 7
  • That second thing makes no sense: it should be: If P20="2" AND ... (and even then the last part is wrong). – Dominique Aug 11 '21 at 09:19
  • Hi @Dominique, thank you for your comment. I've amended my question and the formula slightly but am now getting a 'FALSE' value. Please can you advise what is wrong. I can make each part of the formula work on its own but don't know how to combine them properly. I'm new to this and just learning as I go. – Nene Aug 11 '21 at 10:29

1 Answers1

1

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".
Dominique
  • 16,450
  • 15
  • 56
  • 112