Author: Oscar Cronquist Article last updated on March 13, 2023

How to use the SCAN function 2

The SCAN function passes all values in an array to a LAMBDA function, the LAMBDA function calculates new values based on a formula you specify. It then returns an array with the same size as the original array. (This is not always the case.)

1. SCAN Function Syntax

SCAN([initial_value], array, lambda(accumulator, value, calculation))

Back to top

2. SCAN Function Arguments

Argument Description
[initial_value] Optional. Lets you specify a starting value.
array Required. A cell reference to a cell range or an array.
lambda(
accumulator,
value, calculation)
Required. A lambda function with three arguments:
- accumulator
- value
- calculation
accumulator Required. This is the sum the SCAN function returns.
value Required. Iterates through each value in the array or cell range.
calculation Required. A calculation containing the accumulator and value parameters.

Back to top

3. SCAN Function Example

How to use the SCAN function 2

This example demonstrates a formula that calculates cumulative interest based on a cell range containing loan balance numbers (D9:D20).

The table in the image above shows a linear loan meaning the monthly payment varies contrary to an annuity loan that has a fixed monthly payment.

This is a simple demonstration of the SCAN function, I know that the formula can be made a lot smaller using only the division, multiplication, and addition operators.

Formula in cell D3:

=SCAN(0,D9:D20,LAMBDA(a,b,a+b*$D$6/12))

Explaining the formula

Step 1 - Build the LAMBDA function

The LAMBDA function build custom functions without VBA, macros or javascript.

Function syntax: LAMBDA([parameter1, parameter2, …,] calculation)

The LAMBDA function is iterated as many times as there are values in cell range B3:C11.

LAMBDA(a,b,a+b*$D$6/12)

The first argument specifies the accumulator variable, the second argument is the value variable. The third argument is the actual calculation:

a+b*$D$6/12

It calculates the interest for each month and adds it to a total (accumulate variable a).

Step 2 - Pass values to the LAMBDA function

The SCAN function lets you pass arrays to the LAMBDA function.

SCAN(0,D9:D20,LAMBDA(a,b,a+b*$D$6/12))

Each value in cell range D9:D20 is put in variable b, it is then added to a total.

Back to top

Back to top