Formula Help Needed

I need a formula that basically say:

If column one = “no” then give me a “3” or if it is “yes” then give me “1” BUT if status is “cancelled” then give me a “0”

Anyone awesome want to help me out?

Hi @jdss2222 - you can use the SWITCH function to achieve this.

SWITCH({ColumnName}, “no”, 3, “yes”, 1, “cancelled”, 0, 0)

Just replace {ColumnName} with the actual column name you are wanting to check and you should be good to go. The last 0 is a default value you can set to whatever value makes the most sense for you.

Hope this helps,

Mark

3 Likes

Thanks for the quick reply @mark.anley

The issue is the “cancelled” value is in a different column than the “no” value. So really its like “First check if column one says no and if it does then put a 3 o/w 1 and then check the status and if the status is cancelled then ignore the first part and just put a 0”

Does that make sense? I might be going about that order backwards also.

Hey @jdss2222 - thanks for clarifying… You can absolutely do this with nesting your SWITCH Statement into an IF statement. Basically, Cancelled always wins, so we check that first, then move onto the next check if need be. Let’s call your column you want to check which may have “cancelled” {Status1} and the other column {Status2} - it would look something like this:

IF({Status1} = “cancelled”, 0, SWITCH({Status2}, “no”, 3, “yes”, 1, 0))

Again, keeping in mind the last zero (bolded above) is the default value to insert in none of the checks validate.

Hopefully this helps and if you have any other questions feel free to reach out any time.

Mark