Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Calculations based on checkboxes (monthly payment tracker)

2848 12
cancel
Showing results for 
Search instead for 
Did you mean: 
Anxious
7 - App Architect
7 - App Architect

So I have a payment tracker in google sheets and am trying to figure out how I can get it to work similarly in Airtable. Below is an image of my google sheet. Everything in red has a formula.

The “Monthly” is calculated by dividing the total by the length.
The “Remaining” is calculated by multiplying the amount of the “Monthly” by the number of checkboxes, then subtracting that from the total.

image

Google “Remaining” formula:

=C3-(COUNTIF(E3:P3,TRUE)*D3)
Airtable Account: Free Plan
Records: 1,200/1,200 1000/1000 (I might have to find a different solution soon)
Automation Runs: ??/100
Extensions: 1/1 (TinyPNG Compression Script, looking for free alternatives)
12 Replies 12

Im pretty sure theres ways you could restructure to do this more efficiently but if you’re dead set on using this workflow here’s how you do it.

Formula in ‘remaining’
TOTAL-SUM(IF(JAN,MONTHLY)+ IF(FEB,MONTHLY), IF(MAR,MONTHLY), IF(APR,MONTHLY), IF(MAY,MONTHLY), IF(JUN,MONTHLY), IF(JUL,MONTHLY), IF(AUG,MONTHLY), IF(SEP,MONTHLY), IF(OCT,MONTHLY), IF(NOV,MONTHLY), IF(DEC,MONTHLY))

Formula in ‘monthly’
TOTAL / LENGTH

I am all game for a more efficient way to do this, so long as it is easily readable at a glance. Especially when using a 24 month payment cycle.

Airtable Account: Free Plan
Records: 1,200/1,200 1000/1000 (I might have to find a different solution soon)
Automation Runs: ??/100
Extensions: 1/1 (TinyPNG Compression Script, looking for free alternatives)

lemme look at some of my other bases and play with that base I just linked for a few days and get back to you. in the meantime it might be helpful to do some digging on the difference in a normal spreadsheet and airtable. there’s a few good youtube videos on it. you have some features available to you in airtable that you cant get in a spreadsheet but sometimes depending on your usecase and datasets a spreadsheet is more applicable and airtable just isn’t gunna do what you need, even if that’s the case you can use something like zapier to still be able to use airtable even with your google sheets. also take a look at the formula field reference on the airtable support site. it can help you to see all of airtables possible formula operators and functions to see how the excel/google sheets formula language translates into airtables abriged javascript formula language.

It looks like you have a total amount to pay, which is split into several monthly payments. Each checkbox represents a payment that needs to be paid. When the total is paid off, all the check boxes would be cleared.

There are many ways to represent this in Airtable. One way is to have two tables: one for [Accounts] and another for linked [Payments], with 1-24 linked payments, depending on the situation. On the other hand, this method might quickly run up the number of records.

Assuming you recreate this exact table structure in Airtable and use Cehckbox-type fields for all the months, the formula for the Remaining field would be:

Total - (SUM(JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, DEC) * Monthly)

I suspect, however, you may benefit from an alternate base structure since as far as I can tell you can only track one year (2021) at a time like this. If some of the lengths are 24, how would you even mark that they paid both March’s statement? Without more information on your use case I would imagine the preferred database-oriented approach would be to have a setup like the one advised by Kuovonne above.

I think this is why there are two rows of checkboxes for the row with 24 as the length. This couldn’t really be done horizontally in Airtable.

Ah, I thought that was its own row.

in theory you could take the super long way and have all 24 check boxes horizontally and have a formula that formats rich text and pastes it into a rich text field to make it more easily readable but you would burn an automation for every little update. that’s also a bit more advanced lol I would try to write it just for the fun of it if interested lol