Overview
The Excel SCAN function scans an array by applying a LAMBDA function to each value and returns an array that has each intermediate value.
Syntax
=SCAN([initial_value], array, function)
Arguments
initial_value – [optional] The starting value for the accumulator.
array – The array to be scanned.
function – A LAMBDA that is called to scan the array.
Version
Excel 365
Purpose
The Excel SCAN function scans an array by applying a LAMBDA function to each value and returns an array that has each intermediate value created during the scan. SCAN can be used to create a running total or calculate incremental results.
=SCAN([initial_value], array, function)
Examples
Let’s take a look at how we can use the SCAN function in it’s simplest form:
The formula we have in cell B7 (and is spilled over to the adjacent cells) is:
Let’s break this formula down. Our first argument is the initial value, which we set to 1. The second argument is the array we wanted scanned, which is B4:D5 (numbers 1 through 6 in a 2×3 array). Lastly, our LAMBDA function takes two inputs. The ‘a’ input is our initial value and the ‘b’ input is the individual element in the array. The calculation applied in the LAMBDA function is the initial value multiplied by the individual element value.
Looking at cell B7, we get a result of 1 (initial value = 1, element value = 1; 1*1 = 1). Looking at cell C7, we get a result of 2 (intermediate value = 1 from previous LAMBDA, element value = 2; 1*2 = 2). Looking at cell D7, we get a result of 6 (intermediate value = 2 from previous LAMBDA, element value = 3; 2*3 = 6). Carrying this logic onward, we get a result of 24 in cell B8 (6*4), 120 in cell C8 (24*5) and 720 in cell D8 (120*6).
Let’s see what difference adjusting the initial value from 1 to 2 makes:
The formula we have in cell B7 (and is spilled over to the adjacent cells) is:
Here we’ve only changed the initial value from 1 to 2, and we can see how the results spilled across from the SCAN function have doubled. This is expected as our ‘a’ value in the LAMBDA has doubled from the original example.
The SCAN function can also be used on text string, instead of just numbers. We can see this below:
The formula we have in cell B7 (and is spilled over to the adjacent cells) is:
Here instead of multiplying intermediate values together, we can instead combine or concatenate text string using the ‘&’ symbol. In this example, we set the initial value to “”, or blank. This helps better illustrate how the SCAN function performs it’s intermediate calculations in an array, and constantly builds on it’s previous calculated result as it works through the array.
Notes
- The ‘initial_value’ argument can be left blank as it is optional
- Providing an invalid LAMBDA function or an incorrect number of parameters will return a #VALUE! error
Author
Kyle Stott
Certified Microsoft Excel Expert
Kyle has worked professionally with Microsoft Excel for over a decade and has been consulting on and teaching best practices in Microsoft Excel to over 400 companies across 30+ countries.
Want to learn more?
Join My Excel Academy Today!
This All-Access Pass Includes:
- Access to All My Excel Academy Courses
- 20+ hours of on-demand video
- 1-on-1 instructor support
- Mobile learning supported
- Frequently updated content
- Learn at your own pace
- 500+ practice problems
- Certificate of completion
30-Day Money-Back Guarantee
Want to learn more?
Join My Excel Academy Today!
All-Access Pass Includes:
- Access to All My Excel Academy Courses
- 20+ hours of on-demand video
- 1-on-1 instructor support
- Mobile learning supported
- Frequently updated content
- Learn at your own pace
- 500+ practice problems
- Certificate of completion
30-Day Money-Back Guarantee
Want to learn more?
Join My Excel Academy Today!
30-Day Money-Back Guarantee
This all-access pass includes:
- Access to all My Excel Academy Courses
- 20+ hours of on-demand video
- 1-on-1 instructor support
- Mobile learning supported
- Frequently updated content
- Learn at your own pace
- 500+ practice problems
- Certificate of completion
Don’t hesitate – join now!
