David Manowitz wrote:
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).
Tushar Mehta wrote:
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.)