Forum Discussion
Excel Scan versus Reduce LAMBDA Issue
=REDUCE({1}, {2, 3, 4}, LAMBDA(acc, cv, VSTACK(acc, cv)))
Works fine in Excel and Google Sheets.
=SCAN({1}, {2, 3, 4}, LAMBDA(acc, cv, VSTACK(acc, cv)))
generates and Error in Excel ( indicates nested arrays are not supported), but works as expected in Google Sheets.
Is this an Excel BUG, or a Feature?
Thanks in advance.
I don't know but I think they made the conscious decision not to support array of arrays in these functions because of all the potential ways things can go wrong. In your example and many examples there is a clear and correct output:
1 1 1 2 2 2 3 3 4
but if you try this:
=REDUCE(1,hstack( {2;3}, {4;5} ),LAMBDA(acc,cv,VSTACK(acc,cv)))
or in sheets you can go 'more directly' as
=REDUCE(1,{ {2;3}, {4;5} },LAMBDA(acc,cv,VSTACK(acc,cv)))
both result in
1
2
4
3
5
which at least is not the obvious output to me. and neither support SCAN in this case while I would have liked to see1 1 2 2 3 3 4 5 - SergeiBaklan , mtarler - Thanks for your responses.
I do realize that Excel LAMBDA does not support Array of Arrarys, - but I was not trying to do Array or Arrays.
The code works fine when using REDUCE, so I don't think its an Array or Arrays issue.
I often use SCAN to examine the intermediate steps of my REDUCE functions and that is what I was doing here. SCAN and REDUCE should, I would think, be identical in the final output.
My REDUCE function worked fine, so I did not need a workaround.
I will report this as a bug.
Thanks for the input.With scan you do array of array. On first step you make array {1;2}, on second step another array {1;2;3} and on on third step one more array {1;2;3;4}. Thus you have 3 arrays which shall be returned as one combined array, i.e. array of array. That doesn't work directly.
With REDUCE on first step you add value to initial array, i.e. generate {1;2}, on each next step you only expand it by another value. Finally you have only one array {1;2;3;4}, single array is returned without any problems.
Hard to say is that a bug or missed feature, depends on your attitude. Excel doesn't support array of arrays since its calc engine was designed so long before dynamic arrays are introduced.
Different kind of workarounds exist, but no native support.
The behavior you have described is not a bug, but rather a difference in the implementation of array functions between Excel and Google Sheets.
In Excel, the REDUCE function is designed to work with arrays and perform operations like aggregation, whereas the SCAN function is not a built-in Excel function, so when you use it, Excel may not recognize it or its behavior may differ.
In Google Sheets, it appears that the SCAN function is implemented and designed to handle scanning through arrays with the provided lambda function.
So, when you use =REDUCE({1}, {2, 3, 4}, LAMBDA(acc, cv, VSTACK(acc, cv))) in Excel, it correctly uses the REDUCE function and works as expected.
However, when you use =SCAN({1}, {2, 3, 4}, LAMBDA(acc, cv, VSTACK(acc, cv))) in Excel, Excel doesn't recognize the SCAN function as a built-in function, and it appears to treat it as an array operation, leading to an error. In Google Sheets, the SCAN function seems to be recognized and works as expected.
So, it's not a bug but rather a difference in feature support between the two spreadsheet applications. If you need to use this specific behavior, you should use REDUCE in Excel and SCAN in Google Sheets, recognizing that they may not be interchangeable. The text was created with the help of AI.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and like it!
This will help all forum participants.
Hi NikolinoDE
I fully agree with you that there appears to be a difference in implementation between the SCAN and REDUCE.
That said, SCAN is intended to show the intermediate steps associated with a REDUCE function. I use SCAN to debug REDUCE functions.
SCAN and REDUCE should give the same end-result, given the same inputs.
How SCAN and REDUCE are implemented internally should not be a FACTOR.
My opinion after reading yours, SergeiBaklan, and mtarler responses is that it is a BUG, but of course, I could be wrong.
