OFFSET with array 2nd or 3rd argument
Author Message 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  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)

Thu, 08 Jul 2004 17:14:17 GMT  OFFSET with array 2nd or 3rd argument
Maybe this explanation from Laurent Longre applies here:

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  OFFSET with array 2nd or 3rd argument
Thanks.

Fri, 09 Jul 2004 08:47:50 GMT  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  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.

- Show quoted text -

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  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  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

 Page 1 of 1 [ 8 post ]

Relevant Pages