Struggling with nested if statements to create a single output from multiple options

1

Searched high and low for this, and it seems like it should be simple, but I can’t quite get it.

I need to output a number based on a value from a single number field within the same table - nothing fancy. The field with the reference number is named “Nights”

I can’t even get it to work with one boolean:
if(field(‘nights’) < 5, 0, 1)

If I’m understanding the above, anything less than 5 would be 0, anything 5 or above, would be 1.
What I ultimately need, simply is:
if Nights < 5, then 0
if Nights < 9, then 1
if Nights < 15, then 2
if Nights < 25, then 3
if Nights < 50, then 4
if Nights > 49, then 5

I’m coming from Airtable (hoping to completely make the jump). This is how the formula looks over there:

IF(Nights<5, 0,
IF(Nights<9, 1,
IF(Nights<15, 2,
IF(Nights<25, 3,
IF(Nights<50, 4,
IF(Nights>49, 5
)
)
)
)
)
)

2

Hi,
Welcome to the community.

What is the error message you receive after entering the formula if(field(‘nights’) < 5, 0, 1) ?

3

Ahhh - I see what my initial problem was. It was an issue of case (my field has an upper can “N” for “Nights”) - but the error message was:

Error with formula: references the deleted or unknown field nights, argument number 2 given to operator < was of type number but there are no possible types usable here.

The latter half of that message makes no sense to me in context of a field not being found.

Now I just need to figure out how to setup the “if” statement to get it to give me different outputs based on the number of Nights.

4

I figured it out - and am posting here in case anyone else is struggling with this concept:

I had to think about it this way:
What’s the criteria the IF statement needs to match? What should happen when it does? What should happen if it doesn’t? The last piece, what should happen when it doesn’t, becomes the next IF statement.

if(field(‘Nights’)<5, 0,
if(field(‘Nights’)<9, 1,
if(field(‘Nights’)<15, 2,
if(field(‘Nights’)<25, 3,
if(field(‘Nights’)<50, 4,
if(field(‘Nights’)<49, 5, 5
))))))

5

The message argument number 2 given to operator < was of type number but there are no possible types usable here. is a consequence of the field not being found. But I agree with you that it could be clearer.

You can nest your formula by replacing the last part (number 1 in this case) with a new if statement.

if(field(‘nights’) < 5, 0, if(field(‘nights’) < 9, 1, if(field(‘nights’) < 15, 2, if(field(‘nights’) < 25, 3, if(field(‘nights’) < 50, 4, if(field(‘nights’) > 49, 5, 0))))))