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?
Solved! Go to Solution.
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โ )
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:
I seem to get this error, any advice?
Cannot compare List with Text in ([Verticals] = โCreatorโ)
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:
Thanks for the reply, however the error i got was โANY function is used incorrectlyโ
What is the type of your [Objective] column ?
Enum
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
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
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.
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?
Please have a look here:
Tell us is this is not enough
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.
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โ )
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.
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.
Thank You so much, I have learnt a great deal. If I face any other issues, Iโll drop you a message.
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
User | Count |
---|---|
40 | |
34 | |
29 | |
23 | |
17 |