ROW function returning array (though it should not) when used with SUMPRODUCT and OFFSET.

Aren't they returning arrays of 1 character?  {2} for example.  This happens because if you text ROW(2:4) you will get {2,3,4} and if ROW() returned 2 there would be an inconsistancy, sometimes the function would return an array at other times a number.

I don't think this is a mistake.  And I don't think it occurs because the function is inside another function.

Both the ROW and COLUMN functions return arrays even if it's a one element array.

Some functions will not accept the array passed by ROW and/or COLUMN. One way to get around it is to wrap another function around ROW and/or COLUMN that will convert the array to a scalar. Something like SUM, MAX, MIN. For example:

If the formula was entered in cell A1 then ROW() returns the single element array {1}. If the function that is using that result won't accept the array {1} then you can convert it to a scalar like this:

SUM(ROW()) converts {1} to 1

MAX(ROW()) converts {1} to 1

MIN(ROW()) converts {1} to 1

--
Biff
Microsoft Excel MVP

I get a #VALUE error in the following formula:

=SUMPRODUCT(OFFSET(AE$1,1,0,ROW()-ROW($2:$2),1),OFFSET($A$1,1,MATCH("TOTAL_SITE_AREA",$1:$1,0)-1,9,1))

 

When I step through it using the Evaluate Function button, I see that the ROW functions are returning arrays, when they should only be returning numbers.  If I replace "ROW()-ROW($2:$2)" with the value I'm expecting, the formula works correctly.  If I put "ROW()-ROW($2:$2)" in a cell by itself, I also get the value I'm expecting.  Does anyone know why this is happening?  I've also seen the same behavior with the COLUMN function when used in a similar manner.

 

I am wondering if Excel is mistakenly converting the ROW function to return an array when inside a SUMPRODUCT, even though it is really inside the OFFSET function (which should be the only function that returns an array here).

 

I get a #VALUE error in the following formula:

=SUMPRODUCT(OFFSET(AE$1,1,0,ROW()-ROW($2:$2),1),
OFFSET($A$1,1,MATCH("TOTAL_SITE_AREA",$1:$1,0)-1,9,1))

[....] I am wondering if Excel is mistakenly converting the ROW function to return an array when inside a SUMPRODUCT, even though it is really inside the OFFSET function (which should be the only function that returns an array here).

Doesn't SUMPRODUCT require all arrays passed to it be the same dimension?  In this case, the 2nd array is 9 rows x 1 column but the 1st is (ROW()-2) rows x 1 column.

Yes.  And you are correct that that would be a source of the #VALUE error in most rows.

However, even the following returns a #VALUE error (in Excel 2003) when it is placed in A11:

=SUMPRODUCT(OFFSET(B1,1,0,ROW()-2,1),OFFSET(B1,1,0,9,1))

(Note:  I am not trying to reproduce the David's functionality.  I am only trying to demonstrate the semantic problem with ROW() in this context by using similar, but simplified syntax.)

When ROW() is replaced with 11, SUMPRODUCT works insofar as it does not return the #VALUE error.

Using the Tools > Auditing > Evaluate Formula (or F9 in the Formula Bar), ROW() evaluates to the array(!) {11}, and ROW()-2 evaluates to the array(!) {9}.  That is the source of the #VALUE error.

In contrast, the following formula works insofar as it does not return the #VALUE error:

=SUM(OFFSET(B1,1,0,ROW()-2,1))

Again, using Evaluate Formula, it appears that ROW() and ROW()-2 evaluate to the non-array values 11 and 9.

So it does seem that the misbehavior is peculiar to the syntax ROW()-2 in the context of SUMPRODUCT, as David thought.

(Note:  However, in the SUM(OFFSET) example, F9 in the Formula Bar shows that ROW() and ROW()-2 evaluate to the arrays(!) {11} and {9}, contrary to what Evaluate Formula shows.  Since SUM(OFFSET) works, I don't know if we can trust F9 and Evaluate Formula to correctly represent the evaluation steps.)

Another alternative to  ROW()-ROW($2:$2)  would be  ROWS($3:3)  which returns a non-array value.