Forum Discussion
ByRow function produces two different results depending on whether row is range or array
In a nutshell, I am trying to compute average with a combination of ByRow, Index, and Average. I needed a sequence number array for this algorithm. When I put this sequence number in a range and use ...
as always thank Sergei, I seem to always learn something new from you. That said, can you explain:
a) why does it work with a range reference instead of an array? When it's a range, why is the "array of one" treated differently?
As for what is actually happening here is a sample to help:
=AVERAGE(SEQUENCE(5,1,2+{5})) vs =AVERAGE(SEQUENCE(5,1,2+5))
So =AVERAGE(SEQUENCE(5,1,2+5)) eval to =AVERAGE({7;8;9;10;11}) and then =9
BUT =AVERAGE(SEQUENCE(5,1,2+{5})) eval to =7
obviously the {5} is messing it up even though if you use F9 on the SEQUENCE(5,1,2+{5}) you still get {7;8;9;10;11} of which the AVERAGE() should be 9 but my guess is that internally it is treating it as {7};{8};{9};{10};{11} and then AVERAGE(7) ; AVERAGE(8) ; ... and then returning only the first of those results being AVERAGE(7) = 7, but that is more guess than anything
a) why does it work with a range reference instead of an array? When it's a range, why is the "array of one" treated differently?
As for what is actually happening here is a sample to help:
=AVERAGE(SEQUENCE(5,1,2+{5})) vs =AVERAGE(SEQUENCE(5,1,2+5))
So =AVERAGE(SEQUENCE(5,1,2+5)) eval to =AVERAGE({7;8;9;10;11}) and then =9
BUT =AVERAGE(SEQUENCE(5,1,2+{5})) eval to =7
obviously the {5} is messing it up even though if you use F9 on the SEQUENCE(5,1,2+{5}) you still get {7;8;9;10;11} of which the AVERAGE() should be 9 but my guess is that internally it is treating it as {7};{8};{9};{10};{11} and then AVERAGE(7) ; AVERAGE(8) ; ... and then returning only the first of those results being AVERAGE(7) = 7, but that is more guess than anything
That's better to ask JoeMcDaid for more professional explanation.
My guess if we have BYROW on spill, aka array, it takes each element of such array which is value. Each element of the range is an array, auto-lifting doesn't work in such case.
As for the SEQUENCE not sure how it works. With an array in step it looks like it returns only the value for the first step.