Skip to main content
Solved

Nested IF AND Statement

  • October 27, 2023
  • 3 replies
  • 75 views
S
Forum|alt.badge.img+16

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”
)
)
)
)
)
)

Best answer by Alexey_Gusev

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.


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')

 



3 replies

ProsperSpark
Forum|alt.badge.img+5
  • ProsperSpark
  • Participating Frequently
  • 16 replies
  • October 27, 2023

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

 

S
Forum|alt.badge.img+16
  • Scott_Brasted
  • Author
  • Inspiring
  • 47 replies
  • October 27, 2023

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
Forum|alt.badge.img+25
  • Alexey_Gusev
  • Brainy
  • 1260 replies
  • Answer
  • October 28, 2023

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.


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')

 




Terms & ConditionsAccessibility statement