How can I calculate the last payment of the year to be the sum of all previous payments for that year and subtracting it from Research Costs value?

JavaScript
1

How can I calculate the last payment of the year to be the sum of all previous payments(adding all data-amount) for that year and then subtracting the result from Research Costs value from the first row for that year and then assign the value to the last payment’s text and data-amount?

e.g if I pass 26/27 value to the function then it should get all the payments where data-payquarters contains 27/28 and then it should do sum of first 11 payments(in this case, adding all data-amount) and then subtract it from 250,254.21 And assign 20854.49 to the last payment’s text and data-amount (in this case it is the 12th payment.

e.g if I pass 27/28 value to the function then it should get all the payments where data-payquarters contains 27/28 and then it should do sum of first 5 payments(in this case) and then subtract it from 205,121.01 And assign 34,186.81 to the last payment’s text and data-amount (in this case it is the 6th payment.

table class="table table-sm table-bordered">
                    <thead id="thPayments">
                        <tr>
                            <th style="width:50%" class="headercell"></th>
                            <th style="width:10%" class="headercell text-right">Research Costs</th>
                            <th style="width:10%" class="headercell text-center">Payment Schedule</th>
                            <th style="width:10%" class="headercell text-center">Status</th>
                            <th style="width:10%" class="headercell text-right">Amount</th>
                            <th style="width:10%" class="headercell text-right">Reprofiled Amount</th>
                        </tr>
                    </thead>
                    <tbody id="tbPayments">
   </tbody>
   <tbody id="tbPayments">      
      <tr id="trPayment_23322651" data-year="4" data-type="payment" data-eventid="23322651">
         <th>1 Apr 2026 - 31 Mar 2027</th>
         <td class="text-right">250,254.21</td>
         <td class="text-center" data-date="2026-04-01">01-04-2026</td>
         <td class="text-center">Requested</td>
         <td class="text-right " id="tdPayment_23322651" data-type="payment" data-amount="20,854.52">20,854.52</td>
         <td class="text-right " id="tdNewPayment_23322651" data-type="newpayment" data-month="3" data-payquarters="Q1-26/27" data-paymonth="4" data-amount="20854.52">20,854.52</td>
      </tr>
      <tr id="trPayment_23322652" data-year="4" data-type="payment" data-eventid="23322652">
         <th></th>
         <td></td>
         <td class="text-center" data-date="2026-05-01">01-05-2026</td>
         <td class="text-center">Requested</td>
         <td class="text-right " id="tdPayment_23322652" data-type="payment" data-amount="20,854.52">20,854.52</td>
         <td class="text-right " id="tdNewPayment_23322652" data-type="newpayment" data-month="3" data-payquarters="Q1-26/27" data-paymonth="5" data-amount="20854.52">20,854.52</td>
      </tr>
      <tr id="trPayment_23322653" data-year="4" data-type="payment" data-eventid="23322653">
         <th></th>
         <td></td>
         <td class="text-center" data-date="2026-06-01">01-06-2026</td>
         <td class="text-center">Requested</td>
         <td class="text-right " id="tdPayment_23322653" data-type="payment" data-amount="20,854.52">20,854.52</td>
         <td class="text-right " id="tdNewPayment_23322653" data-type="newpayment" data-month="3" data-payquarters="Q1-26/27" data-paymonth="6" data-amount="20854.52">20,854.52</td>
      </tr>
      <tr id="trPayment_23322654" data-year="4" data-type="payment" data-eventid="23322654">
         <th></th>
         <td></td>
         <td class="text-center" data-date="2026-07-01">01-07-2026</td>
         <td class="text-center">Requested</td>
         <td class="text-right " id="tdPayment_23322654" data-type="payment" data-amount="20,854.52">20,854.52</td>
         <td class="text-right " id="tdNewPayment_23322654" data-type="newpayment" data-month="3" data-payquarters="Q2-26/27" data-paymonth="7" data-amount="20854.52">20,854.52</td>
      </tr>
      <tr id="trPayment_23322655" data-year="4" data-type="payment" data-eventid="23322655">
         <th></th>
         <td></td>
         <td class="text-center" data-date="2026-08-01">01-08-2026</td>
         <td class="text-center">Requested</td>
         <td class="text-right " id="tdPayment_23322655" data-type="payment" data-amount="20,854.52">20,854.52</td>
         <td class="text-right " id="tdNewPayment_23322655" data-type="newpayment" data-month="3" data-payquarters="Q2-26/27" data-paymonth="8" data-amount="20854.52">20,854.52</td>
      </tr>
      <tr id="trPayment_23322656" data-year="4" data-type="payment" data-eventid="23322656">
         <th></th>
         <td></td>
         <td class="text-center" data-date="2026-09-01">01-09-2026</td>
         <td class="text-center">Requested</td>
         <td class="text-right " id="tdPayment_23322656" data-type="payment" data-amount="20,854.52">20,854.52</td>
         <td class="text-right " id="tdNewPayment_23322656" data-type="newpayment" data-month="3" data-payquarters="Q2-26/27" data-paymonth="9" data-amount="20854.52">20,854.52</td>
      </tr>
      <tr id="trPayment_23322657" data-year="5" data-type="payment" data-eventid="23322657">
         <th></th>
         <td></td>
         <td class="text-center" data-date="2026-10-01">01-10-2026</td>
         <td class="text-center">Requested</td>
         <td class="text-right " id="tdPayment_23322657" data-type="payment" data-amount="20,854.52">20,854.52</td>
         <td class="text-right " id="tdNewPayment_23322657" data-type="newpayment" data-month="3" data-payquarters="Q3-26/27" data-paymonth="10" data-amount="20854.52">20,854.52</td>
      </tr>
      <tr id="trPayment_23322658" data-year="5" data-type="payment" data-eventid="23322658">
         <th></th>
         <td></td>
         <td class="text-center" data-date="2026-11-01">01-11-2026</td>
         <td class="text-center">Requested</td>
         <td class="text-right " id="tdPayment_23322658" data-type="payment" data-amount="20,854.52">20,854.52</td>
         <td class="text-right " id="tdNewPayment_23322658" data-type="newpayment" data-month="3" data-payquarters="Q3-26/27" data-paymonth="11" data-amount="20854.52">20,854.52</td>
      </tr>
      <tr id="trPayment_23322659" data-year="5" data-type="payment" data-eventid="23322659">
         <th></th>
         <td></td>
         <td class="text-center" data-date="2026-12-01">01-12-2026</td>
         <td class="text-center">Requested</td>
         <td class="text-right " id="tdPayment_23322659" data-type="payment" data-amount="20,854.52">20,854.52</td>
         <td class="text-right " id="tdNewPayment_23322659" data-type="newpayment" data-month="3" data-payquarters="Q3-26/27" data-paymonth="12" data-amount="20854.52">20,854.52</td>
      </tr>
      <tr id="trPayment_23322660" data-year="5" data-type="payment" data-eventid="23322660">
         <th></th>
         <td></td>
         <td class="text-center" data-date="2027-01-01">01-01-2027</td>
         <td class="text-center">Requested</td>
         <td class="text-right " id="tdPayment_23322660" data-type="payment" data-amount="20,854.52">20,854.52</td>
         <td class="text-right " id="tdNewPayment_23322660" data-type="newpayment" data-month="3" data-payquarters="Q4-26/27" data-paymonth="1" data-amount="20854.52">20,854.52</td>
      </tr>
      <tr id="trPayment_23322661" data-year="5" data-type="payment" data-eventid="23322661">
         <th></th>
         <td></td>
         <td class="text-center" data-date="2027-02-01">01-02-2027</td>
         <td class="text-center">Requested</td>
         <td class="text-right " id="tdPayment_23322661" data-type="payment" data-amount="20,854.52">20,854.52</td>
         <td class="text-right " id="tdNewPayment_23322661" data-type="newpayment" data-month="3" data-payquarters="Q4-26/27" data-paymonth="2" data-amount="20854.52">20,854.52</td>
      </tr>
      <tr id="trPayment_23322662" data-year="5" data-type="payment" data-eventid="23322662">
         <th></th>
         <td></td>
         <td class="text-center" data-date="2027-03-01">01-03-2027</td>
         <td class="text-center">Requested</td>
         <td class="text-right " id="tdPayment_23322662" data-type="payment" data-amount="20,854.52">20,854.52</td>
         <td class="text-right " id="tdNewPayment_23322662" data-type="newpayment" data-month="3" data-payquarters="Q4-26/27" data-paymonth="3" data-amount="20854.52">20,854.52</td>
      </tr>
      
      
      
      
      <tr id="trPayment_23322663" data-year="5" data-type="payment" data-eventid="23322663">
         <th>1 Apr 2027 - 31 Mar 2028</th>
         <td class="text-right">205,121.01</td>
         <td class="text-center" data-date="2027-04-01">01-04-2027</td>
         <td class="text-center">Paid</td>
         <td class="text-right nochange paid" id="tdPayment_23322663" data-type="payment" data-amount="34,186.84">34,186.84</td>
         <td class="text-right nochange paid" id="tdNewPayment_23322663" data-type="newpayment" data-month="3" data-payquarters="Q1-27/28" data-paymonth="4" data-amount="34,186.84">34,186.84</td>
      </tr>
      <tr id="trPayment_23322664" data-year="5" data-type="payment" data-eventid="23322664">
         <th></th>
         <td></td>
         <td class="text-center" data-date="2027-05-01">01-05-2027</td>
         <td class="text-center">Paid</td>
         <td class="text-right nochange paid" id="tdPayment_23322664" data-type="payment" data-amount="34,186.84">34,186.84</td>
         <td class="text-right nochange paid" id="tdNewPayment_23322664" data-type="newpayment" data-month="3" data-payquarters="Q1-27/28" data-paymonth="5" data-amount="34,186.84">34,186.84</td>
      </tr>
      <tr id="trPayment_23322665" data-year="5" data-type="payment" data-eventid="23322665">
         <th></th>
         <td></td>
         <td class="text-center" data-date="2027-06-01">01-06-2027</td>
         <td class="text-center">Paid</td>
         <td class="text-right nochange paid" id="tdPayment_23322665" data-type="payment" data-amount="34,186.84">34,186.84</td>
         <td class="text-right nochange paid" id="tdNewPayment_23322665" data-type="newpayment" data-month="3" data-payquarters="Q1-27/28" data-paymonth="6" data-amount="34,186.84">34,186.84</td>
      </tr>
      <tr id="trPayment_23322666" data-year="5" data-type="payment" data-eventid="23322666">
         <th></th>
         <td></td>
         <td class="text-center" data-date="2027-07-01">01-07-2027</td>
         <td class="text-center">Paid</td>
         <td class="text-right nochange paid" id="tdPayment_23322666" data-type="payment" data-amount="34,186.84">34,186.84</td>
         <td class="text-right nochange paid" id="tdNewPayment_23322666" data-type="newpayment" data-month="3" data-payquarters="Q2-27/28" data-paymonth="7" data-amount="34,186.84">34,186.84</td>
      </tr>
      <tr id="trPayment_23322667" data-year="5" data-type="payment" data-eventid="23322667">
         <th></th>
         <td></td>
         <td class="text-center" data-date="2027-08-01">01-08-2027</td>
         <td class="text-center">Paid</td>
         <td class="text-right nochange paid" id="tdPayment_23322667" data-type="payment" data-amount="34,186.84">34,186.84</td>
         <td class="text-right nochange paid" id="tdNewPayment_23322667" data-type="newpayment" data-month="3" data-payquarters="Q2-27/28" data-paymonth="8" data-amount="34,186.84">34,186.84</td>
      </tr>
      <tr id="trPayment_23322668" data-year="5" data-type="payment" data-eventid="23322668">
         <th></th>
         <td></td>
         <td class="text-center" data-date="2027-09-01">01-09-2027</td>
         <td class="text-center">Paid</td>
         <td class="text-right nochange paid" id="tdPayment_23322668" data-type="payment" data-amount="34,186.84">34,186.84</td>
         <td class="text-right nochange paid" id="tdNewPayment_23322668" data-type="newpayment" data-month="3" data-payquarters="Q2-27/28" data-paymonth="9" data-amount="34,186.84">34,186.84</td>
      </tr>
   </tbody> 


                    
                </table>
2

I do not fully understand your requirements but this will give you the total ‘Reprofiled’ amounts in a specified year:

function total(year){
  let sum = 0;
  cells=document.querySelectorAll("tbody>tr>td:nth-child(6)");
  for(let z=0 ; z<cells.length ; z++){
    if(cells[z].dataset.payquarters.substring(3)==year) sum+=parseInt(cells[z].textContent.replace(/[\.\,]/g,""));
  }
  return sum/100;
}

alert(total("26/27"));

Incidentally, I have used textContent so there is no need to include data-amount attributes.

3

So Archibald’s most of the way there… though I agree, the requirements here aren’t 100% clear… I think I understand what you’re asking for. Mostly.

The goal is to balance the book, using the last value of the table to make up for any difference.

Whether you’re balancing the “payment” or “newpayment” i’m not sure of.

So for those following, I summate the request as:

  1. Select all entries for a given year.
  2. Remove the last entry, and hold reference for use.
  3. Sum remaining entries;
  4. Insert the difference between the Reported Year Total (Row 1 Column 2 for a given year) and the Summed Total as the NewPayment(?) value and text value for the final entry (the reference held from #2).

Archibald’s code gets you a good head start (I probably would have just used Number rather than trying to parseInt/manual division, but swings and roundabouts). See what you come up with from there.

1 Like
4

The Number() method does not like the commas in the table cell text content. If you remove the commas and then use Number() you are liable to get a long float number with many ‘9’ characters. If you remove the commas and decimal points (as in my CodePen) then ‘Number’ does the same as parseInt(). As it stands my JavaScript code relies on there always being two decimal places in the table cells (and no currency symbols).

5

and no spaces or other characters, but yes. If you remove the commas and decimal points, Number does the same, because you’ve reduced it to an integer, and then are manually re-dividing to get a float. Whereas Number would just work with the floats as they are. (And then doesnt care if the table cell contains 2 decimal places or gives a whole-dollar amount)

I have never seen Number generate a floating point error from a string; if you start doing division math with the floating result, you might indeed get rounding errors as you would with any float, or if you’re doing strict comparisons to the result… but can you show an example of a 2-decimal (or an integer, for that matter) string going through Number() and coming out with a floating point error?

6

After removing the commas this is giving 250254.23999999996 . . . .

Strange :grinning:

Of course you could still use toFixed(2).

7

Yes, but that’s not Number(), because if you do

    if(cells[z].dataset.payquarters.substring(3)==year) {
      let val = Number(cells[z].textContent.replace(/[\,]/g,""))
      console.log(val);
      sum+=val
    }

You see that it translated every number correctly; you get a floating point error because you do math with the float result, not because of the use of Number.

Again, swings and roundabouts (which… I have realized is perhaps not a well-translated idiom; “swings and roundabouts” means “it doesnt matter which way round you go, you’ll get to the same place in about the same amount of work; there are pros and cons to each”)