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?

To prevent the use of a double (or even a single) `DROP()` function, maybe start with the first 2 lines instead: `=REDUCE(A1:C2,ROW(A3:A3),LAMBDA(x,y,VSTACK(x,{"","",""},A1:C1,INDEX(A:C,y,))))` — JvdV, Jan 16 '23 at 12:38

Jos Woolley · Accepted Answer · 2023-01-16T09:24:38.523

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.

The end of your post is what I think happens here. I hoped it to perform the arguments of the array all at the same and result in dropping mentioned rows in the array, but it each array argument refers to the array. (Still funny how that range/array gives different behaviour). I guess repeating the DROP function is the only way. Great explanation, by the way! — P.b, Jan 16 '23 at 09:27
Yes, when I first saw this function I too assumed (hoped) that arrays passed as the second and third parameters would act cumulatively upon the array passed, but alas no. — Jos Woolley, Jan 16 '23 at 09:30

Excel DROP function unexpected behaviour

1 Answers1