Search code examples
excelexcel-formulaexcel-365spill-range

Excel DROP function unexpected behaviour

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: enter image description here

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:

enter image description here

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: enter image description here

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

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?

Solution

  • 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.