Excel functions / Logical
SCAN function
Like REDUCE, but returns every step: a running total, a running maximum and the like.
Syntax
=SCAN(initial_value, array, lambda)
Arguments
initial_value: What the running value starts as. Left empty, it starts as the first item.array: The array to go through.lambda: A LAMBDA with two inputs: the running value, then the item.
When to use it
SCAN works like REDUCE, but spills the running result after every step instead of only the last one. A running total is the classic use, without an expanding SUM in every row.
Examples
A running total
A B C 1 Sales 2 100 100 3 150 250 4 90 340 In C2:
=SCAN(0, A2:A4, LAMBDA(total, x, total+x))→ 100; 250; 340The highest so far
A B C 1 Score 2 5 5 3 3 5 4 8 8 5 6 8 In C2:
=SCAN(0, A2:A5, LAMBDA(best, x, MAX(best, x)))→ 5; 5; 8; 8
Common mistakes
- The starting value. The first argument is where the running result starts: 0 for a total, 1 for a product.
Practise it
Related functions
- REDUCE: Boils an array down to one result by running a function on a running value and each item in turn.
- MAP: Runs a function on each value of an array and returns the results in the same shape.
- LAMBDA: Makes a function of your own: names for its inputs, then the calculation.
- SUM: Adds up numbers, cells and ranges.