Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Min function with an offset | Excel Discussion (Misc queries) | |||
XL2002 - OFFSET function and LARGE function | Excel Worksheet Functions | |||
Offset function with nested match function not finding host ss. | Excel Worksheet Functions | |||
offset function | About this forum | |||
help with offset function | Excel Worksheet Functions |