Calculations based on checkboxes (monthly payment tracker)

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.

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.

@Kamille_Parks @kuovonne thoughts?

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.

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

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

It is a layaway payment tracker. If the box is checked, that means a payment has been made and the monthly amount is deducted from the remaining total. Layaway lengths are 6,9,12, and 24 months.

The formula works fine, just the 24-month layaway will be a bit fiddly. Maybe I will stare at it later and see if I can come up with a new design. I also had a note on the google sheet with the transaction number for that payment (the little black triangles).

i mean you can definitely set up 24 check boxes and double the formula

Since you’re already trying to store information about each individual payment, you should follow the base structure recommended by Kuovonne.