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
- Re: Another Nested IF Formula question

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

2
749
0

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

Aug 22, 2019 11:53 AM

Hi,

I’m trying to get one field to return a value based on the number in a different field. I’ve tried both uploading and linking to a screenshot but it’s not allowing me to do that.

This is the Nested IF formula I wrote:

IF({Number of ELLs}<21,“0-20”,IF({Number of ELLs}>20,“21-50”,IF({Number of ELLs}>50,“51-75”,IF({Number of ELLs}>75,“76-100”,IF({Number of ELLs}>100,“101-300”,IF({Number of ELLs}>300,“301-500”,IF({Number of ELLs}>500,“501-1000”,IF({Number of ELLs}>1000,“1001-1500”,IF({Number of ELLs}>1501,“1500+”)))))))))

The formula won’t return any values other than “0-20” and “21-50”. Does anyone have any insight on why this might happen?

2 Replies 2

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

Aug 22, 2019 03:02 PM

The reason your formula is never returning anything greater than the “21-50” range is because your formula will always return either the first value or the second value, due to the way it is processed. When Airtable goes to process your formula, it sees the first `IF()`

and checks the condition : `{Number of ELLs} < 21`

? : … if the condition is met, then it returns your value of `"0-20"`

. If the condition *is not* met, it moves on to the next `IF()`

statement and checks its condition : `{Number of ELLs} > 20`

? : … since that statement will **always** be true if the first one is false, Airtable never makes it past that statement – it returns “21-50” for **all** values greater than `20`

.

A series of nested `IF()`

statements is never processed beyond a matching condition - once a condition is matched, and a value is returned, the rest of the formula is ignored. For that reason, you have to write your formula in such a way that the failure of one condition leads naturally to checking the next one, and the final value is only returned if all other conditions fail – you could call this a “threshold pattern”. You are asking Airtable to evaluate, “Do we meet this top threshold? no… move on to the next… do we meet this next threshold? no… move on to the next… etc”. You have to write the formula in such a way that it’s possible to *even make it* to the final condition check, rather than have it subsumed by prior condition checks.

So let’s re-write that formula with a descending threshold pattern (because descending is the easiest way to write it):

```
IF(
{Number of ELLs} >= 1501,
"1500+",
IF(
{Number of ELLs} >= 1001,
"1001-1500",
IF(
{Number of ELLs} >= 501,
"501-1000",
IF(
{Number of ELLs} >= 301,
"301-500",
IF(
{Number of ELLs} >= 101,
"101-300",
IF(
{Number of ELLs} >= 76,
"76-100",
IF(
{Number of ELLs} >= 51,
"51-75",
IF(
{Number of ELLs} >= 21,
"21-50",
"0-20"
))))))))
```

That should do what you want.

Reply

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

Aug 23, 2019 10:49 AM

Thank you! That did the trick :slightly_smiling_face:

Reply