Help

Combining Nested If and Switch Formulas

Topic Labels: Formulas
Solved
Jump to Solution
512 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Eliz_Comm
4 - Data Explorer
4 - Data Explorer

I'm not sure if what I'm trying to do is possible, but here goes:

I'm working off of an existing base template, and am trying to tweak one of the formulas that's in there. The existing formula is as follows:

Screenshot 2023-05-15 at 9.42.53 AM.png

I would like to add one additional condition: if Equipment Status is "Unknown" the result is

" Not Available". Since this is pulling from a different field than the existing SWITCH formula, I can't add it in there; I've tried nesting another "If" within this formula, but that also doesn't seem to work. It may be that I simply can't do this, but if anyone has any ideas or suggestions, I'd appreciate it--I'm pretty new to Airtable formulas, and trying to figure out how best to leverage these formulas. Thanks in advance!
1 Solution

Accepted Solutions
Ben_Young1
11 - Venus
11 - Venus

Hey @Eliz_Comm

Here's the original formula from your screenshot:

IF(
    OR(
        {Equipment Status} = "Out of Commission",
        {Equipment Status} = "Out for Repair",
    ),
    " Removed",
    SWITCH(
        {Status},
        "Checked In",
            "✔ Available",
        "Checked Out",
            " Not Available",
        "✔ Available"
    )
)

In plain language, this formula looks at whether the Equipment Status field returns the value of "Out of Commission" or "Out for Repair".
If that expression evaluates true, the value of " Removed" is returned.
If that expression evaluates false, we default to a SWITCH function that evaluates the Status field values with a fallback value of "✔ Available."

Now, you'd like to add a conditional spike to this formula where the Equipment Status field is "Unknown", the formula should return " Not Available."
There are a couple of possible permutations to this edit, but here's one way to do this:

IF(
    OR(
        {Equipment Status} = "Out of Commission",
        {Equipment Status} = "Out for Repair",
    ),
    " Removed",
    IF(
        {Equipment Status} = "Unknown",
        " Not Available",
        SWITCH(
            {Status},
            "Checked In",
                "✔ Available",
            "Checked Out",
                " Not Available",
            "✔ Available"
        )
    )
)

 

See Solution in Thread

2 Replies 2
Ben_Young1
11 - Venus
11 - Venus

Hey @Eliz_Comm

Here's the original formula from your screenshot:

IF(
    OR(
        {Equipment Status} = "Out of Commission",
        {Equipment Status} = "Out for Repair",
    ),
    " Removed",
    SWITCH(
        {Status},
        "Checked In",
            "✔ Available",
        "Checked Out",
            " Not Available",
        "✔ Available"
    )
)

In plain language, this formula looks at whether the Equipment Status field returns the value of "Out of Commission" or "Out for Repair".
If that expression evaluates true, the value of " Removed" is returned.
If that expression evaluates false, we default to a SWITCH function that evaluates the Status field values with a fallback value of "✔ Available."

Now, you'd like to add a conditional spike to this formula where the Equipment Status field is "Unknown", the formula should return " Not Available."
There are a couple of possible permutations to this edit, but here's one way to do this:

IF(
    OR(
        {Equipment Status} = "Out of Commission",
        {Equipment Status} = "Out for Repair",
    ),
    " Removed",
    IF(
        {Equipment Status} = "Unknown",
        " Not Available",
        SWITCH(
            {Status},
            "Checked In",
                "✔ Available",
            "Checked Out",
                " Not Available",
            "✔ Available"
        )
    )
)

 

This worked perfectly--thank you so much!