3

QUESTION:

How do I create a running balance that has the following criteria:

  1. It uses an array formula
  2. the rows don't need to be sorted by date, but running total only returns balance of:
    • Rows with the same or earlier dates
    • Rows with the same exact account name
  3. Balance accrues by Date, by Name. (i.e. it filters all results by one name and only adds AMT's whose date is the same or comes before)

CONTEXT:

I've created a check ledger, that accounts for deposits and withdrawals. here is a link to what I've been working on so far ( it has more columns than the picture below: because I've experimented with several solutions. ) enter image description here

WHAT I'VE TRIED: see google sheet

  • =ARRAYFORMULA(SUMIF($A$2:A,$A$2:A,$C$2:C)) This only gives the current total ( not the running total for each account & it completely ignores the date column.
  • Using an Array =SORT(A2:C,2,TRUE) to populate duplicate columns of A through C except now they are sorted. Then adding the formula =SUMIF($H$2:H2,H2,$J$2:J) Where column H is the sorted column of A & Column J is the sorted column C. Then copying that formula down to the rest of the column. Problem is, that it is not an Array. Although the result is closest to what I'm hoping to accomplish.
3

If you did not insist on array-based formula, then

sumifs(C:C, A:A, A2, B:B, "<="&B2)

entered in D2 and copied around the D column, would do the job.

I don't think that an arrayformula-based solution exists, because arrayformula runs through an array once, performing some element-wise operations. You want to compare each element with all others, which is one step further in complexity (a double for loop).

But you can use a custom function, something like =runningtotal(A2:A, B2:B, C2:C) which processes the columns and returns an array of running totals. The script implementing this function is below; add it via Tools > Script Editor. The logic is quite straightforward: as said above, it's a double for loop.

function runningTotal(names, dates, amounts) {
  var sum, totals = [], n = names.length;
  if (dates.length != n || amounts.length != n) {
    return 'Error: need three columns of equal length'; 
  }
  for (var i = 0; i < n; i++) {
    if (names[i][0]) {
      sum = 0;
      for (var j = 0; j < n; j++) {
        if (names[j][0] == names[i][0] && dates[j][0] <= dates[i][0]) {
          sum = sum + amounts[j][0];
        }
      }
    }
    else {
      sum = '';
    }
    totals.push([sum]);
  }
  return totals;
}
  • I think it still might be possible to do it with just arrays using a set of complicated matrices... but they also lag quite a bit, and the logic elludes me. Question, in terms of making google-sheets run more quickly, would it be better to just use the formula and copy it down? -- or will the script you created for me keep the sheet from lagging as much? – Xzila Dec 14 '15 at 18:27
  • 1
    Using sumifs and copying it down should be faster than the custom function. – user79865 Dec 14 '15 at 18:36
-1

You can use an ARRAYFORMULA, combined with SUMIF:

=ARRAYFORMULA(SUMIF(A2:A,"="&A2:A,C2:C))
  • 1
    This answer would be much better if you could describe what the different parts of the formula do. – Vidar S. Ramdal Mar 13 '17 at 8:28
  • 1
    This formula is not desired because all results are just totals by the Name Column, and do not take into account the dates. The best result so far was given by user79865 ( although it doesn't use arrays ) sumifs(C:C, A:A, A2, B:B, "<="&B2) <-- this formula must be "copied down" such that it exists on every row being operated upon. – Xzila Mar 13 '17 at 21:59

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.