ROWS does not spill?

[BrianGGG]

Hi. Anyone have any idea why...

This array formula "spills", or creates 3 values:

$A$1:(A1:A3)

However, wrapping it in ROWS does not spill (or creates a simple value of 3):

ROWS($A$1:(A1:A3))

 

[KevCarter]

Hi Brian,

Does this work for you?

=SEQUENCE(ROWS(A1:A3))

I love the new array structure!

 

[joeu2004]

@BrianGGG.... A1:(A1:A3) and (A1:A3):A3 are simply the range A1:A3. If we array-enter a formula with A1:A3, we get 3 separate values.

But the ROWS() parameter is a range. If we enter ROWS(A1:A3), we get 3 because ROWS() "consumes" the entire range. If we array-enter a formula with ROWS(A1:A3), we get an array of 3s, the equivalent of an array of the formulas ROWS(A1:A3), ROWS(A1:A3), ROWS(A1:A3).

The same issue applies to AND() and OR() functions. It is the reason why we cannot use them as we might intend in an array-entered formula.

For example, we might array-enter =IF(AND(A1:A3=B1:B3),C1:C3) with the intent for that to be treated like an array of the formulas IF(A1=B1,C1), IF(A2=B2,C2), IF(A3=B3,C3). But instead, it is treated as IF(TRUE,C1:C3) or IF(FALSE,C1:C3) because AND(A1:A3=B1:B3) returns a single logical value, the equivalent of AND(A1=B1,A2=B2,A3=B3).

Bottom line: functions that expect array and range parameters cannot be used in array-entered formula with the intent of creating an array of unique values.

 

[joeu2004]

PS.... When I want an array of the numbers 1 to n, I use ROW(A1:A3) or ROW(A1:INDEX(A:A,X1)). ROW, not ROWS. I use the first form when I know "n" (3, in this example). I use the second form when "n" is a variable (the value of X1, in this example). I purposely avoid using INDIRECT because that is a "volatile" function. It causes the formula that references it and all directly or indirectly dependent formulas to be recalculated any time any cell in the workbook is recalculated.

 

[BrianGGG]

Thanks @joeu2004 and @KevCarter

I think it's a great analogy that ROWS and other functions "consume" or "swallow" the arrays into a single value.
I can work around it with the examples above.

I have a similar problem with SUM that I'll post separately ...

 

[Aladin Akyurek]

Thanks @joeu2004 and @KevCarter

I think it's a great analogy that ROWS and other functions "consume" or "swallow" the arrays into a single value.
I can work around it with the examples above.

I have a similar problem with SUM that I'll post separately ...

ROWS, SUM, AVERAGE, MAX, etc. applied to a reference delivers a single summary score (a scalar value).