This function scans an array and produces intermediate values by application of a LAMBDA function to each value. Returns an array of the intermediate values obtained at each step.
Sample Usage
SCAN(5, A1:A3, LAMBDA(accumulator, current_value, accumulator+current_value))
SCAN(2, A1:A3, LAMBDA(accumulator, current_value, accumulator*current_value))
Syntax
SCAN(initial_value, array_or_range, LAMBDA)
initial_value: The initialaccumulatorvalue.array_or_range: An array or range to be scanned.LAMBDA: ALAMBDAthat’s applied to each value inarray_or_rangefor scanning it.- Syntax:
LAMBDA(name1, name2, formula_expression) - Requirements:
- The
LAMBDAmust have exactly 2namearguments along with aformula_expressionwhich uses thosenames. Thename1resolves to the current value in theaccumulatorandname2resolves to thecurrent_valueinarray_or_range, when applying theLAMBDA. Theaccumulatoris updated in each step to the intermediate value obtained in the previous step.
- The
- Syntax:
Notes
- The passed
LAMBDAshould accept exactly 2namearguments, otherwise an#N/Aerror is returned. These arguments correspond toaccumulatorandcurrent_value, in order. These are explained as:name1: Resolves to the valueaccumulator.name2: Resolves to thecurrent_valuein the input array.
- The
accumulatoris initialized byinitial_valueand updated in each step to the intermediate value obtained in the previous step.
-
The
current_valuein the input array are found row by row, while you apply theLAMBDA.
- A
named functioncan be passed for theLAMBDAparameter and behaves like aLAMBDAin this case. Learn more about named functions.- The
named functionmust follow theLAMBDAsyntax forSCANwith exactly 2 argument placeholders defined for it. - The
named functionshouldn't be followed by parenthesis.
- The
Examples
Return the running total of an array
Example data:
|
A |
|
|---|---|
|
1 |
4 |
|
2 |
2 |
|
3 |
1 |
Example: =SCAN(5, A1:A3, LAMBDA(accumulator, current_value, accumulator+current_value))
Result:
|
9 |
|
11 |
|
12 |
Return the cumulative percentage of total value
Example data:
|
A |
|
|---|---|
|
1 |
4 |
|
2 |
2 |
|
3 |
1 |
Example: =SCAN(0, A1:A3, LAMBDA(accumulator, current_value, accumulator + current_value/sum(A1:A3)))
Result:
|
0.57 |
|
0.85 |
|
1 |
Return the running total of an array and restart calculation when the number is 0 with a named function as LAMBDA.
Example data:
|
A |
|
|---|---|
|
1 |
4 |
|
2 |
2 |
|
3 |
1 |
|
4 |
0 |
|
5 |
3 |
|
6 |
6 |
Example: =SCAN(0, A1:A6, RUNNING_TOTAL_0)
Named function: RUNNING_TOTAL_0 is a named function which outputs the running total of the array and restarts the calculation when the current_value is 0.
Formula definition: =if(current_value=0, current_value, accumulator+current_value), where accumulator and current_value are argument placeholders defined for RUNNING_TOTAL_0.
Result:
|
4 |
|
6 |
|
7 |
|
0 |
|
3 |
|
9 |
Common Errors
The passed LAMBDA doesn't have exactly 2 name argumentsIf the LAMBDA function doesn’t have 2 name arguments, this error occurs:
“Wrong number of arguments to LAMBDA. Expected 3 arguments, but got 2 arguments."
Example: =SCAN(5, C1:C4, LAMBDA(current_value, current_value+1))
In this example, LAMBDA was given only 1 name argument when it needed 2.
If the last parameter of SCAN function wasn’t a LAMBDA function, this error occurs:
“Argument must be a LAMBDA.”
Example: =SCAN(5, C1:C4, 3)
In this example the last function is 3, instead of a LAMBDA function.
If 1 or more name arguments aren’t valid, this error occurs:
“Argument 1 of function LAMBDA is not a valid name.”
Example: =SCAN(5, C1:C4, LAMBDA(C1, v, C1+v))
In this example, C1 is an invalid name since it clashes with a range.
If the application of LAMBDA on the input array produces any non-single intermediate value, this error occurs:
“Single value expected. Nested array results are not supported.”
Example: =SCAN(5, C1:C4, LAMBDA(accumulator, value, {accumulator, value}))
Every application of LAMBDA must produce an intermediate value which is a single value only and can’t be another array.
Related functions
- LAMBDA function: This function lets you create and return a custom function with a set of
namesand aformula_expressionthat uses them. - MAP function: This function maps each value in the given arrays to a new value.
- REDUCE function: This function reduces an array to an accumulated result.
- BYROW function: This function groups an array by rows.
- BYCOL function: This function groups an array by columns.
- MAKEARRAY function: This function creates a calculated array of specified dimensions.
- Create & use named functions: This function lets users create and store custom functions, similar to
LAMBDA.