Scan array and return intermediate results
=SCAN([initial_value], array, function)| Parameter | Description |
|---|---|
initial_value |
[optional] The initial value of the accumulator. Default is zero. |
array |
The array to be scanned. |
function |
The function or custom LAMBDA to apply. |
The SCAN function takes three arguments: initial_value, array, and function. Initial_value is the initial seed value to use for the first result. Init
LAMBDA(a,v,calculation)The first argument, a, is the accumulator used to store intermediate values. The accumulator begins as the initial_value provided to SCAN and changes
=SCAN(0,{1,2,3},LAMBDA(a,v,a+v)) // returns {1,3,6}A simple use of SCAN is to create a running total. In the worksheet shown below, we have a list of values in the range B5:B16, and we want to create a
=SCAN(0,B5:B16,LAMBDA(a,v,a+v))Like other newer dynamic array functions, SCAN supports an abbreviated syntax for the function argument. Using the abbreviated syntax, the formula abo
=SCAN(0,B5:B16,SUM)It is not obvious, but SCAN is delivering two values, the accumulator and the value to the SUM function at each loop. The result is a running total. W
=SCAN(0,B5:B16,COUNT) // returns {2,2,2,2,2,2,2,2,2,2,2,2}One of SCAN's key strengths is its ability to work with dynamic arrays. When you give SCAN an array that is dynamically generated, it will automatical
=SCAN(0,TRIMRANGE(B5:B1000,2),SUM)