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...
A less technical/more naive solution (i.e., one that works specifically for this situation) can be for you to use SCAN with the ISOMITTED function in the LAMBDA to handle the blank areas of the array, so that they function as a 0% increase.
=SCAN(value,rates,LAMBDA(val,rate,IF(ISOMITTED(rate),val,val*(1+rate))))To mask the unchanged values, if you always have the "upper triangular" form, then you can use MAP on the above array: return "-" if the result is equal to the original value.
=MAP(SCAN(value,rates,LAMBDA(val,rate,IF(ISOMITTED(rate),val,val*(1+rate)))),LAMBDA(result,IF(result=value,"-",result)))Forgive me, I don't write technically for others often, so I hope you can interpret the above with my explanation without using LET to put things step-by-step.
Thanks!! I tried the first formula and got a #CALC!. I probably didn't do it right, though. Any suggestions?
My attempt is attached.