Forum Discussion
Using Scan function with Oddly shaped arrays
I have two arrays of different shares. One array is a set of values. The other is a set of growth rates. The challenge is to apply the scan function to get the an array of values that are increased...
The are at least two problems here. The first is how to apply SCAN to multiple rows as an array operation. My solution is to reference the rates array column by column using thunks. This means the results will be returned as a row of thunks which need to be evaluated and stacked (I have written a utility function EVALTHUNKARRλ for that).
The other problem is how to deal with ragged array problems. In this case I was able to duck the problem because interpreting blanks as escalate by 0% gives the required numerical values. Masking the unwanted values provides the result.
= LET(
PRODUCTλ, LAMBDA(accϑ,fϑ, THUNK(accϑ() * fϑ())),
valuesϑ, THUNK(values),
factorsϑ, BYCOL(1 + rate, THUNK),
resultϑ, SCAN(valuesϑ, factorsϑ, PRODUCTλ),
result, EVALTHUNKARRλ(resultϑ),
IF(ISNUMBER(rate), result, "")
)Even as it is, following it is not going to be straightforward!
A version of Excel MAP helper function that will return an array of arrays