IF, AND Formula Help

[Alex_Cristian]

Hello all,

I’m trying to create an IF(AND( condition for the following statement and seem to be doing something wrong:

IF(AND({Nr. Of Payments}=“2 Payments”, {Deposit Made}=FALSE()),“2 Payments Left”,
IF(AND(Nr. Of Payments} =“2 Payments”, {Deposit Made}=TRUE()),“1 Payment Left”,
IF(AND(Nr. Of Payments}=“2 Payments”, {Deposit Made}=TRUE(),{Final Payment}=TRUE()),“Paid In Full”,0))))

Any input would be very much appreciated.

Thank you!

[JonathanBowen]

Hi @Alex_Cristian - there’s a couple of typos in the formula that are stopping it from being saved in the field:

• There’s an additional closing bracket at the end of the formula which should be removed
• There’s a couple of instances of “Nr. Of Payments” missing its first curly bracket (lines 2 and 3).
• It is possible that the formatted quotes are also a problem but that could be the forum site doing this formatting and not what was entered into AT.

However, fixing all of these won’t fix your formula - there are a couple of logic errors to contend with too.

• {Nr. Of Payments} = '2 Payments' appears in all 3 IF statements which makes it redundant and not required (unless there could be other values in this field)
• On the assumption that you don’t need {Nr. Of Payments} = '2 Payments', then you have the set of possible values of the other two fields to consider to determine the status:

Both checked, both unchecked, deposit checked/final not, deposit not checked/final checked. Some of these may be “error” states, e.g. deposit not checked, final checked, but they should still be account for.

The reason for noting these states is that your second IF statement:

IF(AND({Nr. Of Payments} =“2 Payments”, {Deposit Made}=TRUE()),“1 Payment Left”,

does not take account of the final payment status and so you will end up with “1 Payment Left” displaying whether final payment is checked or not. So, the answer here is to decide what all the combinations of states mean - perhaps something like this:

This would lead you to a formula of:

IF(
  AND(NOT({Deposit Made}), NOT({Final Payment})),
  '2 payments left',
  IF(
    AND({Deposit Made}, NOT({Final Payment})),
    '1 payment left',
    IF(AND(NOT({Deposit Made}), {Final Payment}),
    'ERROR!',
    'Paid in full'
    )
  )
)

Note: I prefer to use:

IF({Deposit Made}, ....)

rather than:

IF({Deposit Made}=TRUE(), ....)

Hope this helps!

JB

[Alex_Cristian]

Thank you so much! For explaining it so thoroughly and the formula. It works perfectly! :slightly_smiling_face:

[Alex_Cristian]

Hey JB,

I have one other question! You mentioned the formula would be different if there were more options (other than just {2 Payments}. If I wanted to add {3 Payments} and {4 Payments} to the mix, what would that look like?

Thanks again!
Alex

[JonathanBowen]

Hi @Alex_Cristian - well, probably a few ways to achieve this. One way would be to turn the # of Payments field into an integer and then have a number of columns for the maximum number of payments you could receive:

Then, you’d want your status formula to respond to the # of payment value, perhaps like this:

SWITCH(
  {Nr. Of Payments},
  2, IF(
    OR({Second Payment}, {Third Payment}),
    'ERROR!!',
    IF(
      AND(NOT({Deposit Made}), NOT({First Payment})),
      '2 payments left',
      IF(
        AND({Deposit Made}, NOT({First Payment})),
        '1 payment left',
        IF(
          AND({Deposit Made}, {First Payment}),
          'Paid in full',
          IF(
            AND(NOT({Deposit Made}), {First Payment}),
            'ERROR!!'
          )          
        )
      )
    )
  ),
  3, IF(...),
  4, IF(...) 
)

This uses the SWITCH function based on the # of payments value. The formula feels a bit complicated and there might be a more elegant way to do this, although it does work for case “2”.

What this is saying is:

• In the case # of payments = 2
• If Second or Third are checked, then ERROR (as this shouldn’t be allowed if there’s only 2 payments)
• Then evaluate the status based on the deposit and first payment fields using the 4 possible scenarios shown above.

Obviously this would need to be extended out for 3, 4, 5 payments, but would essentially follow the same pattern.

JB