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...
My solution utilizes SCAN within MAKEARRAY (the black sheep of Lambda helper functions!).
=LET(
h, ROWS(array2),
w, COLUMNS(array2),
UpperPyrScan, LAMBDA(i, j,
LET(
upper_pyr, j >= i,
initial, INDEX(array1, i),
arr, CHOOSEROWS(array2, i),
acc, SCAN(initial, arr, LAMBDA(a, v, a * (1 + v))),
IF(upper_pyr, INDEX(acc, , j), 0)
)
),
MAKEARRAY(h, w, UpperPyrScan)
)
Hi Patrick
You got away without thunks which may well the cause of fear and distress even for advanced Excel users!
I think the downside may be that your black sheep revisits the SCAN feed for each column j evaluation?
I am tending to use thunks ever more as the means to pass references to arrays of arrays [or even arrays of arrays of arrays ...!]
Yes, indeed, SCAN is being run quite often with my approach. I had considered using a mesh grid (to borrow a Python term) of i-j coordinates and going at it without MAKEARRAY, but the result wouldn't be much different. I think other ways to solve this would be with a recursive SCAN but if the results are to be stacked, it would not be all that different than a typical REDUCE/VSTACK combo, in my opinion.
In terms of performance, I wonder how often data_junky encounters these upper triangular arrangements and how large the data sets are? If the data is mostly rows that have been summarized (not in the 10s of thousands of rows), then maybe the best solution is the one that makes the most sense to the author of the workbook.
