Let me show you what I want to achieve:
I start with a range of figures and I want to see how the range is built horizontally, one figure at a time.
I am able to get the last line of the result by using a REDUCE function. Until here, everything is fine.
However, if I want to see how the REDUCE function has built its result, line by line, and I try it by replacing REDUCE with SCAN, I simply get a #CALC! error. I simply do not understand it.
In my way of understanding these two functions, if one works, so should it the other.
Any suggestions?
>Solution :
With the exception of REDUCE, the LAMBDA helper functions in Excel cannot return arrays per iteration. If you want to see the intermediate steps, you can still do it using REDUCE by vertically stacking the accumulator with VSTACK and replacing every occurrence of the accumulator a with TAKE(a;-1):
=DROP(REDUCE(0;B4:B6;LAMBDA(a;b;VSTACK(a;HSTACK(TAKE(a;-1);b))));1;1)
