How to use Excel SCAN Function (With Examples)

The Excel SCAN function lets you perform cumulative operations over a list of values. It is mainly used to apply a custom LAMBDA calculation to each element of the given array and return an array that contains the intermediate values.

For example, you can find the running total (cumulative sum) of the expenses over the given period. Refer to the following image,

Our formula will be as follows:

=SCAN(0, B2:B11, LAMBDA(a, b, a + b))

Where,

0 is the initial value or accumulator
B2:B11 is the range that includes expenses
LAMBDA(a, b, a + b) is the formula to calculate the running expenses

The results are displayed in the following image,

The SCAN function progressively applies a LAMBDA formula to each element in an array. It progressively updates the initial value or accumulator after each step.

Syntax

The SCAN is a simple logical function with the following syntax. It is used to return an array of all intermediate results during cumulative operations.

=SCAN(initial_value, array, function)

Where,

initial_value is the accumulator that allows you to start your LAMBDA calculation with a specific number
array needs to be replaced with the array or range to which you wish to apply the LAMBDA calculation
function lets you specify the LAMBDA formula to be applied on the given array or range

Important Notes:

The function is compatible with both the vertical and horizontal arrays.
The initial value or accumulator is optional. If not specified, the function takes zero as the initial value.
The LAMBDA formula must have two parameters: the accumulator and the current value.
If the LAMBDA function is incorrect, you will get a #VALUE! error.

How to use the SCAN Function in Excel

Download the example spreadsheet used to demonstrate the SCAN function in the upcoming section. Practice is the key to success!

Download Now

Example 1: SCAN Function Basics

Let us assume that you are a Sales Manager. You wish to track daily sales generated by your sales reps. Here are Jack’s sales figures over the given period.

Our task is to calculate cumulative sales or running totals over the given week.

Here are the steps,

Select the cell C1
Type =SCAN
Double-click the SCAN command
Specify 0 as the initial sales for the given week
(It is our initial accumulator)
Type , to move to the next argument
Specify the range as B2:B8
(It includes sales figures over the given period)
Type ,
Specify LAMBDA
(As the LAMBDA is a built-in function in Excel, you will see the following popup)
Choose the first option from the popup
Specify total as the first parameter for the LAMBDA function
Type ,
Specify sale as the second parameter
Type ,
Put total + sale formula to calculate the sale for the given date
Complete the bracket for the LAMBDA function using )
Use one more ) to close the bracket for the SCAN function
Hit the Enter key

Our final formula is as follows,

=SCAN(0, B2:B8, LAMBDA(total, sale, total + sale))

Where,

0 is the initial sales value
B2:B8 is the range that includes Jack’s sales figures
LAMBDA(total, sale, total + sale) is the formula to calculate the sales for the given day
total and sale are parameters for the LAMBDA function

The results are displayed in the following image,

To learn more about the Excel LAMBDA function, click here: Learn Excel LAMBDA Function (Formula, Syntax, Examples)

Example 2: Combining SCAN with Other Functions to Create Complex Formulas

Assume that you are a private tutor. You are planning to conduct 5 quizzes for students. Those who pass will be eligible for special rewards or gifts. You want to calculate the running total and make sure to mark the student as Passed if he/she reach or exceed 30.

Let us start with Jack. He has obtained the following scores.

We will combine the IF and LET with the SCAN function.

IF Function: It is a logical function in Excel. It can test a condition and return values based on whether the condition is satisfied or not. Click here to learn more about the IF function.
LET Function: This function lets you assign names to each value and calculation in the given formula. It is used to simplify the complex formulas and make them more readable. Click here to learn more about the LET function.

Our final formula is as follows,

=LET(totals,
   SCAN(0, B2:B6, LAMBDA(acc, s, acc + s)),
     IF(totals >= 30, totals & " - Passed", totals)
)

Where,

totals is the first named variable.
SCAN(0, B2:B6, LAMBDA(acc, s, acc + s)) is the calculation assigned to the first named variable. It calculates the cumulative sum (running total) of the scores obtained by Jack.
totals >= 30 is the logical expression to identify if the running total is greater than or equal to 30 during any stage of the scan.
totals & ” – Passed” is the desired output if Jack could reach or exceed passing marks. It combines a running total and the message “Passed”. Refer to the following GIF.
totals or running total is the default output.

The results are displayed in the following GIF,

Things to Avoid While Using the SCAN Function in Excel

Initial Value: It is the starting point for the function. The initial value plays a vital role in the overall functioning of the SCAN function. Most of the time, we tend to use zero as the initial value. However, in the case of complex formulas, it may take a numeric value.
Using LET Function: Make sure to be minimalistic while defining the LAMBDA formula. When it becomes too complex, use the LET function to break it down.

SCAN vs. REDUCE Function in Excel

Both functions perform a LAMBDA calculation on each element of the given array. The difference lies in their outputs:

The SCAN function returns an array of values
The REDUCE function returns a single value

Moreover, the syntax and functioning of both functions remain the same. To learn more about the REDUCE function, click here.

Takeaway

The SCAN function in Excel allows users to find running totals, running counts, and cumulative statistics. It is particularly useful when you wish to perform calculations that create intermediate or incremental results. It returns an array of values.

You may take some time to learn this function, but the effort is totally worth it. Make sure to practice the examples and refer to the examples above until you master the SCAN function in Excel.

That said, I hope this article taught you all the bells and whistles of the SCAN function. Please comment below if you are stuck or encounter any particular error. I will answer your questions as soon as possible.

Additional Resources:

Get an Office 365 Subscription to Access all the powerful Functions and Tools in Excel.