Excel SCAN Function: Keep a Running Total

Photo of author
Written By Chris

Just a simple "data guy". Changing the world one formula at a time.

The SCAN function in Excel offers a powerful way to track running totals and perform specific calculations on data arrays. By combining LAMBDA with the SCAN function, you can create custom calculations that adapt as the function progresses through the array. 

This allows for effortless application of measures across a range of values, adjusting as needed without reliance on absolute or relative references. Whether you are new to Excel or a seasoned user, this potent blend of functions can be an excellent addition to your repertoire.

Understanding the SCAN Function

So, what exactly will the SCAN function do?

Let’s say we want to calculate the total interest accrued over a series of loan payments.

loan interest calculator

Obviously, we could sum the total of the calculated interest in the third column. But this simple example should help you grasp the concept of the SCAN function.

Let’s start by building a formula using =SCAN. This function has three required arguments: initial value, array to scan, and calculation to apply to the array. Since we’re working with numbers, we can begin by setting the initial value to zero. If you were scanning text strings, you’d use an empty pair of double quotes. 

Excel scan function syntax

Next, select the Interest field as your array and input a comma. Now, it’s time to define the calculation. 

To do this, use the LAMBDA function. As mentioned before, LAMBDA allows you to set a custom formula for Excel to apply with each new row.

Type LAMBDA followed by an opening parenthesis and set two parameters: A and B. A corresponds to the initial value, and B represents the first value in the array. Set the calculation as A plus B.

You can define the parameters however you want, but A and B are pretty standard.

Once you hit enter, the SCAN function will spill down the column. With each new row, the corresponding interest value (A) is added to the previously calculated total (B).

lambda function to create custom calculation in Excel

If you need to accommodate an initial value other than zero, simply change the initial value accordingly. For instance, in the example above, we could include previous interest payments from a balance transfer.

To do that, replace the first argument of zero with the total amount of prior interest as the initial value.

change initial value of scan function

In the image above, I adjusted the formula to account for the $500 already paid before the SCAN function begins to calculate.

Keep in mind that the SCAN function is currently available only in Excel for Microsoft 365 and Excel online. If you’re an experienced user and have discovered new and interesting applications for the SCAN function, please share them below. I’d love to hear about them!

Leave a Comment