SCAN

Formulas / SCAN
Return intermediate results from scanning an array.
==SCAN(initial_value,array,lambda)
  • initial_value - [OPTIONAL] the initial value of the accumulator
  • array - the array to search
  • lambda - the custom LAMBDA function to apply

Examples

  • =SCAN(0,B5:B16,LAMBDA(a,v,a+v))

    The SCAN function can be used to return the running sum of values in a range. For example, the formula returns the running sum of values in the range B5:B16.

  • =SCAN(0,{1,2,3},LAMBDA(a,v,a+v))

    The SCAN function can be used to return an array of values. For example, the formula returns {1,3,6}.

  • =SCAN(1,{1,2,3},LAMBDA(a,v,a*v))

    The SCAN function can also be used to calculate more complex values in an array. For example, the formula returns {1,2,6}, which is the product of all the values in the array.

  • =SCAN(0,{1,2,3},LAMBDA(a,v,IF(v>1,a+v,a)))

    The SCAN function can also be used to calculate values based on a condition. For example, the formula returns {0,3,5}, which is the sum of all values greater than 1 in the array.


Summary

The SCAN function is a powerful tool that can quickly apply a custom LAMBDA function to each element in an array, resulting in an array of the intermediate values created during the process.

  • The SCAN function applies a custom LAMBDA function to each element in a given array, returning an array of intermediate values. The LAMBDA function is used to perform the required formula logic.
  • Like the REDUCE function, SCAN iterates over all elements in an array and performs a calculation on each element in turn.
  • The SCAN function returns an array with the same dimensions as the original array.
  • The SCAN function can be used to generate running totals and other calculations that show results in between sets of values.
  • MAP processes each element in an array and returns an array of non-intermediate results.

Frequently Asked Questions

What is the SCAN function?
The SCAN function returns an array of intermediate values from a calculation applied to each element in an array.
How does the SCAN function work?
The SCAN function takes three arguments: initial_value, array, and lambda. The initial_value argument sets the starting value for the accumulator, the array argument is the array to scan, and the lambda argument is a function that is called to scan the array. The SCAN function returns an array with each intermediate value.
What are the arguments for the SCAN function?
  • initial_value - sets the starting value for the accumulator
  • array - the array to scan
  • lambda - a function that is called to scan the array
What does the SCAN function return?
The SCAN function returns an array with each intermediate value.

Related Functions

Sourcetable Logo

Work smarter

Al is here to help. Leverage the latest models to
analyze spreadsheets, enrich data, and create reports.

Drop CSV