Thanks Harlan. Very informative as usual.

Have a nice day,

Daniel M.

"Harlan Grove" wrote in message
...
"Daniel.M" wrote...
...
2.
Also, OFFSET doesn't always react appropriately in ARRAY formulas but
this seems to be corrected if we enclose it in N(). As if N() around
OFFSET() enabled it to truely consider the non-scalar results coming
from OFFSET() : don't ask me why.
...

The 'why' is that OFFSET called with conforming array 1st, 2nd or 3rd
arguments returns something that seems to behave like an array of range
references. For example, with A1:A6 containing {1;2;3;4;5;6},

=SUM(COUNTIF(OFFSET(A1,{0;1;2},0,3,1),"=2"))

and OFFSET(A1,{0;1;2;3;4;5},0) == INDIRECT("A"&{1;2;3;4;5;6}). What N() does
is return the value of the top-left cell in each of these ranges. The thing
to note is that N() accepts anything as an argument, not just range
references, and notably arrays. When N() is given an array argument, it
returns an array. When fed crypto arrays of range references, it converts
each entry to a value and returns an array of those values, which happens to
be a standard array.

Excel automatically converts single area range references to their values
when used in contexts in which Excel would accept scalars or arrays, but
Excel chokes on multiple area range references and crypto arrays of range
references. That's why it's necessary to convert such arrays to their
values. Note that these crypto arrays of ranges aren't multiple area ranges.

AREAS((A1,A2,A3)) returns 3

=AREAS(OFFSET(A1,{0;1;2},0)) returns {1;1;1}

This is just one of the dark corners of Excel's array semantics. I suppose
we shouldn't expect Microsoft to have documented this because that would
mean someone at Microsoft fully understands Excel's array semantics. Perhaps
I'm being too cynical, but if so why hasn't Microsoft documented this? Nice
paradox: either Microsoft doesn't know how it's own software works or it
can't be bothered to document it.