Calculating the % of Payments completed where payments are checkboxes

[MegKrashCourse]

Hiya all!

So, I am trying to create a formula that uses each of the checkboxes in my table as a way to track payments that have come in.

I need each checkbox to equal/represent to the amount listed in the {Payment Amounts} column and for the number of payments that have come in (ie - the number of checkboxes checked) to be represented by a progress bar where the final tuition amount is listed in the table too as {Tuition}. 

I have the below pasted formula so far, but its obviously not working: 

IF( COUNTA({1st}, {2nd}, {3rd}, {4th}, {5th}, {6th}, {7th}, {8th}, {9th}, {10th})=0 , 0 , VALUE(SUBSTITUTE(SUBSTITUTE({Tuition (from Course Tracker)}, "$", ""), ",", "")) / ({Payment Amounts} * COUNTA({1st}, {2nd}, {3rd}, {4th}, {5th}, {6th}, {7th}, {8th}, {9th}, {10th})))

The issue thats coming up with this formula is that when there are no check boxes ticked, the formula seems to be working and the % paid shows 0%, but then when I tick a check box, it comes up with an error code... Any pointers on how to fix this formula?

I have pasted a screenshot of the table for reference - is there anyone that can help me figure out this formula? x

[dilipborad]

Hello @MegKrashCourse ,

NOTE:- This is not an exact answer but helps you to get it.
GPT OR Gemini can also able to give more precise answers for Airtable formula creations.

Based on your detailed explanations I've tried it and got this formula. Check it.
If it does not fit your requirements then try again with a revised query(prompt).

IF(
    SUM({1st}, {2nd}, {3rd}, {4th}, {5th}, {6th}, {7th}, {8th}, {9th}, {10th}) = 0,
    0,
    SUM({1st} * {Payment Amounts}, {2nd} * {Payment Amounts}, {3rd} * {Payment Amounts}, {4th} * {Payment Amounts},  {5th} * {Payment Amounts}, {6th} * {Payment Amounts}, {7th} * {Payment Amounts}, {8th} * {Payment Amounts}, {9th} * {Payment Amounts}, {10th} * {Payment Amounts}) / VALUE(SUBSTITUTE(SUBSTITUTE({Tuition}, "$", ""), ",", ""))
)

[pressGO_design]

Is there a reason that you can't just sum the total of the checked boxes and divide that by the number of payments? 

IF(
   {# Payments},
      ({1}+{2}+{3}+{4}+{5})/{# Payments}
)

Link to base here.

[Brian_Sweeny]

HI! It's a bit difficult to help because I don't have visibility on your base set up, but it looks like you may not have a 'Payments' table? Having a payments table would be best practice! Then probably a rollup field on this table. Let me know if you'd like to schedule a session sometime briansweeny.zone

[BillH]

I meant to get back to this yesterday, but every time I did and looked at the screenshots I just got more confused.  I think @pressGO_design manages to get past off that and has a solution for you.  The only thing would be if you have more checks than the number of payments, but you can add another if function to take care of than.  

And if this is part of a larger system @Brian_Sweeny is correct.

[pressGO_design]

Totes agree with @BillH and @Brian_Sweeny about the data structure issues here.

Part of the difficulty of Airtable relying on the Community Board for customer service is that we generally don’t have access to the bases that people are asking about, so we can’t chime in with, “sure, this will answer your question, but doing it *this way* will make your life so much easier.”

[o_elsiefy]

I'd maybe rework how you have the infrastructure setup. I'd have a table with the Student Info and their total tuition, and another table (linked to the original table) with the payment plan info. You can even use automations to auto generate this. But basically, EACH ROW would be payment of $500 all linked to the original student. When a payment is completed you update it and on the original table use roll-up to see how much is paid, the percentage etc.

If you want to then be able to view everything on one screen use a list view with levels.

[MegKrashCourse]

Thank you all! I ended using the following formula (if anyone needs to use it): 

IF(
{Tuition (from Course Tracker)} = 0,
0,
(
SUM(
IF({1st}, {Payment Amounts}, 0),
IF({2nd}, {Payment Amounts}, 0),
IF({3rd}, {Payment Amounts}, 0),
IF({4th}, {Payment Amounts}, 0),
IF({5th}, {Payment Amounts}, 0),
IF({6th}, {Payment Amounts}, 0),
IF({7th}, {Payment Amounts}, 0),
IF({8th}, {Payment Amounts}, 0),
IF({9th}, {Payment Amounts}, 0),
IF({10th}, {Payment Amounts}, 0)
) / {Tuition (from Course Tracker)}
)
)

I then formatted the column to present as a percentage bar 🙂