Formula to calculate the difference in fields

[Scott_Brasted]

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

[Arthur_Tutt]

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. 

[Scott_Brasted]

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

[Arthur_Tutt]

Got it, thanks for the clarification @Scott_Brasted ! In that case I'd create a linked record between your two tables so you can pull the membership price data. When I did, I used your formula to calculate the Membership Level, then just copy/pasted the result (individual, family, patron, etc) into the new linked record field. Then I added a lookup, to lookup the price of the membership. Then just a simple subtraction formula to calculate the donation amount. Here's screenshots to show how it looks:

[Scott_Brasted]

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

[Scott_Brasted]

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.

https://airtable.com/invite/l?inviteId=inveqcOPTL1NM2uGc&inviteToken=b756f69ce1c77451d46528a3bd7030f...

Best, Scott

[Scott_Brasted · Accepted solution]

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