Skip to main content

Calculating the percentage of tuition covered by the payments made using check boxes

  • April 9, 2024
  • 2 replies
  • 28 views
M
Forum|alt.badge.img+5
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: 
VALUE(SUBSTITUTE(SUBSTITUTE({Tuition (from KCIT Tracker)}, "$", ""), ",", "")) / ({Payment Amounts} * COUNTA({1st}, {2nd}, {3rd}, {4th}, {5th}, {6th}, {7th}, {8th}, {9th}, {10th}))
 
I have pasted a screenshot of the table for reference - is there anyone that can help me figure out this formula? x
 
1 Attachments

2 replies

BillH
Forum|alt.badge.img+24
  • BillH
  • Brainy
  • April 9, 2024

It looks like a divide by zero situation.  If so, and assuming the rest of the formula is ok, try something like

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

M
Forum|alt.badge.img+5
  • MegKrashCourse
  • Author
  • Participating Frequently
  • April 9, 2024

It looks like a divide by zero situation.  If so, and assuming the rest of the formula is ok, try something like

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


So it looks like its working when nothing is checked, (none of the checkboxes) but then when one of the checkboxes is checked, it gives another error code 😞

2 Attachments
Terms & ConditionsAccessibility statement