Excel DROP function unexpected behaviour

Question

When getting a formula solution for this question Insert row to separates group of data with header I tried dropping the first and last row from a spill result.

This was the data used:

	column A	column B	column C
row 1	pos	item	qty
row 2	p1	hat	2
row 3	p3	cap	3

I started off with

=DROP( 
      REDUCE(0,ROW(A2:C3),LAMBDA(x,y,VSTACK(x,A1:C1,INDEX(A:C,y,),{"","",""}))),
      1)

This gave me the following spill:

I then wanted to not only drop the start value of the REDUCE function, but also the last line, which would always be blanks.

For that I tried:

=DROP( 
      REDUCE(0,ROW(A2:C3),LAMBDA(x,y,VSTACK(x,A1:C1,INDEX(A:C,y,),{"","",""}))),. 
      {1,-1})

I expected the {1,-1} array to pull it off (1 for it's first row and -1 for the last row). This however resulted in:

I couldn't understand this behaviour, so I tried it on a simpler range and or array:

In A1 I used =SEQUENCE(3,3)

And I used =DROP(A1#,{1,-1}) which resulted in:

While doing the same directly: =DROP(SEQUENCE(3,3),{1,-1}), gave another result:

And a bit funny: =DROP(SEQUENCE(3,3),{1,1,1,1,1}) Results in {4,4,4,4,4}

I know I can use DROP twice to het the desired result, but I can't explain this behaviour.

Is this because the first argument of the array alters the size of the array/range and Excel can't reference that from within the same calculation?

Answer 1

Another example of the new array functions exhibiting different behaviour when passed an array or a range.

It appears that, in certain constructions, these functions behave like older functions such as OFFSET, in that, when passed a worksheet range, that range may need to first be 'dereferenced' to an array.

For example, with A1 containing =SEQUENCE(3,3), as in your example, =DROP(A1#,{-1,1}) returns {#VALUE!,#VALUE!}.

However, 'dereferencing' that range with, for example, N, i.e. =DROP(N(+A1#),{-1,1}) produces {1,4}, an identical result to =DROP(SEQUENCE(3,3),{1,-1}).

As to the result of =DROP(SEQUENCE(3,3),{1,-1}), I don't see how you could expect this to return {4,5,6}, since you are effectively asking it to simultaneously perform =DROP(SEQUENCE(3,3),1), i.e. {4,5,6;7,8,9} and =DROP(SEQUENCE(3,3),-1), i.e. {1,2,3;4,5,6}, which would require a third dimension, though Excel has never been capable of storing internal arrays of more than two dimensions. DROP's second and third parameters are not designed to be used cumulatively when passed an array, if that was your intention.