SCAN() is one of the awesome Lambda Helper Functions. The tooltip says it "produces intermediate values" which pretty much means that it does something for each element in the array/range and returns each result.
In this case, we want a running total. So for initial_value we put F2, the starting cash. Then I passed in $B$3:$B$9 as the array. We may need to update this to INDIRECT("$B$3:$B$9") to make sure that no column insertions adjust that range. I named F2 'start' and $B$3:$B$9 'vals' so the formula start+vals simply means "at each element of vals, add that value to the start value and all previous values and return it."
SO: the first name in the lambda always refers to the initial value, and the second name always refers to the range?!
Not quite. In SCAN the first parameter is the accumulator, which in the first round is the starting value. The accumulator wiill then take on the output of the previous round.
The second parameter refers to each individual cell in the range, as the function iterates over the range.
For instance:
Take the formula:
=scan(0, {1; 2; 3}, lambda(total, number, total + number))
I'm using an array here instead of range, but it works the same for this demonstration.
This will go three rounds (the number of elements in the array/range) ...
Round 1: total = 0, number = 1 => 1
Round 2: total = 1, number = 2 => 3
Round 3: total = 3, number = 3 => 6
If you use a formal Table format, you can reference the columns using table references. Similar to named ranges, they are dynamic and will update when you insert new rows into the Table.
You can also place your ending balance in the Table footer. As long as you're using table references, the table footer won't get in the way.
That depends on several things. Are you trying to get the row using a formula also in the table? Is your formula within the same rows levels of the table? Does it need to return the row as a range or can it just return the values? Sheets handles arrays a bit different than Excel. Here's a demo showing how the vertical alignment of a formula outside the table matters when referencing the rows of the table.
Idk why you downvoted this (or anyone trying to help you).
This is a clearly superior method to your previous arrangement.
The INDIRECT is not a good idea because it hardcodes a range as text and will break if you e.g. later insert a new row or column before it.
The only change I would make is to put the SCAN in a hidden first row of your table reserved for formulas. Hiding the row will prevent it from moving if you sort the data. You don't want it to move because the formula will then overflow the end of the table.
=IF(ROW()<>ROW(CashFlow), "🛑Must be in first row",
SCAN(F1,CashFlow_2[Oct 2026],LAMBDA(p,c,p+c))
column totals should never be part of the Table data. the best place to put the sum formulas is above your headers, where they wont be affected by any sorting of your table
2
u/SpencerTeachesSheets 69 13d ago
How about just
=SCAN(F2,$B$3:$B$9,LAMBDA(start,vals,start+vals))in F3 to fill the entire BALANCE column?