Help with a Nested If Statement

Question

Hi - I am trying to write out a long nested IF statement and am having issues! Can anyone help with this formula?
IF(AND({What category survey did you work on?}=“Category 1”,{Did you also conduct Qual Coding?}=“No”),{Cat 1 Survey Rate (LC) (from Countries | 60dB base)},
IF(AND({What category survey did you work on?}=“Category 1”,{Did you also conduct Qual Coding?}=“Yes”),{Cat 1 QC Survey Rate (LC) (from Countries | 60dB base)},
IF(AND({What category survey did you work on?}=“Category 2”,{Did you also conduct Qual Coding?}=“No”), {Cat 2 Survey Rate (LC) (from Countries | 60dB base)},
IF(AND(
{What category survey did you work on?}=“Category 2”,{Did you also conduct Qual Coding?}=“Yes”),{Cat 2 QC Survey Rate (LC) (from Countries | 60dB base)},
IF(AND({What category survey did you work on?}=“Category 3”,{Did you also conduct Qual Coding?}=“Yes”),{Cat 3 QC Survey Rate (LC) (from Countries | 60dB base)},
IF(AND({What category survey did you work on?}=“Category 3”,{Did you also conduct Qual Coding?}=“No”), {Cat 3 Survey Rate (LC) (from Countries | 60dB base)}))))))

Justin_Barrett · Accepted Answer

@Zollie has the right idea when talking about changing from IF() to SWITCH(). Here’s your formula converted to use the latter option:

SWITCH(
    {What category survey did you work on?},
    "Category 1", SWITCH(
        {Did you also conduct Qual Coding?},
        "No", {Cat 1 Survey Rate (LC) (from Countries | 60dB base)},
        "Yes", {Cat 1 QC Survey Rate (LC) (from Countries | 60dB base)}
    ), "Category 2", SWITCH(
        {Did you also conduct Qual Coding?},
        "No", {Cat 2 Survey Rate (LC) (from Countries | 60dB base)},
        "Yes", {Cat 2 QC Survey Rate (LC) (from Countries | 60dB base)}
    ), "Category 3", SWITCH(
        {Did you also conduct Qual Coding?},
        "No", {Cat 3 Survey Rate (LC) (from Countries | 60dB base)},
        "Yes", {Cat 3 QC Survey Rate (LC) (from Countries | 60dB base)}
    )
)

While it optimized things slightly, it still feels a little cumbersome. Part of what makes this feel so cumbersome is the fact that you’re using such long field names. Something tells me this might have been done to make it easier to build a form, but remember that in forms you can override the literal field name with any other text you want. That way you can keep the field names simple, while still providing more descriptive text to those filling out the form.

If you were to simplify the field names, it could be something closer to this:

SWITCH(
    Survey,
    "Category 1", SWITCH(
        {Qual Coding?},
        "No", {Cat 1 Survey Rate},
        "Yes", {Cat 1 QC Survey Rate}
    ), "Category 2", SWITCH(
        {Qual Coding?},
        "No", {Cat 2 Survey Rate},
        "Yes", {Cat 2 QC Survey Rate}
    ), "Category 3", SWITCH(
        {Qual Coding?},
        "No", {Cat 3 Survey Rate},
        "Yes", {Cat 3 QC Survey Rate}
    )
)

Could this be simplified further? Sure. Instead of doing both field tests separately, mash their contents together inside the first SWITCH() function. If you only have three categories with a yes/no option for each, that formula (along with simpler field names) could look like this:

SWITCH(
    Survey & " | " & {Qual Coding?},
    "Category 1 | No", {Cat 1 Survey Rate},
    "Category 1 | Yes", {Cat 1 QC Survey Rate},
    "Category 2 | No", {Cat 2 Survey Rate},
    "Category 2 | Yes", {Cat 2 QC Survey Rate},
    "Category 3 | No", {Cat 3 Survey Rate},
    "Category 3 | Yes", {Cat 3 QC Survey Rate}
)

Even with your current field names, though, this format still condenses things quite a bit:

SWITCH(
    {What category survey did you work on?} & " | " & {Did you also conduct Qual Coding?},
    "Category 1 | No", {Cat 1 Survey Rate (LC) (from Countries | 60dB base)},
    "Category 1 | Yes", {Cat 1 QC Survey Rate (LC) (from Countries | 60dB base)},
    "Category 2 | No", {Cat 2 Survey Rate (LC) (from Countries | 60dB base)},
    "Category 2 | Yes", {Cat 2 QC Survey Rate (LC) (from Countries | 60dB base)},
    "Category 3 | No", {Cat 3 Survey Rate (LC) (from Countries | 60dB base)},
    "Category 3 | Yes", {Cat 3 QC Survey Rate (LC) (from Countries | 60dB base)}
)

Zollie · Answer

One thing that stands out is that all of your IF/AND blocks are nested into the first AND statement. Not sure if that was intentional. Depending on what you’re trying to do, you might benefit from using SWITCH instead of IF in some cases.

Could you expand a little on what logic you’re trying to convey? Sometimes it helps me to spell out the inputs/outputs. Looks like your inputs are some sort of questions, probably a single select, with the responses as the options. How are you hoping to tie them together for a single output? Are there examples of outputs you’d like to see?

Hi Zollie, thank you so much! Appreciate your quick response.
Yes, exactly - I am trying to get a specific output e.g. “Cat 1 Survey Rate…” based on a set of criteria (Category survey and whether the response to one of the questions is Yes/No). Essentially a conditional lookup but within one table.

Sign up

Login to the community

Scanning file for viruses.

This file cannot be downloaded