Excel SCAN

Running totals, compound stuff, and that one weird trick with arrays.

So What’s SCAN, Anyway?

Okay, imagine you have a list of numbers. You want to add them up, but you don’t just want the final total — you want to see the running total after every step. Like, “after the first number, after the second, after the third…” That’s what SCAN does. It scans through an array, does something with each value, and gives you back all the intermediate results.

The Bare Bones Structure

Three pieces:

1. initial_value — the starting point (like 0 for a sum, or 1 for a product).
2. array — the list of numbers (or text, or whatever) you’re scanning.
3. lambda — the operation you’re doing each step. It takes two arguments: the accumulator (what you’ve built so far) and the current value.

The LAMBDA inside is what does the work. It’s like a little machine that takes in the running total and the next item, and spits out the new running total. SCAN just runs that machine over and over and collects every output.

Easy Baby Steps

Example 1: Running Total (Like a Cash Register)

You’ve got daily sales numbers. You want to see the cumulative total after each day. This is the classic SCAN move.

The formula:

If A1:A5 has {10, 20, 30, 40, 50}, SCAN gives you {10, 30, 60, 100, 150}.

Start at 0. Add 10 → 10. Add 20 → 30. Add 30 → 60. You get the idea. It’s like a running tally on a whiteboard.

Example 2: Cumulative Product (Money Doubling)

You’re doing that thing where you double a penny every day. Show the amount after each day.

The formula:

If B1:B5 is all 2s, you get {2, 4, 8, 16, 32}. Day 1: $0.02, Day 2: $0.04, etc. Start with 1 because multiplying by 1 doesn’t change anything.

Example 3: Running Max (Did We Break a Record?)

You’ve got a list of daily temperatures. You want to see the highest temperature so far after each day.

The formula:

If temps are {15, 22, 18, 27, 20}, SCAN gives {15, 22, 22, 27, 27}. Each step, it keeps whichever is bigger: the previous record or today’s temp. Start with a super small number so the first temp always wins.

Medium Okay, We’re Cooking

Example 1: Running Total with a Twist — Only Add Positive Numbers

You’re tracking your bank account, but you only care about deposits. Withdrawals (negative numbers) should reset the running total to 0? No, that’s not right. You want to ignore them. So the running total only goes up when you see a positive number.

The formula:

If D1:D5 is {10, -5, 20, -10, 30}, you get {10, 10, 30, 30, 60}. The negative numbers just leave the accumulator alone. This is where LAMBDA inside SCAN starts to shine — you can put any logic you want in there.

Example 2: Compound Interest — Step by Step

You’ve got a starting balance, an annual interest rate, and a list of monthly deposits. You want to see the balance after each month. This is basically a mini-banking simulator.

The formula:

Start with $1000. Each month, earn 0.5% interest (that’s 6% annual / 12) and then add the deposit for that month. If deposits are all $100, you see the balance grow month by month. SCAN gives you the whole timeline in one spill.

Example 3: Running Standard Deviation (Okay, this is a bit nerdy)

You’ve got a list of test scores. You want to see how the standard deviation changes as you add more students. This is more of a “look what I can do” example, but it shows you can do complex math inside the LAMBDA.

The formula:

No, no — SCAN doesn’t have a way to “see” the whole array inside the LAMBDA (well, it does, but it’s tricky). Let’s do something simpler: running average. That’s actually useful.

Running Average:

Okay, you got me. SCAN isn’t great for rolling averages because you need the whole array context. But you can do a cumulative average: keep track of the count and the sum, then divide each step.

Nope, that’s just counting. Let’s just do a running sum with a count, then divide. But I’m overcomplicating this. The real power of SCAN is in the next section.

Hard Advanced Moves

Example 1: Running Balance with Conditional Resets

You’re tracking a budget. Every time you hit a certain threshold, you “reset” the running total to 0 and start over. This is like a rolling budget where you allocate $100 per week, and any leftover rolls over, but if you exceed $100, the excess is “spent” and resets.

The formula:

If H1:H10 is {50, 60, -10, 30, 40, 20, -5, 100, -50, 10}, you’ll see it add up until it crosses 100, then reset to 0. This is the kind of thing that would be a nightmare with regular formulas, but SCAN handles it in one line.

Example 2: Custom Running Count — Count Consecutive Wins

You’ve got a list of game results: “W” or “L”. You want to track the current winning streak. Each “W” increases the streak by 1, each “L” resets it to 0.

The formula:

If I1:I10 is {"W", "W", "L", "W", "W", "W", "L", "L", "W", "W"}, you get {1, 2, 0, 1, 2, 3, 0, 0, 1, 2}. This is perfect for tracking streaks in sports, sales, or anything binary.

Example 3: SCAN with Text — Build a Sentence Word by Word

You’ve got a list of words. You want to build a sentence by adding one word at a time, showing the partial sentence after each step.

The formula:

If J1:J5 is {"SCAN", "is", "a", "cool", "function"}, you get {" SCAN", " SCAN is", " SCAN is a", " SCAN is a cool", " SCAN is a cool function"}. You can use this to build dynamic labels or just show how a string grows.

Notice the leading space in the first result? That’s because we started with an empty string and added a space before each word. You could tweak it with TRIM, but you get the idea.

Stuff You Should Know

• SCAN spills. It returns an array that’s the same size as your input array. If you put it in a cell, it’ll spill down (or across) automatically. Make sure you have enough empty cells.
• The LAMBDA is the engine. You can put ANY formula inside that LAMBDA. IF, VLOOKUP, XLOOKUP, even other SCANs (though that gets wild).
• Initial value matters. For sums, start with 0. For products, start with 1. For text, start with “”. For max, start with a really small number. For min, start with a really big number.
• You can use SCAN with arrays from other functions. Like, =SCAN(0, SORT(A1:A10), LAMBDA(a,v, a+v)) sorts the array first, then does a running total. Mind blown.

Your Turn — Play With It

Fire up Excel and try these:

1. Running total of expenses. List some purchases and see the cumulative spend.
2. Cumulative discount. You’ve got a list of discount percentages (like 10%, 20%, 15%). Apply them sequentially to a starting price and see the price after each discount.
3. Consecutive days of rain. You’ve got “R” and “S” for rain/sun. Track the current rain streak. Reset on sun.
4. Build an email address. You’ve got first name, last name, domain. Use SCAN to concatenate them with dots and @s.

SCAN is one of those functions that you don’t think you need until you see it in action. Then you’ll find a hundred uses for it. It’s like a Swiss Army knife for running calculations.

Now go scan something. You’ve got this.