How to create a running total in Apple Numbers (4.0ff)

numbers

A typical use of a spreadsheet is to create a running total, as for a checkbook register. The usual way to do this is to simply add a current row to a prior row. My example is a variation — showing the accumulation of bills over time.

Typical running total using

In text, the formula for "Amount" in row "Homeowners" is 

   ($Amount Homeowners) + 
IF (ROW() = ROW($Amount $electric Nov), 0, Calculate via Prior Sum electric Nov)

However, things get a little sticky if you want to interpose some notes (cell background red here) or whatever:

Typical running addition fails when you vary your use of a row

It fails in either column, and ruins all successive values

Fails in running total column, too

Best Answer

The way I got around this today was to generate a range dynamically and use the SUM operation on it. SUM ignores non-numeric entries:

SUM over range gracefully skips alphabetic entries

The formula I use sets a fixed initial cell to start the running total, column "Amount" in row "electric Nov". I can't use regular tricks, I need to generate the range dynamically for each row using INDIRECT. This requires I create a string identifying the range to sum up. This range should only go up to the current row (which is what makes it a running total). I don't want to use fixed columns A, B, C, etc., which break if I need to shift columns around; I need to use the initial cell with the numeric column number, and I must use the "R1C1" cell notation instead of "A1".

So I concatenate the (unchanging) row and column numbers from the initial cell, a colon (:) to indicate a range, and then the current row number and column number. I have pretty-printed the formula (at least I think it's pretty!) below.

    SUM (
        INDIRECT (
           CONCATENATE (
               "R",
               ROW ($Amount $electric Nov),
               "C",
               COLUMN($Amount $electric Nov),
               ":",
               "R",
               ROW(),
               "C",
               COLUMN($Amount $electric Nov)
           ),
           FALSE
         )
    )

Note that this goes quite gracefully past non-numeric entries to give a reliable running total.

I hope this helps someone out there!

Related Question