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
- Formula to calculate the difference in fields

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
1142
6

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 28, 2023 07:40 PM

Hello, I have posted about 15 questions this board and I have always received a solution my problem. So thank you all very much.

I think I have a really tough one this time. I have a base to manage memberships and donations and legacy gifts for a not for profit. There are 6 membership levels with specific dollar amounts attached to them. So all is well as long as a person pays an amount that corresponds to an existing membership level. But when they pay an amount that is not a preexisting level, I have to assign the difference to a donation.

I have a Currency field that records the actual amount paid. I have a formula field (thank you Alexey_Gusev) that shows the membership level closest to the payment amount. For instance, $35 would show as an Individual membershi which is set at $25. I need to then add the remaining $10 the donation field. The next level is $45 for a Family level.

To be completely honest, I have no idea where to even begin with this. Due mostly to the help I have received here, most of the time I at least have a an inkling of where to start.

Does anyone have an idea of how to accomplish this? Thanks, Scott

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 30, 2023 03:30 AM

I finally figured it out. Took a little playing around, but it works fine now. Thanks,, Scott

Reply

6 Replies 6

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

Oct 28, 2023 08:19 PM

Hey @Scott_Brasted ! Try this formula (adjust to your actual field names)

`{Paid} - {Closest Membership ($)}`

If I'm misunderstanding your question, post up screenshots of your base and fields so I better understand the data you're working with.

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

Oct 28, 2023 08:47 PM

Hi, thanks for the reply. I guess I was not clear enough. Above are two screen shots. The first is the LU Table for the membership levels and the seconds the membership table with the payment amount, the result of the formula that's the membership-level and the difference which is manual right now. I want to make it automatic with a formula. I am putting the formula for the membership level field below to help.

SWITCH(

({Amount Paid}>=1) + ({Amount Paid}>=25) + ({Amount Paid}>=45) + ({Amount Paid}>=60) +

({Amount Paid}>=100)+({Amount Paid}>=250)+({Amount Paid}>=1000),

1,'Donation Only',

2,'Individual',

3,'Family',

4,'Patron',

5,'Sustaining',

6,'Benefactor',

7,'Sponsor')

I Hope this helps.

Best, Scott

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

Oct 28, 2023 09:14 PM

Reply

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

Oct 29, 2023 05:33 AM - edited Oct 29, 2023 06:22 AM

Ok, so that got me a long way to there. I created the two new fields and I now have a field I can use to calculate Donation Amount.

IF({Amount Paid}-{Level Amount} = 0, BLANK(),{Amount Paid}-{Level Amount})

It works perfectly. The only thing that I would like to solve is how to not have to manually choose the Membership Level in the linked field. I enter the payment amount which automatically enters the Membership Level with the formula I shared in the previous post.

With this system I enter the payment amount then have to enter the Membership Level manually that is already in the Membership Level formula field.

Do you have ideas on how to avoid the manual choice?

Thanks, Scott

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

Oct 29, 2023 03:39 PM - edited Oct 29, 2023 04:01 PM

I'm confused. The lookup field that I created worked when I first created it, but when I added a record it did not fill in the field for the level amount (which is a lookup field). The system so far would work if that field self filled as it provides the second part of the formula for donations.

And as I said in my last post, I am still puzzling how to not manually enter an already entered pierce of data in the Membership Level field.

I hope I am being clear. It is very possible I am not. If it would make it easier, here is a link to the base.

Best, Scott

Solved
See Solution in Thread

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

Oct 30, 2023 03:30 AM

I finally figured it out. Took a little playing around, but it works fine now. Thanks,, Scott

Reply