Get row name if the accumulated sum is the NEAREST to 30 and reset the sum again

Osm 0
Viewed 60

I have a table with 2 columns: input and length. Make a copy of the example sheet.

The Ask
I want to output the row numbers in a single formula if the accumulated sum of the "Length" B2:B is the closest to 30, Reset the sum, and do the same.

the desired result is shown in C2:C

Example

C2 = sum of $C1:C1 is 8 and sum of $C1:C2 is 18 >>>> Do Nothing
C3 = sum of $C1:C2 is 18 and sum of $C1:C3 is 28 >>> Do Nothing
C4 = sum of $C1:C3 is 28 and sum of $C1:C4 is 36>>> output the row number 4
---because the following cell C4 sum $C1:C4 is => 30
C4 = sum of $C1:C4 is 36 "equal or exceeded 30 so reset the sum">>> 

enter image description here

accumulate row array-formulas sum google-sheets
1 Answers

I tried and tested this on a custom function, but the issue is that setFormulas() applies =ROW() as a string value instead of it being detected as a sheets formula, so the script below needs to be manually run.

Try this script instead:

  function resetthirty() {
    var ss = SpreadsheetApp.getActiveSpreadsheet(); 
    var sheet = ss.getActiveSheet();
    var data = ss.getActiveRange();
    var values = data.getValues();
    var range2 = sheet.getRange(data.getRow(), data.getColumn() + 1, values.length, 1); 
    console.log(values);
    var sum = 0;
    var acc = [];
    var out = [];
    for (var i = 0; i < values.length; i++) {
      sum += parseInt(values[i]);
      if (sum > 30) {
        sum = parseInt(values[i]);
        out.push(["=ROW()"]);
      }
      else out.push([""]);
      acc.push(sum);
    }
    out.shift();
    out.push(['']);
    range2.setFormulas(out);
}

Output:

NOTE: You need to highlight the values on Column B first, and then run the script because the script uses getActiveRange().

enter image description here

References:

https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app#getactiverange

Diego Sanchez0
Related