Row()/Column() Function Does not Work with CoVar() and Offset()

[Guest]

Hi,

I encountered an odd problem recently. It looks like the ROW/COLUMN
functions do not work with the OFFSET and COVAR function.

For example, if I write the following fomula in cell G2, it gives me #NUM!.

=COVAR(OFFSET($B$2,0,COLUMN(G2)-COLUMN($G$2),4),OFFSET($B$2,0,ROW(G2)-ROW($G$2),4))

B25 has numbers filled in. However, if I evaluate the ROW and COLUMN
functions separately I get the following results:

=COLUMN(G2)-COLUMN($G$2) gives 0.
=ROW(G2)-ROW($G$2) gives 0.

=COVAR(OFFSET($B$2,0,0,4),OFFSET($B$2,0,0,4)) gives 1.25. (This depends on
the values I put in A25, but it evaluates at least.)

So the problem is that the formulars evaluate correctly if I evalate them
separately, but they don't work together.

Further, if I evaluate the fomular using the evaluate formula button, I get
the following step by step results:
1.
=COVAR(OFFSET($B$2,0,{7}-COLUMN($G$2),4),OFFSET($B$2,0,ROW(G2)-ROW($G$2),4))
2. =COVAR(OFFSET($B$2,0,{7}-{7},4),OFFSET($B$2,0,ROW(G2)-ROW($G$2),4))
3. =COVAR(OFFSET($B$2,0,{0},4),OFFSET($B$2,0,ROW(G2)-ROW($G$2),4))
4. =COVAR({#VALUE!},OFFSET($B$2,0,ROW(G2)-ROW($G$2),4))

Does anyone have any clue to make this work?


Thanks,
Geoffrey

 

[hgrove]

Geoffrey Zhu wrote...
...

Further, if I evaluate the fomular using the evaluate formula
button, I get the following step by step results: ...
3. =COVAR(OFFSET($B$2,0,{0},4),OFFSET($B$2,0,
ROW(G2)-ROW($G$2),4))
4. =COVAR({#VALUE!},OFFSET($B$2,0,ROW(G2)-ROW($G$2),4))

Does anyone have any clue to make this work?

This is an obscure problem that arises from passing OFFSET array 2nd o
3rd arguments. The result of doing so is an array of range references
and most functions can't deal with such objects.

The work-around involves making the 2nd or 3rd arguments scalars. Th
easiest way to do that is to sum the degenerate single-element array
returned by ROW and COLUMN.

=COVAR(OFFSET($B$2,0,SUM(COLUMN(G2)-COLUMN($G$2)),4),
OFFSET($B$2,0,SUM(ROW(G2)-ROW($G$2)),4)