This website uses Cookies. Click Accept to agree to our website's cookie use as described in our Privacy Policy. Click Preferences to customize your cookie settings.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Airtable Community
- Discussions
- Ask A Question
- Formulas
- Nested IF AND Statement

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Topic Labels:
Formulas

Solved

Jump to Solution

0
749
3

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Comment Post Options

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Oct 27, 2023 08:08 AM - edited Oct 27, 2023 08:33 AM

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”

)

)

)

)

)

)

Solved! Go to Solution.

1 Solution

Accepted Solutions

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Oct 28, 2023 03:12 AM

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

Reply

3 Replies 3

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Oct 27, 2023 02:09 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Oct 27, 2023 02:26 PM

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Oct 28, 2023 03:12 AM

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

Reply