Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Jun 22, 2022 02:45 AM
Hello! Do you guys know if it’s possible to create a formula that return a rate (1 to 3) IF:
I tried to use IF, FIND, AND, OR but I couldn’t do it :frowning:
Thanks for support!
Jun 22, 2022 10:12 AM
Hey @Edoardo_Abate!
Welcome in!
So, I presume you’re using a multi-select field.
If you are, then this formula format will work with the parameters you provided:
IF(
{Field},
IF(
{Field} = "option1, option2",
3,
IF(
{Field} = "option1",
2
)
),
1
)
Truth be told, multi-select fields are an absolute pain to work with sometimes.
Here, Airtable treats the multi-select values as a string containing the comma-separated values.
Lemme know if you have any questions or run into any issues!
Jun 23, 2022 01:35 AM
Thank you Ben!!
It works, I’v just modified like this:
IF(
AND(
FIND("option1",{field}),
FIND("option2",{field})
),"5",
IF(
OR(
FIND("option1",{field}),
FIND("option2",{field})
),"4",
IF(
AND(
FIND("option2",{field}),
FIND("option3",{field})
),"3",
IF(
AND(
FIND("option3",{field}),
FIND("option4",{field})
), "1", "2"
)
)
)
)
Thanks a lot :slightly_smiling_face:
Jun 23, 2022 09:30 AM
The OR(option1, option2) condition should be your last condition checked, or should at least be after the AND(option2, option 3) condition. Your formula as written will never check if both 2 and 3 are present since it stops running once 2 is found.
Jun 23, 2022 02:19 PM
You’re right @Kamille_Parks!! I really appreciated your support guys, now everything works correctly :star_struck:
Thanks again
Jun 23, 2022 09:01 PM
Hi,
Depending on your final goal, you can also use variations of:
SWITCH(
8*(FIND(“option1”,{field})>0)+
4*(FIND(“option2”,{field})>0)+
2*(FIND(“option3”,{field})>0)+
(FIND(“option4”,{field})>0),
0,‘empty cell’,
1,‘just option4’,
2,‘just option3’,
3,‘option 3 and 4 together’,
…
15,‘all are present’,
‘some default’
)