Using the OFFSET function within SUMXMY2
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 |
Using the OFFSET function within SUMXMY2
Use
=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__" wrote: 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 |
Using the OFFSET function within SUMXMY2
Thank You!! It works!!
Typical Microsoft Bug By Design. "Sheeloo" wrote: Use =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__" wrote: 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 |
Using the OFFSET function within SUMXMY2
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. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "yk__" wrote: 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 |
Using the OFFSET function within SUMXMY2
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. "Shane Devenshire" wrote: 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. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "yk__" wrote: 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 |
All times are GMT +1. The time now is 08:59 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com