Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

IF, AND, checkboxes, and ranges

Solved
Jump to Solution
1339 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Greg_Davies
4 - Data Explorer
4 - Data Explorer

I’m trying to write formula for a field that would check the {CHECKBOX} field for a check (=1), and also check another {FINAL AMOUNT} field for a range, to then return a corresponding value. If the {CHECKBOX} is empty (=0), I’d like the field to just return a “0”.

I’ve tried tons of different ways to nest IF and AND statements, and the only formula I tried that will not trigger a syntax error will not display the values if both the checkbox and range criteria are met:

IF(AND({CHECKBOX}=1,{FINAL AMOUNT}<20000,500,IF(AND({CHECKBOX}=1,{FINAL AMOUNT}>=20000,{FINAL AMOUNT}<40000,750,IF(AND({CHECKBOX}=1,{FINAL AMOUNT}>=40000,1000))))))

If I try to add one more IF statement (either at the beginning or the end) to yield the “0” if {CHECKBOX}=0, Airtable tells me it’s invalid:

IF({CHECKBOX}=0,0,IF(AND({CHECKBOX}=1,{FINAL AMOUNT}<20000,500,IF(AND({CHECKBOX}=1,{FINAL AMOUNT}>=20000,{FINAL AMOUNT}<40000,750,IF(AND({CHECKBOX}=1,{FINAL AMOUNT}>=40000,1000)))))))

I’ve read tons of different IF, AND support threads and none of them (that I’ve found) deal with ranges, so I would LOVE some help. Thanks!

1 Solution

Accepted Solutions
augmented
10 - Mercury
10 - Mercury

Hi Greg. I think the formula below will work for you.

IF({CHECKBOX}, IF({FINAL AMOUNT}<20000,500, IF({FINAL AMOUNT}<40000,750, 1000)),0)

Good luck!

See Solution in Thread

2 Replies 2
augmented
10 - Mercury
10 - Mercury

Hi Greg. I think the formula below will work for you.

IF({CHECKBOX}, IF({FINAL AMOUNT}<20000,500, IF({FINAL AMOUNT}<40000,750, 1000)),0)

Good luck!

Wow. I was clearly working overtime to get this to work. I was building off a previous formula that WAS working with the IF, AND combo, but when I tried to get the other field involved, it wouldn’t work. Clearly even though my first formula did work, I was working WAY too hard to get it to do its thing. THANK YOU!!!