Answering my own question many years later; the previous answers are very good, but now it's best to do this:
=SCAN(
"", A1:A10,
LAMBDA(acc,x,
IF(x="", acc, x)
)
)
SCAN
iterates through a 1-dimensional array, at each step keeping track of the previous result as returned by the lambda (or exceptionally, during the very first step, the previous result is considered to be the ""
parameter we specified). SCAN
, considering the previous element acc
and the new element x
, then chooses what it will yield as the next "previous element". We specify the logic we want with an IF
statement: continue to yield our remembered variable acc
if the first column is blank; however, if the first column is something new, forget the old acc
and start returning a new value to give to the new acc
.
tl;dr: Basically the lambda directly returns the value we want (column B), as a function of the previous value (acc
= one higher in column B) and the current input (x
= corresponding cell in column A)
The functional programming functions like MAP
and REDUCE
and MAKEARRAY
and BYROW
and BYCOLUMN
and SCAN
are incredibly powerful.
You can use SCAN
to do much more complicated things. In particular, you could use this as a construct for general iteration with state, but you cannot pass arrays through acc
, only singleton values, so you'd have to serialize and deserialize with JOIN
and SPLIT
and only work with string datatypes*
*(as a general sketch, you could return a row from each lambda (disallowed, so you return the row as a string and unpack it, with the help of named functions, then repack it, to simulate variables like myVar1=INDEX(row,2)
or myVar2=INDEX(row,3)
etc.), and finally you take INDEX({row0;row1;row2;...;rowN},,1)
where {...}
is the result of SCAN
, with the convention that the leftmost elements of the row are the ones you 'print').(actually as of today, it seems returning rows is now supported? see addendum for a sketch of some more advanced functionality)
Lambdas seem to be a bit buggy or not generally powerful enough to do iteration with recursion e.g. Y-combinator.
You could use Apps Script for all this if you need something more complicated, but then it wouldn't be "instant speed" and might buggily freeze forever with no workaround because of a AppsScript server hiccup, and then you're stuck.
Addendum:
You could used Named Functions to shoe-horn in your own imperative programming, with continuation-passing style. This gives you the power to sanely do more complicated things without resorting to 'tricks'.

=ITERATE(
{
".arrIndex",0,
".subIndex",0,
".curTotal",0,
".subTotal",0,
".curCount",0
},
A2:A13,
LAMBDA(s, i,x, get,set,yield,
IF( x<>"",
set(s,".arrIndex", get(s,".arrIndex")+IF(get(s,".subIndex")=0, 1,0), "",LAMBDA(s,
set(s,".subIndex", get(s,".subIndex")+1, "",LAMBDA(s,
set(s,".curTotal", get(s,".curTotal")+x, "",LAMBDA(s,
set(s,".subTotal", get(s,".subTotal")+x, "",LAMBDA(s,
set(s,".curCount", get(s,".curCount")+1, "",LAMBDA(s,
set(s,".curAvg", IFERROR(get(s,".curTotal")/get(s,".curCount")), "",LAMBDA(s,
yield(s, i&": at arr["&get(s,".arrIndex")&"]="&"x"&" subarr["&get(s,".subIndex")&"] we yielded "&GET(s,".curAvg") )
)) )) )) )) )) )),
set(s,".subIndex", 0, "",LAMBDA(s,
set(s,".subTotal", 0, "",LAMBDA(s,
yield(s, "")
)) ))
)
)
)
The above code requires we defined a Named Function ITERATE
to be something as shown below. It's reusable though. This is what I meant by using SCAN
to "thread state" between rows in an iterative manner.
LAMBDA(seed,arr,f,
i.e. name="ITERATE", param1="seed", param2="arr", param3="f"
with function body
LAMBDA(get,set,
LAMBDA(yield,
SCAN({"yield","", "i",-1, seed},arr,
LAMBDA(acc,x,
set(acc,"i", get(acc,"i")+1, "",LAMBDA(s,
f(
s, get(s,"i"), x,
get,set,yield
)
))
)
)
)(
LAMBDA(state,retVal,
set(state,"yield", retVal, "",LAMBDA(x,x))
)
)
)(
LAMBDA(state,varname,
INDEX(state, XMATCH(varname,state)+1)
),
LAMBDA(state,varname,newVal, comment,k,
k(
IF( COUNTIF(state,varname)=0,
{state, varname,newVal},
SCAN("",state, LAMBDA(last,oldVal,
IF(last=varname,
newVal,
oldVal
)
))
)
)
)
)
end of last paren; ignore if you are defining a Named Function
)
You could elaborate on the above so you have an easier time yielding a YIELD(s, {"row","of","values"})
rather than a singleton, but the above is a proof of concept.