CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetscustom-function
Lee Davis-Thalbourne
Lee Davis-Thalbourne

Reputation: 133

Custom Google Sheets function to find position of subtotal

I'm currently trying to write a custom function for google sheets that, given a 1-dimensional range, tells me where in that range the cumulative sum reaches (or surpasses) a given total, or throws an error if the cumulative sum never reaches the given total.

As an example, given a range like so:

10
12
14
15
18
 3
 8
 9

If the sum requested was "24", the function would return 3.

If the sum requested was "60", the function would return 5.

If the sum requested was "1000", the function would throw an error.

The code I'm currently stuck on is:

function SUBTOTALPOSITION(range,sum)
{
 /**
 * Finds the position in a one-dimensional range where
 * the cumulative sum reaches the requested subtotal
 *
 * @range The range being searched (1-dimensional range only)
 * @sum The cumulative sum being searched for
 * @customfunction
 */
  if((range.length > 1) && (range[0].length > 1))
  {
    throw 'range is multi-dimensional';
  }

  var position = 0;
  var total = 0;
  while(position < range.length)
  {
    total = total + range[position];
    position++;

    if(total >= sum)
    {
      return position;
    }
  }
  throw 'Sum not reached';
}

I've reached a point where it's giving me a position, but not the correct one. If I give a sum equal to or less than the first number in the range, it correctly returns 1, but any other number given always returns 2 (and never throws an error), and I've reached my limits of debugging ability.

Where in this code am I going wrong? Or is there a better approach to this problem? Can this be done without custom functions?

Upvotes: 2

Views: 73

Answers (1)

Tanaike
Tanaike

Reputation: 201573

How about this modification?

Modification point :

  • I think that the reason of error is range[position] of total = total + range[position]. Because when the values of 10,12,14,15,18, 3, 8, 9 are put to A1:A8, range given by =SUBTOTALPOSITION(A1:A8, 100) is [[10.0], [12.0], [14.0], [15.0], [18.0], [3.0], [8.0], [9.0]]. This is 2 dimensional array. range[position] is an object. So in this case, when total = total + range[position] is run, total is used as a string. By this, each element of the array is summed as the string like 0101214.... As the result, when sum is 1000, total becomes 01012 at the second loop, and total >= sum becomes true. So 2 is returned.

In order to remove this issue, please modify as follows.

From :

total = total + range[position];

To :

total = total + range[position][0];

or

total += range[position][0];

If I misunderstand your question, I'm sorry.

Upvotes: 1

Related Questions