1

Is there a way to run through a column of number values in google spreadsheets (like in a "while" loop), incrementally suming the values and when reaching specific value print it in the corresponding cell? Example:

enter image description here

So here I sum up all the elements in A column, when reaching >=40 I print the value, refresh the counter, and continue this iteration, counting till the end of the column. So I get these portions of elements with approx. equals sum values.

Thank you!

1

Looks like a job for a custom function. In cell B2, put =sumuntil(A2:A, 40) where the code of sumuntil, entered in Script Editor, is

function sumuntil(arr, limit) {
  var out = [];
  var sum = 0;
  for (var i in arr) {
    sum = sum + arr[i][0];
    if (sum >= limit) {
      out.push([sum]);
      sum = 0;
    }
    else {
      out.push([""]);
    }
  }
  return out;
}
  • Thank you! But it gives me an ERROR. And theres no autocomplete when I start typing "sumuntil". But I did put the function intro script editor. Don't understand what could be wrong. – momijigari Feb 22 '18 at 9:26
  • error text "error of formula analysis" – momijigari Feb 22 '18 at 11:04
  • There is no autocomplete, because it's a custom function. But it works in my spreadsheet exactly as shown, I tested before posting. – user135384 Feb 22 '18 at 13:35
  • Oh, it was syntasyx error, had to use ";" instead of "," in "=sumuntil(A2:A; 40)". Thank you a lot! – momijigari Feb 22 '18 at 13:55
  • Just one small question from me. Could you tell me please if there's a way to change fill background colors for the cells with values in B column? Thank you! – momijigari Feb 22 '18 at 14:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.