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?

=SUM(TRANSPOSE(OFFSET(A1,{0;2;4},0,1,1)))

and

=SUM(SUM(OFFSET(A1,{0;2;4},0,1,1)))

=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

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

I keep forgetting about that function.

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",...).

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

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.