SCAN function
SCAN(initial_value, array_or_range, LAMBDA)
The SCAN function scans an array and produces intermediate values by application of a LAMBDA function to each value. Returns an array of the intermediate values obtained at each step.
Formula
=LET(cap,TOCOL(B2:B,1), yst,QUERY(cap, "LIMIT "&ROWS(cap)-1,0), td,QUERY(cap, "OFFSET 1",0), SCAN(1, INDEX(td/yst), LAMBDA(a,v, a*v)))
Explanation
cap stores the Capital column after removing any blank rows.yst stores yesterday's capital. Basically offset minus one row from today's value.td stores today's capital. Basically offset plus one row from yesterday's.- SCAN has its accumulator
a starting at 1, and each individual value v is created by dividing that day's capital by yesterday's. The array of those values is created by dividing al the values at once td/yst. - Scan passes the current value of
a and the next v into a LAMBDA which multiplies them together, returns the result but also stores the new result in a for the next iteration.
For example: # Example Formula =SCAN(1, {1.01, 1.02, 1.03}, LAMBDA(a, v, a*v)) # Iterations Pass 1. a=1, v=1.01, a*v=1.01 Pass 2. a=1.01, v=1.02, a*v=1.0302 Pass 3. a=1.0302, v=1.03, a*v=1.061106
