Skip to main content
Solved

Nested IF Formula Review Please

  • June 7, 2020
  • 13 replies
  • 82 views
N
Forum|alt.badge.img+5
  • nadodi
  • Participating Frequently

Hi, Airtable newbie here. We are trying to setup a table that calculates farm type based on size of farm. The result we are looking for is this:

Acres…Farm Type
Empty…Unknown
0.0…Landless
0.1-5.0…Small
5.1-20.0…Medium
20.1+…Large

Trying to use this formula:

IF(Acres = "", "Unknown", 
    IF(Acres >= 20.1, "Large", 
	    IF(Acres >= 5.1, "Medium", 
		    IF(Acres >= 0.1, "Small", 
			    IF(Acres >= 0.0, "Landless", 
				    "Unknown"
)))))

This is causing a conflict between Unknown and Landless. Any idea why and how to fix this? Please help. Thanks in advance.

Best answer by kuovonne

This is somewhat of a known issue. Not sure why it hasn’t been addressed, but I often advise users to avoid using BLANK() all together. By just referencing the field name, Airtable inherently evaluates whether or not there is a value in the field.

For example, writing something like…

IF({Acres}, "Acres Exist")

Is the same thing as…

IF({Acres} != BLANK(), "Acres Exist")

Additionally, to force Airtable to recognize a value of “0.0” as NOT blank, you can use ARRAYJOIN() to evaluate the 0 as a text value.

I would rewrite the formula to something like this:

IF(ARRAYJOIN({Acres}),
IF(Acres >= 20.1, "Large", 
IF(Acres >= 5.1, "Medium", 
IF(Acres >= 0.1, "Small", 
IF(Acres >= 0.0, "Landless")))),
"Unknown")

This is a really neat trick. Thank you for pointing this out.

I found this problem intriguing and my mind kept coming back to it. I realize that this point was why my previous formula failed. (Even though I totally know that numbers with 0 evaluate to false, and I still got tripped up. I should have tested the formula before posting it, but sometimes I’m posting away from a computer.)

I’m glad that the original poster got a formula that works for him.

I’m posting this alternate version here so that if I revisit this thread, I can see where my thoughts led me. This formula uses the same trick of turning the number into a string, but uses concatenation instead of ARRAYJOIN.

IF(Acres >= 20.1, "Large",
  IF(Acres >= 5.1, "Medium",
    IF(Acres >= 0.1, "Small", 
      IF({Acres} & "", "Landless", 
        "Unknown"
) ) ) )

13 replies

M
Forum|alt.badge.img+3
  • Melanie_E_Magda
  • Participating Frequently
  • June 7, 2020

Hello!

Give this a shot. I swapped the “” for BLANK() and my quick test seems to be producing your desired results!

IF(Acres = BLANK(), "Unknown", 
IF(Acres >= 20.1, "Large", 
    IF(Acres >= 5.1, "Medium", 
	    IF(Acres >= 0.1, "Small", 
		    IF(Acres >= 0.0, "Landless", 
			    "Unknown"
)))))

N
Forum|alt.badge.img+5
  • nadodi
  • Author
  • Participating Frequently
  • June 7, 2020

Hello!

Give this a shot. I swapped the “” for BLANK() and my quick test seems to be producing your desired results!

IF(Acres = BLANK(), "Unknown", 
IF(Acres >= 20.1, "Large", 
    IF(Acres >= 5.1, "Medium", 
	    IF(Acres >= 0.1, "Small", 
		    IF(Acres >= 0.0, "Landless", 
			    "Unknown"
)))))


Thank you @Melanie_E_Magdalena

Strangely, doing that is still causing problems at my end.

This Code:

    IF(Acres = "", "Unknown", 
    IF(Acres >= 20.1, "Large", 
	    IF(Acres >= 5.1, "Medium", 
		    IF(Acres >= 0.1, "Small", 
			    IF(Acres >= 0.0, "Landless", 
				    "Unknown"
)))))

Results in:

ScottWorld
Forum|alt.badge.img+35
  • ScottWorld
  • Genius
  • June 7, 2020

Thank you @Melanie_E_Magdalena

Strangely, doing that is still causing problems at my end.

This Code:

    IF(Acres = "", "Unknown", 
    IF(Acres >= 20.1, "Large", 
	    IF(Acres >= 5.1, "Medium", 
		    IF(Acres >= 0.1, "Small", 
			    IF(Acres >= 0.0, "Landless", 
				    "Unknown"
)))))

Results in:


You didn’t change the first part to BLANK(), as @Melanie_E_Magdalena suggested above.

N
Forum|alt.badge.img+5
  • nadodi
  • Author
  • Participating Frequently
  • June 7, 2020

You didn’t change the first part to BLANK(), as @Melanie_E_Magdalena suggested above.


Thanks @ScottWorld

Sorry, my bad. I pasted the wrong/old snippet earlier. I do have Blank() in the code. Please see screenshot attached. Still 0.0 is resolving to ‘Unknown’

Current Code:

ScottWorld
Forum|alt.badge.img+35
  • ScottWorld
  • Genius
  • June 7, 2020

Thanks @ScottWorld

Sorry, my bad. I pasted the wrong/old snippet earlier. I do have Blank() in the code. Please see screenshot attached. Still 0.0 is resolving to ‘Unknown’

Current Code:


Wow, I think that you might have stumbled upon a major & significant bug in Airtable.

BLANK() should never be evaluated the same as zero, because they are completely different values altogether.

No programming language should ever evaluate BLANK and ZERO as the same value, but that looks like exactly what Airtable is doing here. This is totally incorrect.

I tested out your formula in Airtable, and your formula resulted in the exact same problem for me.

Then, I typed up this test formula in Airtable:

IF(BLANK()=0,"Same","Different")

And that formula results in “Same”.

However, it should result in “Different”, because a blank value is NOT the same as a ZERO value.

On the other hand, THIS formula:

IF(BLANK()="","Same","Different")

is the ONLY formula which should result in “Same”. And it actually DOES result in “Same”.

(And if you replace my 0 and “” values with number fields, it’s still the exact same problem.)

So Airtable can’t tell the difference between BLANK and ZERO. This is a huge problem.

I think that you should report this bug ASAP to support@airtable.com. You can also tell them to check out this thread here, where you have uncovered this bug.

A
N
A
Forum|alt.badge.img+19
  • AlliAlosa
  • Inspiring
  • June 7, 2020

Wow, I think that you might have stumbled upon a major & significant bug in Airtable.

BLANK() should never be evaluated the same as zero, because they are completely different values altogether.

No programming language should ever evaluate BLANK and ZERO as the same value, but that looks like exactly what Airtable is doing here. This is totally incorrect.

I tested out your formula in Airtable, and your formula resulted in the exact same problem for me.

Then, I typed up this test formula in Airtable:

IF(BLANK()=0,"Same","Different")

And that formula results in “Same”.

However, it should result in “Different”, because a blank value is NOT the same as a ZERO value.

On the other hand, THIS formula:

IF(BLANK()="","Same","Different")

is the ONLY formula which should result in “Same”. And it actually DOES result in “Same”.

(And if you replace my 0 and “” values with number fields, it’s still the exact same problem.)

So Airtable can’t tell the difference between BLANK and ZERO. This is a huge problem.

I think that you should report this bug ASAP to support@airtable.com. You can also tell them to check out this thread here, where you have uncovered this bug.


This is somewhat of a known issue. Not sure why it hasn’t been addressed, but I often advise users to avoid using BLANK() all together. By just referencing the field name, Airtable inherently evaluates whether or not there is a value in the field.

For example, writing something like…

IF({Acres}, "Acres Exist")

Is the same thing as…

IF({Acres} != BLANK(), "Acres Exist")

Additionally, to force Airtable to recognize a value of “0.0” as NOT blank, you can use ARRAYJOIN() to evaluate the 0 as a text value.

I would rewrite the formula to something like this:

IF(ARRAYJOIN({Acres}),
IF(Acres >= 20.1, "Large", 
IF(Acres >= 5.1, "Medium", 
IF(Acres >= 0.1, "Small", 
IF(Acres >= 0.0, "Landless")))),
"Unknown")
ScottWorld
N
kuovonne
kuovonne
Forum|alt.badge.img+29
  • kuovonne
  • Brainy
  • June 8, 2020

I’m not seeing where BLANK() is being evaluated as zero. I believe that you are seeing “Unknown” for the blank record because your first IF statement says that the value should be “Unknown” when {Acres} is blank.

You might also be running into a general computing floating point issue. Decimal numbers are not stored as exact numbers, and you you might be running into problems because for a computer the number 0.0 is not quite the same as 0.

If you don’t have to worry about negative numbers, try this formula.

IF(Acres = BLANK(), "Unknown",
  IF(Acres >= 20.1, "Large",
    IF(Acres >= 5.1, "Medium",
      IF(Acres >= 0.1, "Small", "Landless"
))))

@nadodi If one of the formulas in this discussion solves your problem, could you please mark it as the solution.

kuovonne
Forum|alt.badge.img+29
  • kuovonne
  • Brainy
  • June 8, 2020

Wow, I think that you might have stumbled upon a major & significant bug in Airtable.

BLANK() should never be evaluated the same as zero, because they are completely different values altogether.

No programming language should ever evaluate BLANK and ZERO as the same value, but that looks like exactly what Airtable is doing here. This is totally incorrect.

I tested out your formula in Airtable, and your formula resulted in the exact same problem for me.

Then, I typed up this test formula in Airtable:

IF(BLANK()=0,"Same","Different")

And that formula results in “Same”.

However, it should result in “Different”, because a blank value is NOT the same as a ZERO value.

On the other hand, THIS formula:

IF(BLANK()="","Same","Different")

is the ONLY formula which should result in “Same”. And it actually DOES result in “Same”.

(And if you replace my 0 and “” values with number fields, it’s still the exact same problem.)

So Airtable can’t tell the difference between BLANK and ZERO. This is a huge problem.

I think that you should report this bug ASAP to support@airtable.com. You can also tell them to check out this thread here, where you have uncovered this bug.


Airtable formulas sometimes do type conversion behind the scenes. Type conversion is why you are able to do things like concatenate a number with a string. When you concatenate a number with a string, the number is automatically converted to a string.

In @ScottWorld’s example, he is comparing different types of data, and Airtable is doing type conversion. BLANK(), 0, and "" are all different data types: null, number, and string. Trying to compare them is like comparing apples and oranges–it doesn’t work. You have to convert the apples and oranges to the data type “fruit” before you can make a comparison. Similarly, Airtable converts BLANK(), 0, and "" to the same data type to compare them. When they are all converted to boolean data types, they all evaluate to false so they are the same in that they are all false.

In JavaScript (and other languages), you can compare variable by value only (==) or by value and data type (===). In JavaScript (null == 0) is true, but (null === 0) is false. However, in the interest of simplicity, Airtable only compares by value, not by value and type.

ScottWorld
N
Forum|alt.badge.img+5
  • nadodi
  • Author
  • Participating Frequently
  • June 8, 2020

I’m not seeing where BLANK() is being evaluated as zero. I believe that you are seeing “Unknown” for the blank record because your first IF statement says that the value should be “Unknown” when {Acres} is blank.

You might also be running into a general computing floating point issue. Decimal numbers are not stored as exact numbers, and you you might be running into problems because for a computer the number 0.0 is not quite the same as 0.

If you don’t have to worry about negative numbers, try this formula.

IF(Acres = BLANK(), "Unknown",
  IF(Acres >= 20.1, "Large",
    IF(Acres >= 5.1, "Medium",
      IF(Acres >= 0.1, "Small", "Landless"
))))

@nadodi If one of the formulas in this discussion solves your problem, could you please mark it as the solution.


This one still has problems between landless and unknown. Both those are resolving to unknown.

kuovonne
N
Forum|alt.badge.img+5
  • nadodi
  • Author
  • Participating Frequently
  • June 8, 2020

This is somewhat of a known issue. Not sure why it hasn’t been addressed, but I often advise users to avoid using BLANK() all together. By just referencing the field name, Airtable inherently evaluates whether or not there is a value in the field.

For example, writing something like…

IF({Acres}, "Acres Exist")

Is the same thing as…

IF({Acres} != BLANK(), "Acres Exist")

Additionally, to force Airtable to recognize a value of “0.0” as NOT blank, you can use ARRAYJOIN() to evaluate the 0 as a text value.

I would rewrite the formula to something like this:

IF(ARRAYJOIN({Acres}),
IF(Acres >= 20.1, "Large", 
IF(Acres >= 5.1, "Medium", 
IF(Acres >= 0.1, "Small", 
IF(Acres >= 0.0, "Landless")))),
"Unknown")

Thanks @AlliAlosa.

IF(ARRAYJOIN({Acres}),
    IF(Acres >= 20.1, "Large", 
        IF(Acres >= 5.1, "Medium", 
            IF(Acres >= 0.1, "Small", 
                IF(Acres >= 0.0, "Landless")))),
"Unknown")

This formula works for all values. :grinning_face_with_big_eyes: :pray:

A
kuovonne
kuovonne
Forum|alt.badge.img+29
  • kuovonne
  • Brainy
  • Answer
  • June 8, 2020

This is somewhat of a known issue. Not sure why it hasn’t been addressed, but I often advise users to avoid using BLANK() all together. By just referencing the field name, Airtable inherently evaluates whether or not there is a value in the field.

For example, writing something like…

IF({Acres}, "Acres Exist")

Is the same thing as…

IF({Acres} != BLANK(), "Acres Exist")

Additionally, to force Airtable to recognize a value of “0.0” as NOT blank, you can use ARRAYJOIN() to evaluate the 0 as a text value.

I would rewrite the formula to something like this:

IF(ARRAYJOIN({Acres}),
IF(Acres >= 20.1, "Large", 
IF(Acres >= 5.1, "Medium", 
IF(Acres >= 0.1, "Small", 
IF(Acres >= 0.0, "Landless")))),
"Unknown")

This is a really neat trick. Thank you for pointing this out.

I found this problem intriguing and my mind kept coming back to it. I realize that this point was why my previous formula failed. (Even though I totally know that numbers with 0 evaluate to false, and I still got tripped up. I should have tested the formula before posting it, but sometimes I’m posting away from a computer.)

I’m glad that the original poster got a formula that works for him.

I’m posting this alternate version here so that if I revisit this thread, I can see where my thoughts led me. This formula uses the same trick of turning the number into a string, but uses concatenation instead of ARRAYJOIN.

IF(Acres >= 20.1, "Large",
  IF(Acres >= 5.1, "Medium",
    IF(Acres >= 0.1, "Small", 
      IF({Acres} & "", "Landless", 
        "Unknown"
) ) ) )
A
ScottWorld
Forum|alt.badge.img+35
  • ScottWorld
  • Genius
  • June 9, 2020

Thanks, @kuovonne and @AlliAlosa, for all the great tips & tricks above! :slightly_smiling_face: I will be referring to this thread forever in the future!! This is a very valuable thread.

A
N
Forum|alt.badge.img+5
  • nadodi
  • Author
  • Participating Frequently
  • June 9, 2020

This is a really neat trick. Thank you for pointing this out.

I found this problem intriguing and my mind kept coming back to it. I realize that this point was why my previous formula failed. (Even though I totally know that numbers with 0 evaluate to false, and I still got tripped up. I should have tested the formula before posting it, but sometimes I’m posting away from a computer.)

I’m glad that the original poster got a formula that works for him.

I’m posting this alternate version here so that if I revisit this thread, I can see where my thoughts led me. This formula uses the same trick of turning the number into a string, but uses concatenation instead of ARRAYJOIN.

IF(Acres >= 20.1, "Large",
  IF(Acres >= 5.1, "Medium",
    IF(Acres >= 0.1, "Small", 
      IF({Acres} & "", "Landless", 
        "Unknown"
) ) ) )

Thank you. This solution also works. :grinning_face_with_big_eyes: :pray:

Terms & ConditionsAccessibility statement