Running Totals

A running total is the summation of the current and previous numbers in a column. It’s a useful way of keeping track of sales figures over a time period or for monitoring the balance on a bank statement.

Have a look at the Running Total 1 column in Image 1.

In Old Excel, one might use a formula akin to =SUM($B$3:B3). The expanding range references the first cell of the Sales column in the January table. The first part of the reference is absolute and the second relative, which means the latter’s row number increases incrementally as the cell is dragged downwards with the fill handle.

The problem with this approach is it’s inefficient. It’s likely to cause problems further down the line when you add or delete rows, causing the formulas to mess up.

Whilst there are other alternatives, including dynamic array-friendly solutions, they often involve long windy formulas that aren’t exactly pleasant to work with.

Let me introduce the SCAN function. It’s only available to Office Insiders currently, but it’s worth getting clued up about as it’s likely to come in handy one day.

The syntax is:

SCAN([initial_value], array, function)

[initial_value] — accumulator’s starting value. Omitting this is the same as 0 or “”.

array — array to scan.

function — custom LAMBDA applied to each array element.

The LAMBDA arguments are:

accumulator — values totalled up. Starts at [initial_value].

value — calculation applied to each array element.

The Running Total 2 column returns the same values but contains just one formula.

0 is the starting value and the Sales column is the array. Inside the LAMBDA function, accum holds the Sales total whilst value cycles through each figure and adds it to accum.

SCAN also works with text, as you’ll see in Image 2.

Here, Manchester United’s running Premier League form is displayed for each match — ‘W’ for win, ‘D’ for draw and ‘L’ for loss. Notice how the formula is structured the same way as the previous example, except the concatenation operator (&) replaces the plus (+).

Adjacent to this column is the same thing for points along with the running total.

How can calculations be performed on this data?

Image 3 shows several examples of how it can be extracted to create useful results and points summaries. You can find more information on how to work with same-cell data by reading my article, ‘How to Calculate Multiple Values in the Same Cell’.

Are you excited about the SCAN function?