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([𝗶𝗻𝗶𝘁𝗶𝗮𝗹_𝘃𝗮𝗹𝘂𝗲], 𝗮𝗿𝗿𝗮𝘆, 𝗳𝘂𝗻𝗰𝘁𝗶𝗼𝗻)
[𝗶𝗻𝗶𝘁𝗶𝗮𝗹_𝘃𝗮𝗹𝘂𝗲] — accumulator’s starting value. Omitting this is the same as 0 or “”.
𝗮𝗿𝗿𝗮𝘆 — array to scan.
𝗳𝘂𝗻𝗰𝘁𝗶𝗼𝗻 — custom LAMBDA applied to each array element.
The LAMBDA arguments are:
𝗮𝗰𝗰𝘂𝗺𝘂𝗹𝗮𝘁𝗼𝗿 — values totalled up. Starts at [𝗶𝗻𝗶𝘁𝗶𝗮𝗹_𝘃𝗮𝗹𝘂𝗲].
𝘃𝗮𝗹𝘂𝗲 — 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, 𝘢𝘤𝘤𝘶𝘮 holds the Sales total whilst 𝘷𝘢𝘭𝘶𝘦 cycles through each figure and adds it to 𝘢𝘤𝘤𝘶𝘮.
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’ (https://lnkd.in/esjsxyhm).
Are you excited about the SCAN function?
