If Statement Formula focused on a Lookup Field

Question

I have the following formula in a field names "Status":

IF({Current Performance}=5,"5: Excellent",
IF({Current Performance}>=4,"4: Good",
IF({Current Performance}>=3,"3: Fair",
IF({Current Performance}>=2,"2: Poor",
IF({Current Performance}<2,"1: Very Poor")))))

The Current Performance field is a lookup field set to show the last (most recent) item from the related table.

If the Current Performance field is blank (no record in the lookup table) the formula will return "Very Poor" even though there is no number in the Current Performance field. I've tried various iterations of the formula, but can't seem to make it behave properly. Essentially, if the Current Performance lookup field is empty there should be no corresponding record in the Status formula field.

Any ideas/suggestions?

Ben_Young1 · Accepted Answer

Hey @Tom_Glatt!

Here's a version of your formula that will return blank if the Current Performance field returns null:

IF(
    {Current Performance} = 5,
    "5: Excellent",
    IF(
        {Current Performance} >= 4,
        "4: Good",
        IF(
            {Current Performance} >= 3,
            "3: Fair",
            IF(
                {Current Performance} >= 2,
                "2: Poor",
                IF(
                    AND({Current Performance}, {Current Performance} < 2),
                    "1: Very Poor"
                )
            )
        )
    )
)

Here's an walkthrough on what's happening. To start, here's a formatted version of your formula:

IF(
    {Current Performance} = 5,
    "5: Excellent",
    IF(
        {Current Performance} >= 4,
        "4: Good",
        IF(
            {Current Performance} >= 3,
            "3: Fair",
            IF(
                {Current Performance} >= 2,
                "2: Poor",
                IF(
                    {Current Performance} < 2,
                    "1: Very Poor"
                )
            )
        )
    )
)

The problem is caused by the evaluation parameter of the last nested IF function:
{Current Performance} < 2

When Airtable polls the Current Performance field for a value and comes back with a null value, the expression is translated to null < 2.
Airtable evaluates null as a falsy, which shares loose equality with a number value of 0.
Knowing that a number data type would satisfy the formula syntax, Airtable treats the null value of your Current Performance field as a 0.
With this, we now know that Airtable is evaluating the expression null < 2 as 0 < 2. Since this always resolves to true, 1: Very Poor will always be returned Current Performance is null.

We can account for this by implementing an AND function to check that the field is not empty (or zero) but less than a value of 2.

Thank you for the detailed explanation! I kind of thought it was doing something like that but had no idea how to correct for it. Nice to learn the why. Thanks again for taking the time to explain.

Tom_Glatt · Answer

Hey @Tom_Glatt!

Here's a version of your formula that will return blank if the Current Performance field returns null:

IF(
    {Current Performance} = 5,
    "5: Excellent",
    IF(
        {Current Performance} >= 4,
        "4: Good",
        IF(
            {Current Performance} >= 3,
            "3: Fair",
            IF(
                {Current Performance} >= 2,
                "2: Poor",
                IF(
                    AND({Current Performance}, {Current Performance} < 2),
                    "1: Very Poor"
                )
            )
        )
    )
)

Here's an walkthrough on what's happening. To start, here's a formatted version of your formula:

IF(
    {Current Performance} = 5,
    "5: Excellent",
    IF(
        {Current Performance} >= 4,
        "4: Good",
        IF(
            {Current Performance} >= 3,
            "3: Fair",
            IF(
                {Current Performance} >= 2,
                "2: Poor",
                IF(
                    {Current Performance} < 2,
                    "1: Very Poor"
                )
            )
        )
    )
)

The problem is caused by the evaluation parameter of the last nested IF function:
{Current Performance} < 2

When Airtable polls the Current Performance field for a value and comes back with a null value, the expression is translated to null < 2.
Airtable evaluates null as a falsy, which shares loose equality with a number value of 0.
Knowing that a number data type would satisfy the formula syntax, Airtable treats the null value of your Current Performance field as a 0.
With this, we now know that Airtable is evaluating the expression null < 2 as 0 < 2. Since this always resolves to true, 1: Very Poor will always be returned Current Performance is null.

We can account for this by implementing an AND function to check that the field is not empty (or zero) but less than a value of 2.

Thank you for the detailed explanation! I kind of thought it was doing something like that but had no idea how to correct for it. Nice to learn the why. Thanks again for taking the time to explain.

Sign up

Login to the community

Scanning file for viruses.

This file cannot be downloaded