Solved: Multiple conditions to get a pre-defined result

Wee_Chin_Lim · 04-21-2021 02:22 AM

Hi All,

I have three columns here, Objective (Enum), Verticals (EnumList) and Sub Programme (Text).

If i wish to have Sub Programme to have a pre-defined result D, when Objective = A and Verticals = B and Verticals = C, is there any way for me to go about doing this?

Aurelien

You are on the good way

The first input of each pair must be a condition, and that means you can include as many AND, OR, IF, etc as you want in it.
These are evaluated in a sequential way, which means the first condition that returns a Yes will stop the evaluation and give the result.
In your case, because you had first the condition [Objective] = “Monetization” to return Yes, then the following condition is not evaluated.
You may want to evaluate double conditions, so you should use the AND operator.

Try something like this:

IFS(
   AND([Objective]="Monetization",
       IN(“Creator”,[Verticals])
   ), 
   “Brand Jam”,

   AND([Objective]="Monetization",
         IN(“Musics”,[Verticals])
   ), 
   “Monetization Workshop”,

   otherCondition,
   “otherResult”
)

View solution in original post

Aurelien

HI @Wee_Chin_Lim

Yes, you could use the SWITCH, IF, AND, or OR Expressions.
Some examples here:

AND({condition 1},{condition 2},{condition 3},..,{condition n})
OR({condition 1},{condition 2},{condition 3},..,{condition n})
IF(<condition>,<then-expression>,<else-expression>)
IFS(<condition1>,<then-expression1>, <condition2>, <then-expression2>, ...)
SWITCH(<expression>, <value1>, <result1>, <value2>, <result2>, ..., <default_result>)

One example with your description:

IF(
   AND([Objective]=A,
      OR([Verticals]=B,
         [Verticals]=C
      )
   ),
   "text1",
   "text2"
)

Please have a look here:

Wee_Chin_Lim

I seem to get this error, any advice?

Cannot compare List with Text in ([Verticals] = “Creator”)

Aurelien

Yes you are right, this is because you have an enumList Type with Verticals.
Problem is: enumList allows you to pick various item.
If tou set your column to “Enum” type, that will restrains the choice to one item only.
In this case, please replace my formula with

IF(
   AND(ANY([Objective])=A,
      OR(ANY([Verticals])=B,
         ANY([Verticals])=C
      )
   ),
   "text1",
   "text2"
)

If you want to keep enumList Type for Verticals, then prefer

IF(
   AND(ANY([Objective])=A,
      OR(IN(B,[Verticals]),
         IN(C,[Verticals])
      )
   ),
   "text1",
   "text2"
)

Please have a look there for further explanations:

And feel free to type in the search bar some keyWords that may help you, like “list”, in the documentation:

Wee_Chin_Lim

Thanks for the reply, however the error i got was “ANY function is used incorrectly”

Aurelien

What is the type of your [Objective] column ?

Wee_Chin_Lim

Enum

Aurelien

I must have said a mistake.

Can you try without the ANY, then ?

IF(
   AND([Objective]=A,
      OR(IN(B,[Verticals]),
         IN(C,[Verticals])
      )
   ),
   "text1",
   "text2"
)

And if that does not work, can you please provide a capture from the table, and from the formula you wrote ?
Thanks

Wee_Chin_Lim

So I’ve tried without the ANY and it works as seen in the photo here.

However, based on the second screenshot below, I wish to have an expression where if I select the Objective as “Monetization”, Vertical as “Creator”, the Sub Programme will be “Brand Jam”, and when if with the other Verticals that I have selected are “Creator”, “Music”, or any others, I want the Sub Programme to show “Monetization Workshop” instead.

I hope that is clear! Thank You

Aurelien

Glad to hear that worked

For the rest of your message, it’s very clear.
In that case, please refer to my first answer Multiple conditions to get a pre-defined result - #2 by Aurelien

More specifically, I think the IFS expression would fit your needs.

Wee_Chin_Lim

I tried to integrate the solution as IFS expression, but I am unsure as to where went wrong, may I know how should the code be like?

Aurelien

Please have a look here:

Tell us is this is not enough

Wee_Chin_Lim

I tried to make this in this manner,

IFS(
[Objective] = “Monetization”, “Test”,
IN(“Creator”,[Verticals]), “Brand Jam”,
IN(“Music”,[Verticals]), “Monetization Workshop”
)

but something is wrong, for when i select Monetization, Sub Programme = Test. I thought if i then selected Creator, Sub Programme will change to Brand Jam, but it didnt work.

Aurelien

You are on the good way

The first input of each pair must be a condition, and that means you can include as many AND, OR, IF, etc as you want in it.
These are evaluated in a sequential way, which means the first condition that returns a Yes will stop the evaluation and give the result.
In your case, because you had first the condition [Objective] = “Monetization” to return Yes, then the following condition is not evaluated.
You may want to evaluate double conditions, so you should use the AND operator.

Try something like this:

IFS(
   AND([Objective]="Monetization",
       IN(“Creator”,[Verticals])
   ), 
   “Brand Jam”,

   AND([Objective]="Monetization",
         IN(“Musics”,[Verticals])
   ), 
   “Monetization Workshop”,

   otherCondition,
   “otherResult”
)

Wee_Chin_Lim

In this case, I thought it would work in this manner, where if it hits all 3 conditions it will be give the predefined value. But it doesnt seem to be the case.

Aurelien

you can add a NOT() to avoid the “Music” in the first condition.

Remember that :

These are evaluated in a sequential way , which means the first condition that returns a Yes will stop the evaluation and give the result.

Wee_Chin_Lim

Thank You so much, I have learnt a great deal. If I face any other issues, I’ll drop you a message.

Aurelien

You are welcome,
I’m glad you learnt things and managed to do what you wanted
Don’t forget to tick “solution” if that answered your need, in case that may help people in your initial situation