This website uses Cookies. Click Accept to agree to our website's cookie use as described in our Privacy Policy. Click Preferences to customize your cookie settings.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Airtable Community
- Discussions
- Ask A Question
- Formulas
- Nested IF Formula Review Please

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Topic Labels:
Formulas

Solved

Jump to Solution

0
1178
13

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Comment Post Options

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jun 06, 2020 11:29 PM

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.

Solved! Go to Solution.

1 Solution

Accepted Solutions

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jun 08, 2020 02:50 PM

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

Reply

13 Replies 13

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jun 07, 2020 12:31 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jun 07, 2020 01:54 AM

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:**

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jun 07, 2020 07:15 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jun 07, 2020 08:03 AM

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:**

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jun 07, 2020 08:19 AM

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.

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jun 07, 2020 10:44 AM

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

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jun 07, 2020 05:13 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jun 07, 2020 05:37 PM

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.

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jun 07, 2020 06:44 PM

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

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jun 07, 2020 06:57 PM

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:

Reply

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jun 08, 2020 02:50 PM

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

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jun 08, 2020 05:27 PM

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jun 09, 2020 06:49 AM

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