SCAN
The SCAN function scans an array and returns intermediate values calculated by a LAMBDA function.
SCAN(initial-value, array, LAMBDA(accumulator, value, calculation))
initial-value: The initial value of the accumulator.
array: The input array.
LAMBDA: A defined subformula.
accumulator: The identifier for the initial value.
value: The identifier for the current value in the array.
calculation: The formula you want the function to execute and return. This is required and must be the last argument.
Notes
You can use the spilled range operator “#” to reference an array created by a formula.
Example 1 |
|---|
Given the following table: |
A | |
|---|---|
1 | 1 |
2 | 2 |
3 | 3 |
=SCAN(5, A1:A3, LAMBDA(accumulator, value, accumulator*value)) returns the table below. |
A | |
|---|---|
1 | 5 |
2 | 10 |
3 | 30 |
Example 2 |
|---|
Given the following table: |
A | B | C | |
|---|---|---|---|
1 | 10 | 20 | 30 |
2 | 40 | 50 | 60 |
=SCAN(10, A1:C2, LAMBDA(accumulator, value, accumulator+value)) returns the array below. |
A | B | C | |
|---|---|---|---|
1 | 20 | 40 | 70 |
2 | 110 | 160 | 220 |