Nested IF Formula Review Please

[nadodi]

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.

[nadodi]

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:

[kuovonne · Accepted solution]

AlliAlosa:

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.

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"
) ) ) )

[ScottWorld]

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.

[nadodi]

kuovonne:

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: