# SCAN Function in Microsoft Excel

A common calculation for financial analysts and accountants is the Year-to-Date calculation.

There are as many ways to perform this calculation, and it largely depends on the structure of your data set as to which way you choose.

In many cases, these formulas can become quite complex.

Let’s check out a new way to perform this calculation using the brand new, bright and shiny, still has that “new function” smell function called **SCAN**.

This will allow us to create every YTD formula we need in our table using only a single formula.

Let’s see how.

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 (

*“*), or formula will just be a simple “

**a**”**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*).

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

# 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) ) <> 1

NOTE: If you are using a Fiscal Year that begins in **October**, you would test against a **10**.

IF(MONTH(OFFSET(b, 0, -1) ) <> 10

If 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 + b

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