Skip to main content

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!

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!

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!!!

Reply


Terms & Conditions