## How to use the SCAN function

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.)

#### Table of Contents

## 1. SCAN Function Syntax

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

## 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. |

## 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.

### 'SCAN' function examples

The following article has a formula that contains the SCAN function.

What's on this page Reverse text Insert random characters Convert letters to numbers How to shuffle characters in the alphabet […]

## Functions in this article

### Functions in 'Logical' category

The SCAN function function is one of many functions in the 'Logical' category.

## How to comment

How to add a formula to your comment<code>Insert your formula here.</code>

Convert less than and larger than signsUse html character entities instead of less than and larger than signs.

< becomes < and > becomes >

How to add VBA code to your comment[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

How to add a picture to your comment:Upload picture to postimage.org or imgur

Paste image link to your comment.

Contact OscarYou can contact me through this contact form