Help! How can I add a multiple IF statement

Platform discussions Special workflows & use cases
1

Hi,

I’m struggling getting a formula o work and not sure why it keeps saying its an illegal statement. I want to interigate 2 columns, for example IF the column ‘Free of Charge?’ states yes then add FOC otherwise IF over 70 add On Track :green_circle:, if under 60 add this Action needed :red_circle: etc

IF(AND{Free of Charge?}=“yes”,{LIVE EST vs ACTUAL cost %}>70,“FOC”, “On Track :green_circle:”, IF(AND{Free of Charge?}=“yes”,{LIVE EST vs ACTUAL cost %}<60,“FOC”, “Action needed :red_circle:”,IF(AND{Free of Charge?}=“yes”,{LIVE EST vs ACTUAL cost %}<5,“FOC”, “timesheet data needed”,“Caution :orange_circle:”)))

Thanks,
Ruth

2

I suggest breaking it up into multiple recipies.
for example IF the column ‘Free of Charge?’ states yes then add FOC
IF over 70 add On Track :green_circle:
if under 60 add this Action needed :red_circle:

3

@RuthQ

I’m having trouble understanding what exactly you are trying to accomplish… So, let’s take the first thing I see and then go from there.

AND() is a function similar to other functions. The general syntax is AND(condition test 1, condition test 2,…). The function returns a value of TRUE if and only if all the condition tests are true.

For example, if we wanted to return “Good” when 3 number columns ({V1}, {V2}, {V3}) were all greater than 6 and “Bad” otherwise, this would be our formula:

IF(AND({V1} > 6, {V2} > 6, {V3} > 6), "Good", "Bad")

Using the above, make the needed adjustments. If that does not get it working, repost and we will go from there.

Jim - The Monday Man (YouTube Channel)
Watch Our Latest Video: Column Total in monday.com Formulas? YES!!!

4

Hi,

Thanks for your help, I see that explains a lot and the reason I was getting ‘true’!

Sorry its a tricky one to explain, This is my current formula and all working:
IF({LIVE EST vs ACTUAL cost %}>70,“On Track :green_circle:”,IF({LIVE EST vs ACTUAL cost %}<5,“timesheet data needed”,IF({LIVE EST vs ACTUAL cost %}<60,“Action needed :red_circle:”,“Caution :orange_circle:”)))

However I now want it to cross reference ‘Free of Charge?’ column and if that says ‘yes’ it should be ‘FOC’ if not it should work the same work as above.

Hope this makes sense?

Thanks,
Ruth

5

@RuthQ

K, try this:

IF({Free of Charge?} = "yes", "FOC",
IF({LIVE EST vs ACTUAL cost %} > 70, "On Track :green_circle:", IF({LIVE EST vs ACTUAL cost %} < 5, "timesheet data needed", IF({LIVE EST vs ACTUAL cost %} < 60, "Action needed :red_circle:", "Caution :orange_circle:"))))
6

Hey, thanks for your help, that worked to get the live tracking but unfortunately hasn’t recognised that some projects are yes and therefore ignores FOC for these, see screen grab for a little more clarity
image

7

@RuthQ

The string compare is case sensitive. This will fix it:

IF(LOWER({Free of Charge?}) = "yes", "FOC",
IF({LIVE EST vs ACTUAL cost %} > 70, "On Track :green_circle:", IF({LIVE EST vs ACTUAL cost %} < 5, "timesheet data needed", IF({LIVE EST vs ACTUAL cost %} < 60, "Action needed :red_circle:", "Caution :orange_circle:"))))
8

Thank you so much for your help, thats worked :grinning:

9

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.