Nested IF AND Statement

[Scott_Brasted]

It seems the more I learn here the more ideas I come up with that I need help with.

I have a membership payment table. There are 6 levels of membership.

Individual  $25.00
Family  $45.00
Patron  $60.00
Sustaining  $100.00
Benefactor $250.00
Sponsor  $1,000.00

I would like to write a formula in the membership level field that fills in the membership level in the membership level field using the amount entered in the amount of the membership payment field. There are 6 membership levels as above. So if they pay $35, then it would put in as an Individual membership, etc. The $10 difference would but put in a field of the donations table as a donation. I will start that formula when I get this working.

This is what I came up with but it has an error. Am I missing an OR statement maybe?

Many thanks, Scott

IF(AND({Amount Paid}=25,{Amount Paid}<45),”Individual”,

IF(AND({Amount Paid}>25,{Amount Paid}<45),”Family”,

IF(AND({Amount Paid}>45,{Amount Paid}<60),”Patron”,

IF(AND({Amount Paid}>60,{Amount Paid}<100),”Sustaining”,

IF(AND({Amount Paid}>100,{Amount Paid}<250),”Benefactor”,

IF(AND({Amount Paid}>250,{Amount Paid}<1000),”Sponsor”

)

)

)

)

)

)

[ProsperSpark]

Hi Scott,

This is a tricky one! I've run across it twice this week. (Maybe it has to do with the spooky season coming up!)

The formula you provided appears to be mostly correct. However, there is an issue with the quotation marks you've used. It seems that you've used curly quotes instead of straight quotes. You should use straight double quotes for string values in Airtable formulas. Here's the corrected formula with straight quotes:

IF(AND({Amount Paid}=25, {Amount Paid}<45), "Individual",
IF(AND({Amount Paid}>25, {Amount Paid}<45), "Family",
IF(AND({Amount Paid}>45, {Amount Paid}<60), "Patron",
IF(AND({Amount Paid}>60, {Amount Paid}<100), "Sustaining",
IF(AND({Amount Paid}>100, {Amount Paid}<250), "Benefactor",
IF(AND({Amount Paid}>250, {Amount Paid}<1000), "Sponsor", "")
)
)
)
)
)

This corrected formula should work as intended to assign the membership level based on the "Amount Paid" value. Please make sure to use straight quotes when entering this formula in Airtable, and it should function properly.

If you have any specific questions about setting up formulas or need further assistance, feel free to ask, and we'll be happy to help! www.prosperspark.com

[Scott_Brasted]

Ok, So, Airtable accepted the formula, but not does not fill in any information in the field Membership Level when I enter the amounts in Amount Paid. Hmmmm. I am certainly confused. The formula is in the Membership Level field. Hmmmm again.

[Alexey_Gusev · Accepted solution]

Hi,
The formula is not quite correct. Expression AND({Amount Paid}=25, {Amount Paid}<45) is a mistake because it's equal to IF({Amount Paid}=25. For 24 or 26, same as for 35, it's false, AND means both statements must be true, and here "nothing is equal to 25, except 25".
You should start with AND({Amount Paid}>25, {Amount Paid}<45), ' "Individual",
then IF(AND({Amount Paid}>45, {Amount Paid}<60), ”Family”, and so on..
in the end,  IF(AND({Amount Paid}>250, {Amount Paid}<1000), must be 'Benefactor, and the last must be IF({Amount Paid}>1000,"Sponsor", "")
Second flaw is that you defined option for <45  and for >45 , but not for =45. So, you should write each first expression in line as "more or equal", for example  AND({Amount Paid}>=25, {Amount Paid}<45), ' "Individual"
and so on...

I would write it as:
SWITCH(
({Amount Paid}>=25) + ({Amount Paid}>=45) + ({Amount Paid}>=60) + 
({Amount Paid}>=100)+({Amount Paid}>=250)+({Amount Paid}>=1000),
0,'',
1,'Individual',
2,'Family',
3,'Patron',
4,'Sustaining',
5,'Benefactor',
6,'Sponsor')