Help

Welcome to the Airtable Community! If you're new here, check out our Getting Started area to get the most out of your community experience.

Need help on efficient way to use IF formula

Topic Labels: Formulas
363 2
cancel
Showing results for 
Search instead for 
Did you mean: 

Hi there,

I wanted to create a formula for a field with 15 variables. See table below. Basically, is % complete + status is what’s on the corresponding column, it updates the “new stat” field.

Tried using this, but wonder if there’s a more efficient way than using ‘IF’ statement 15 times.

IF(AND({% Complete}=0, Stat=‘red’), ‘red’,
IF(AND({% Complete}=0,Stat=‘white’), ‘white’))

Screen Shot 2020-07-24 at 4.48.26 PM

2 Replies 2

There is. Here’s what I do when looking at stuff like this: I look for patterns, and then figure out how to work with those patterns in the simplest way possible.

The first pattern I see is with your {% Complete} values. You essentially have only three options: 0, 100, or somewhere in between. To work with those three, we can use a SWITCH() function. Here’s the basic format:

SWITCH(
    THING_TO_COMPARE,
    OPTION_1, OUTPUT_1,
    OPTION_2, OUTPUT_2,
    ...
    OPTIONAL_FALLBACK_OUTPUT
)

In this case, we can look for 0 and 100 as specific options, and fall back to a different comparison scheme for everything else.

0% is really easy: if it’s not green, we pass it through; otherwise it’s an error.
100% is similarly easy: if it’s not green, it’s an error. Otherwise we keep the green.

Starting with just that setup, here’s what we have:

SWITCH(
    {% Complete},
    0, IF(Stat != "green", Stat, "error"),
    100, IF(Stat != "green", "error", Stat)
)

For values between 0 and 100, I see another pattern: yellow and red pass through untouched, while the others have specific options. For that, we can use another SWITCH() on the status. Here’s the full formula with that added:

SWITCH(
    {% Complete},
    0, IF(Stat != "green", Stat, "error"),
    100, IF(Stat != "green", "error", Stat),
    SWITCH(
        Stat,
        "white", "error",
        "orange", "yellow",
        "green", "error",
        Stat
    )
)

NOTE: If your {% Complete} returns a decimal value (between 0 and 1) instead of a whole number (between 0 and 100), use this formula:

SWITCH(
    {% Complete} * 100,
    0, IF(Stat != "green", Stat, "error"),
    100, IF(Stat != "green", "error", Stat),
    SWITCH(
        Stat,
        "white", "error",
        "orange", "yellow",
        "green", "error",
        Stat
    )
)

Thanks @Justin_Barrett. You are my knight today. I thought about this the whole day yesterday and it gave me a headache. THANKS AGAIN!!!