ByRow function produces two different results depending on whether row is range or array

[Viz]

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 that range in ByRow function, it works great. But when I use that sequence function directly inside a Let(), it gives me wrong answers.

I am sharing the codes I have tried, below:

This is the first formula I wrote:

=LET(
    Values, SORTBY(Data[Sensex], Data[DATE]),
    Sqn, SEQUENCE(ROWS(Values)),
    BYROW(
        Sqn,
        LAMBDA(a,
            IF(
                a < 7,
                "NA",
                AVERAGE(
                    INDEX(Values, SEQUENCE(7, 1, a - 7 + 1))
                )
            )
        )
    )
)​

This one produced "a" set of answers. But the answers were slightly incorrect.  To figure out where the problem was, all I did was put the sequences in a separate range and use that range in the ByRow function and this time I got perfect results:

So, this is the first part to get the sequence of numbers

In cell, say E4

=LET(
    Values, SORTBY(Data[Sensex], Data[DATE]),
    Sqn, SEQUENCE(ROWS(Values)),
    Sqn
)

And this is the second part, where I use the ByRow function and specify the range as an array

In cell, say F4

=LET(
    Values, SORTBY(Data[S&P BSE Sensex], Data[DATE]),
    BYROW(
        E4#,
        LAMBDA(a,
            IF(
                a < 7,
                "NA",
                AVERAGE(
                    INDEX(Values, SEQUENCE(7, 1, a - 7 + 1))
                )
            )
        )
    )
)

And now I am getting the perfect answer. Is this a bug or am I doing something wrong here:

   @Peter Bartholomew @Craig Hatmaker  @Sergei Baklan @lori_m, 

[#2]

@mtarler - 

a) you are totally right, my apologies

b) yes!

c) works well, thank you so much!

[#3]

@mtarler 

Hi Matt

Going straight for VSTACK would avoid the fill of #N/As.

= DROP(
    REDUCE(0, seq#,
        LAMBDA(p, q, VSTACK(p, SEQUENCE(q, 1, q, 0)))
    ),
  1 )

In a properly specified system

= MAP(seq#, LAMBDA(q, SEQUENCE(q, 1, q, 0)))

would work.  The 'Nested arrays are not supported' #CALC! error is just plain irritating since the overwhelming majority of solutions I work of have arrays of arrays as their solution.  Mind you my requirements have grown since I first encountered the 'array of array failure'.  I no longer would want to settle for a row of column vectors, packed into a 2D array.  Now I want arrays of 2D arrays, just as one could return with H/VSTACK!

[#4]

Thanks! I actually used the VSTACK on my own intuition!
QQ - when you say "properly specified system" - how do you mean? Thanks!

[#5]

@tdarenkov 

In the traditional spreadsheet, it is 'all about the cell'.  To process arrays, one treats each term as a scalar and writes an array of formulas, each of which processes a single value.  The fact that nested arrays were not supported was not a problem because little use was made of arrays.

Now, in Excel 365, it is the array that is the basic building block of practically all solutions.  The limitation of nested array not being supported was retained to maintain backward compatibility.  The problem is that the rule is fundamentally incompatible with array working.  Any 2D array can be regarded as a column (array) of rows (arrays).  TOROW will convert a 2D array to the equivalent 1D nested array and WRAPROWS reverses the process.  I believe that to specify Lambda helper functions so that they will not support these nested arrays is a monumental error; they are a fundamental part of spreadsheet calculation.

It is rather like specifying a cup, and then adding 'hot liquids are not supported'.  'About as much use as a chocolate teapot' comes to mind.  No matter how skilled the chocolatier is at his craft, the result is not going to be good!

[#6]

@Peter Bartholomew 

IMHO, that's all about auto-lifting. Every cell in Excel (as well as every range) is actually 2D array, After that auto-lifting works to convert into the single value. Or doesn't work, depends on situation. With ranges is more complex, and with array of arrays doesn't work at all.

ExcelScript gives more clear and straightforward picture having practically no assumptions in converting arrays.