OFFSET with array 2nd or 3rd argument
Author |
Message |
Harlan Grov #1 / 8
|
 OFFSET with array 2nd or 3rd argument
Intentionally cross-posted (but not multiposted). Formulas like =SUM(OFFSET(A1,{0;2;4},0,1,1)) entered as an array formula or not returns the value of A1 only, but =SUM(N(OFFSET(A1,{0;2;4},0,1,1))) returns the value of A1+A3+A5. Does anyone know what OFFSET(A1,{0;2;4},0,1,1) returns? A multiple area range?
|
Thu, 08 Jul 2004 10:53:40 GMT |
|
 |
Aladin Akyur #2 / 8
|
 OFFSET with array 2nd or 3rd argument
Quote:
> Intentionally cross-posted (but not multiposted). > Formulas like > =SUM(OFFSET(A1,{0;2;4},0,1,1)) > entered as an array formula or not returns the value of A1 only, but > =SUM(N(OFFSET(A1,{0;2;4},0,1,1))) > returns the value of A1+A3+A5.
So do =SUM(TRANSPOSE(OFFSET(A1,{0;2;4},0,1,1))) and =SUM(SUM(OFFSET(A1,{0;2;4},0,1,1))) Quote: >Does anyone know what > OFFSET(A1,{0;2;4},0,1,1) > returns? A multiple area range?
You might be right about OFFSET(A1,{0;2;4},0,1,1) returning a multiple area range as =AREAS(OFFSET(A1,{0;2;4},0,1,1)) evaluates to {1;1;1} and =SUM(AREAS(OFFSET(A1,{0;2;4},0,1,1))) results in 3. By the way, how does N in =SUM(N(OFFSET(A1,{0;2;4},0,1,1))) force the valuation to complete to the value of A1+A3+A5? I know the double SUM in =SUM(SUM(OFFSET(A1,{0;2;4},0,1,1))) invokes the same question (see http://www.mrexcel.com/wwwboard/messages/13475.html for a recent eruption of this volcanic question) Aladin
|
Thu, 08 Jul 2004 17:14:17 GMT |
|
 |
Tushar Meht #3 / 8
|
 OFFSET with array 2nd or 3rd argument
Maybe this explanation from Laurent Longre applies here: http://groups.google.com/groups?oi=djq&selm=an_562009917 Quoting from it... Why the N() function? Because OFFSET doesn't return values but range references, like INDEX or INDIRECT. So, when the 2nd and 3rd parameters are vectors, OFFSET doesn't return an array of values, but an array of references. And Excel can't show directly the contents of this (undocumented) data type. It would show #VALUE! -- Regards, Tushar Mehta www.tushar-mehta.com --
Quote: > Intentionally cross-posted (but not multiposted). > Formulas like > =SUM(OFFSET(A1,{0;2;4},0,1,1)) > entered as an array formula or not returns the value of A1 only, but > =SUM(N(OFFSET(A1,{0;2;4},0,1,1))) > returns the value of A1+A3+A5. Does anyone know what > OFFSET(A1,{0;2;4},0,1,1) > returns? A multiple area range?
|
Fri, 09 Jul 2004 01:35:06 GMT |
|
 |
Harlan Grov #4 / 8
|
 OFFSET with array 2nd or 3rd argument
Thanks.
|
Fri, 09 Jul 2004 08:47:50 GMT |
|
 |
Harlan Grov #5 / 8
|
 OFFSET with array 2nd or 3rd argument
Quote:
... >>Does anyone know what >> OFFSET(A1,{0;2;4},0,1,1) >> returns? A multiple area range? >You might be right about OFFSET(A1,{0;2;4},0,1,1) returning a multiple >area range as >=AREAS(OFFSET(A1,{0;2;4},0,1,1)) evaluates to >{1;1;1}
... I keep forgetting about that function. Quote: >By the way, how does N in =SUM(N(OFFSET(A1,{0;2;4},0,1,1))) force the >valuation to complete to the value of A1+A3+A5? I know the double SUM in
... Tushar's reference to an old Laurent Longre post partially explains it. It's not a satisfying explanation because the formulas =INDIRECT("A1:A4") =INDEX(A1:A10,{1;2;3;4},1) =OFFSET(A1,{0;1;2;3},0,1,1) return different things. INDIRECT returns a range reference to 4 cells, OFFSET returns an array of 4 one-cell range references (thanks for establishing that), and INDEX returns something else entirely. OK, now that you figured out what OFFSET returns, care to take a guess what INDEX returns. Nevertheless, N() and T() dereference the top right cells in the first area of range references, thus converting them into scalars. FWIW, N() only returns numeric values, converting everything else to 0, and T() only returns string values, converting everything else to "". If you want either returned, use CELL("Contents",...).
|
Fri, 09 Jul 2004 09:05:55 GMT |
|
 |
Aladin Akyur #6 / 8
|
 OFFSET with array 2nd or 3rd argument
Quote:
> ... > >>Does anyone know what > >> OFFSET(A1,{0;2;4},0,1,1) > >> returns? A multiple area range? > >You might be right about OFFSET(A1,{0;2;4},0,1,1) returning a multiple > >area range as > >=AREAS(OFFSET(A1,{0;2;4},0,1,1)) evaluates to > >{1;1;1} > ... > I keep forgetting about that function. > >By the way, how does N in =SUM(N(OFFSET(A1,{0;2;4},0,1,1))) force the > >valuation to complete to the value of A1+A3+A5? I know the double SUM in > ... > Tushar's reference to an old Laurent Longre post partially explains it.
OK. What is/should be the story on double SUM or AVERAGE and TRANSPOSE then? Quote: > It's not a satisfying explanation because the formulas > =INDIRECT("A1:A4") > =INDEX(A1:A10,{1;2;3;4},1) > =OFFSET(A1,{0;1;2;3},0,1,1) > return different things. INDIRECT returns a range reference to 4 cells, > OFFSET returns an array of 4 one-cell range references (thanks for > establishing that), and INDEX returns something else entirely. OK, now that > you figured out what OFFSET returns, care to take a guess what INDEX > returns.
The cell of =INDEX(A1:A10,{1;2;3;4},1) shows 3, the first element of A1:A10 (a scalar, not a reference or indexical), immediately after pasting it. Hit F9, I'm not imagining, you get: =I#NAME? Stands for what? I)nternal #NAME? error maybe? After escape, hit F9, guess what: 3. I can't get =I#NAME? anymore from within this cell. If you want to see the "beast" back, you need a fresh paste into an unused cell. Quote: > Nevertheless, N() and T() dereference the top right cells in the first area > of range references, thus converting them into scalars. > FWIW, N() only returns numeric values, converting everything else to 0, and > T() only returns string values, converting everything else to "". If you > want either returned, use CELL("Contents",...).
|
Sat, 10 Jul 2004 06:49:40 GMT |
|
 |
Harlan Grov #7 / 8
|
 OFFSET with array 2nd or 3rd argument
... Quote: >OK. What is/should be the story on double SUM or AVERAGE and TRANSPOSE
then? ... My GUESS is that the first function dereferences each element in the array of references separately, returning an array of values. The second function call then operates over this array of values. Quote: >The cell of >=INDEX(A1:A10,{1;2;3;4},1) >shows 3, the first element of A1:A10 (a scalar, not a reference or >indexical), immediately after pasting it. >Hit F9, I'm not imagining, you get: >=I#NAME?
I can't duplicate this with Excel 97 SR-2.
|
Sun, 11 Jul 2004 07:05:30 GMT |
|
 |
Aladin Akyur #8 / 8
|
 OFFSET with array 2nd or 3rd argument
Quote:
> ... > >The cell of > >=INDEX(A1:A10,{1;2;3;4},1) > >shows 3, the first element of A1:A10 (a scalar, not a reference or > >indexical), immediately after pasting it. > >Hit F9, I'm not imagining, you get: > >=I#NAME? > I can't duplicate this with Excel 97 SR-2.
I've got that in Excel 2000 on MacOS 9. And it does not happen in Excel 2000 on Windows either.
|
Mon, 12 Jul 2004 19:25:54 GMT |
|
|
|