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 'Logical' category
The SCAN function function is one of 18 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 signs
Use 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 Oscar
You can contact me through this contact form