As of the writing of this post (November 2021), the SCAN function is still in BETA development and is only available to select Office Insider Program participants using Microsoft 365 (formerly known as Office 365.)
The SCAN function accepts an array of values and a formula created using the LAMBDA function.
SCAN then iterates through each value in the array, executes the LAMBDA formula, then returns an array of results for each intermediate value.
The syntax for the SCAN function is as follows:
=SCAN ([initial_value], array, lambda(accumulator, value) )- [initial_value] sets the starting value for the accumulator.
- array is the array of values being scanned.
- lambda is a LAMBDA function being called to scan the array.
The LAMBDA function accepts two parameters:
- accumulator is the value totaled up and returned as the final result.
- value is the calculation that is applied to each element in the array.
We begin with a dataset that has sales listed by month for a series of years. The table is formatted as an official Excel Table and is named “Sales”.
Creating a “To-Date” Formula
Our first pass over this data will be to add each new month’s sales to the previous running total. Even if we cross years, we want to continue adding each new month’s sales to the previous total.
(We’ll reset the formula for each new year in the next example.)
We’ll start our formula in cell E2 by writing the following:
=SCAN(0, Sales[Sales]The 0 (zero) sets our [initial_value] (this will become more understandable in a moment) and Sales[Sales] points to the “Sales” column in the “Sales” table.
Next, we must write a LAMBDA formula that will be exercised on each value in the “Sales” column.
When using the LAMBDA function with the SCAN function, LAMBDA requires two parameters:
- The first parameter is the accumulator. This holds each iteration’s result. If this is the first iteration, then this holds the value declared in the [initial_value]
- The second parameter is a placeholder that holds the current iteration’s value.
You can name these parameters anything you wish. These names can be something like “previous_value” and “current_value”, but they can also be something as simple as “a” and “b”.
=SCAN(0, Sales[Sales], LAMBDA(a, b,The final piece to this puzzle is the calculation to be performed on “a” and “b”.
As we just want to add the current value (“b”) to the previous iteration’s result (“a”), or formula will just be a simple “a + b”.
=SCAN(0, Sales[Sales], LAMBDA(a, b, a + b) )
If we had set the [initial_value] to 1 Million, the first iteration through the array would be 1 Million plus the array’s first cell value ($8,603 in this example).
It should now make sense why we set our [initial_value] to 0 (zero).
Featured Course
Master NEW Excel Functions in Office 365 & Office 2021
Stepping Through the SCAN/LAMBDA Process
To better understand what is occurring at each step in this process, let’s walk through the first three iterations of the “Sales” array.
Iteration # 1
“a” = 0 (this is the [initial_value])
“b” = 8,603 (this is the value in the array’s first cell)
The result of “a + b” is 8,306.
Iteration # 2
“a” = 8,306 (this is the result of the previous iteration)
“b” = 9,687 (this is the value in the array’s second cell)
The result of “a + b” is 18,290.
Iteration # 3
“a” = 18,290 (this is the result of the previous iteration)
“b” = 7,811 (this is the value in the array’s third cell)
The result of “a + b” is 26,101.
This process repeats for every value in the array of sales.
Featured Course
Power Excel Bundle
Creating a “Year-To-Date” Formula
What if we want to reset the results of LAMBDA back to the [initial_value] of 0 (zero) each time we encounter a new year’s sales?
We need to program the SCAN function to examine the “Month” column in the “Sales” table to detect the month of “Jan” or any other month you may be using, such as Fiscal Years that begin in “Oct” or any other month.
As our dates in the “Month” column are input as actual dates…
… we can’t just look for the string “Jan”, as “Jan-19” is the formatted version of “1/1/2019”.
We begin the SCAN formula as before:
=SCAN(0, Sales[Sales], LAMBDA(a, b,At this point, we don’t want to blindly calculate “a + b”; we want to check the date to see if it is January or some other month.
As we are not looking at the “Month” column (column A), we have to use a function that will tell LAMBDA to “look one cell to the left”.
We can use the OFFSET function for this step.
The OFFSET function will need to start from the current location (this will be wherever “b” happens to be at the moment), then move no rows up or down (0), then move 1 column to the left (-1).
OFFSET(b, 0, -1)The result of the OFFSET will be examined by the MONTH function to extract the month number.
MONTH(OFFSET(b, 0, -1) )If the currently examined date is for June, the result would be “6”.
This result will be tested using an IF function to see if the result is any value other than 1 (January).
IF(MONTH(OFFSET(b, 0, -1) ) <> 1NOTE: If you are using a Fiscal Year that begins in October, you would test against a 10.
IF(MONTH(OFFSET(b, 0, -1) ) <> 10If the test results in a TRUE (i.e., any month other than January), we will perform the standard “a + b” formula.
IF(MONTH(OFFSET(b, 0, -1) ) <> 1, a + bIf the test results in a FALSE (i.e., this is a January date), we want to reset the accumulator back to 0 (zero). This can be accomplished by multiplying the accumulator (“a”) by 0 (zero) then adding that result to “b”.
IF(MONTH(OFFSET(b, 0, -1) ) <> 1, a + b, 0 * a + b)The final formula would appear as follows.
=SCAN(0, Sales[Sales], LAMBDA(a, b, IF(MONTH(OFFSET(b, 0, -1) ) <> 1, a + b, 0 * a + b) ) )
You could also write the IF test so that it checks for “1” as the month (January) and performs the accumulator reset, otherwise, it runs the “a + b” formula.
=SCAN(0, Sales[Sales], LAMBDA(a, b, IF(MONTH(OFFSET(b, 0, -1) ) = 1, 0 * a + b , a + b) ) )The Benefits of Proper Excel Tables
The great thing about using proper Excel Tables when building these formulas is that as the table “grows” when new rows are added, the SCAN/LAMBDA function will grow to match the new table length.
Practice Workbook
Feel free to Download the Workbook HERE.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
