Skip to main content
Solved

IF AND formula that ignores one criteria

  • October 11, 2022
  • 5 replies
  • 49 views
N
Forum|alt.badge.img+3

I’m trying to create an IF statement that would return a different result even if something matches the original criteria. I have Client ABC that doesn’t fit into the regular payment schedule, so I want the value “123” to be put as their payment and the have the rest of the clients calculated based on the formula. The problem is that Client ABC has values in each of the computed fields, so the value of those fields is being returned instead of 123. I’ve tried a variety of things without any luck. Thanks for anyone who can help!

If this:

IF({Client Name}=“ABC”),123)

If not:

IF({Payment Schedule}=“Annual”,IF({Contract Length}>=36, 5*{Field 1}))
&IF({Payment Schedule}=“Annual”,IF({Contract Length}=24, 4*{Field 1}))

Best answer by Grunty

That’s just a cut and paste error; I’m using the quotes you suggested. The formulas work separately, but how can I combine them to get the result I need?


Okay. Trying to disentangle that ~~& ~~ spaghetti, I come up with this:

IF({Client Name}="ABC",123,
   IF({Payment Schedule}="Annual",
       IF({Contract Length}>=36, 5*{Field 1},
       IF({Contract Length}=24, 4*{Field 1}))
   )
)

Note that, as it is, the formula will do nothing for Contracts ranging between 25 and 35 months, or shorter than 24 months.

5 replies

Grunty
Forum|alt.badge.img+15
  • Grunty
  • Inspiring
  • October 11, 2022

Hi Nicole,
You’re using typographic double quotes “ ” but those aren’t understood by the formula editor.
Use simple double quotes instead: " "

N
Forum|alt.badge.img+3
  • Nicole_N
  • Author
  • Participating Frequently
  • October 11, 2022

Hi Nicole,
You’re using typographic double quotes “ ” but those aren’t understood by the formula editor.
Use simple double quotes instead: " "


That’s just a cut and paste error; I’m using the quotes you suggested. The formulas work separately, but how can I combine them to get the result I need?

N
Forum|alt.badge.img+3
  • Nicole_N
  • Author
  • Participating Frequently
  • October 11, 2022

That’s just a cut and paste error; I’m using the quotes you suggested. The formulas work separately, but how can I combine them to get the result I need?


Sorry, the first statement had an extra ). This is the correct one
IF({Client Name}=“ABC”,123)

Grunty
Forum|alt.badge.img+15
  • Grunty
  • Inspiring
  • Answer
  • October 11, 2022

That’s just a cut and paste error; I’m using the quotes you suggested. The formulas work separately, but how can I combine them to get the result I need?


Okay. Trying to disentangle that ~~& ~~ spaghetti, I come up with this:

IF({Client Name}="ABC",123,
   IF({Payment Schedule}="Annual",
       IF({Contract Length}>=36, 5*{Field 1},
       IF({Contract Length}=24, 4*{Field 1}))
   )
)

Note that, as it is, the formula will do nothing for Contracts ranging between 25 and 35 months, or shorter than 24 months.

N
Forum|alt.badge.img+3
  • Nicole_N
  • Author
  • Participating Frequently
  • October 11, 2022

Okay. Trying to disentangle that ~~& ~~ spaghetti, I come up with this:

IF({Client Name}="ABC",123,
   IF({Payment Schedule}="Annual",
       IF({Contract Length}>=36, 5*{Field 1},
       IF({Contract Length}=24, 4*{Field 1}))
   )
)

Note that, as it is, the formula will do nothing for Contracts ranging between 25 and 35 months, or shorter than 24 months.


That worked! Thank you!

Terms & ConditionsAccessibility statement