Using the OFFSET function within SUMXMY2

[yk__]

Hi,

I have the following functions working perfectly well:
=SUMSQ(OFFSET($G$2,$T$3-ROW($G$2),0,Number_of_Periods,1))
I also have the following working well:
=SUMSQ(OFFSET($H$2,$T$3-ROW($H$2),0,Number_of_Periods,1))
But the following function returns the #NUM! error
=SUMXMY2(OFFSET($G$2,$T$3-ROW($G$2),0,Number_of_Periods,1),OFFSET($H$2,$T$3-ROW($H$2),0,Number_of_Periods,1))
I tried other functions that operate on two dynamic range parameters and
they also don't work.
I tried in Excel 2003 and Excel 2007.
What am I doing wrong?

Thanks

 

[Sheeloo]

Us
=SUMXMY2(OFFSET($G$2,$T$3-SUM(ROW($G$2)),0,Number_of_Periods,1),OFFSET($H$2,$U$3-SUM(ROW($H$2)),0,Number_of_Periods,1))

The reason your version is not working is because ROW($G$2) and ROW($H$2)
are being returned as an ARRAY
i.e. instead of returning 2 as ins SUMSQ it is being returned as {2}... You
can see this if you evaluate the formula...

I have wrapped them in SUM() so that you get a scalar...

 

[yk__]

Thank You!! It works!!

Typical Microsoft Bug By Design.

 

[Shane Devenshire]

Hi,

I think you can simplify your formula down to this and it will work:

=SUMXMY2(OFFSET($G$2,$T$3-2,,Number_of_Periods),OFFSET($H$2,$T$3-2,,Number_of_Periods))

First the ROW($G$2) and ROW($H$2) are static, they always equal 2 so just
use 2. A few of the other arguments can be left out because you are using
the default.

 

[yk__]

Thank you. I already did simplify it to some degree, but the reference to the
fixed cells serves a purpose.
The spreadsheet will continue to work when cells are inserted or removed
because Excel will update the references automatically, while if I just wrote
"2" I would have to change every cell manually.