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

Back to top

3. SCAN Function Example

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:

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

Get the Excel file

How-to-use-the-SCAN-function.xlsx

Back to top