Only one final total appears
SUM returns a total. SCAN returns every intermediate balance required by this task.
Calculate every running balance with SCAN
Read-only
Step-by-step guide · read-only ·
Loading your interactive spreadsheet…
Preparing the worksheet and formulas…
Data & tools
Task requirements
Enter one SCAN formula in G5 to return eight balances in G5:G12. Start with opening balance G2 and add C2:C9 in source order. Each output is the balance after that entry, not just the final total.
Follow the highlighted cells. Switch to Your turn to edit. Answer cells: G5:G12. Enter one formula in G5; keep the rest of the output area clear. Browser calculations may differ from your Excel version.
Selection & formula search
Practice stays on this device. No worksheet data is uploaded.
Notes & examples
About this lesson
Start from an explicit opening balance and return the state after each signed entry, in source order.
THE QUICK ANSWER01
Start from an explicit opening balance and return the state after each signed entry, in source order.
ƒx
=SCAN($G$2,C2:C9,LAMBDA(balance,change,balance+change))| Balance after entry |
|---|
| 140 |
| 115 |
| 115 |
| 35 |
| 55 |
| 60 |
| 50 |
| 100 |
The remaining reserved output cells are blank. Keep that area clear when trying the formula.
The formula uses the interactive worksheet above. Adjust ranges and criteria for your own data.
How it works
One opening balance
For the original source, 100 plus the first change of 40 gives 140. The next change of -25 gives 115. The zero third change preserves 115; it does not reset the balance.
Order changes intermediate results
The task compares all eight balances. Two sequences can end at the same balance and still describe different payment paths. Keep source order; this task does not sort by the entry label.
Numeric inputs and rounding
The opening balance and changes must be finite numeric values from -1,000,000 through 1,000,000. Negative balances and decimal changes are supported. No currency rounding policy is imposed; the original numeric values remain available in exports.
One formula, eight outputs
The starting value belongs in SCAN’s first argument. The last argument is a two-parameter LAMBDA. Enter the formula only at G5 and leave the remaining output cells clear.
Read the full step-by-step walkthrough
- Set the state before the first entry
G2 is the opening balance. It contributes once, before the first signed change.
- Read the entries in source order
SCAN visits C2 through C9 in that order. A receipt is positive, a payment negative, and zero leaves the balance unchanged.
- Define one update
The LAMBDA receives the previous balance and the current change. Add them to produce the next balance.
- Return each intermediate state
The eight-cell spill retains every update. SUM plus the opening balance would return only the last total.
- Check the path, not only its endpoint
The original path starts at 140, then 115, then stays 115 after a zero change. The final balance is 100. Reversing the entries changes earlier outputs even when the final total agrees.
Things to watch
The opening balance is added repeatedly
Pass G2 once as the initial value; do not add it inside the LAMBDA body.
A payment increases the balance
The source already stores a signed change. Add it directly instead of subtracting every entry.
About this example
The single anchor formula must spill into a clear output area. Local browser calculation and independently defined task rules are checked separately from native Excel/Google Sheets compatibility. Use a desktop Excel version supporting all named functions to recalculate a download; native editing/save/reopen is unverified.
Content version 1. Practice checks compare calculated results across different datasets; they do not certify every possible formula.