Array bug?

[Jerry W. Lewis]

I have an array formula whose answer I cannot explain.

Consider the following data layout in A1:G1

A B C D E F G
1 3 5 6

The array formula

=IF(ISNUMBER(A1:G1),A1:G1,OFFSET(A1,0,MATCH(COLUMN(),IF(ISNUMBER(A1:G1),COLUMN(A1:G1)))-1))

returns

1 3 3 3 5 5 6

The sum of these values is 26, but SUM() of the array formula is 33; why?



Here are the diagnostics that I have tried (all are to be array entered)

=ROWS(IF(ISNUMBER(A1:G1),A1:G1,OFFSET(A1,0,MATCH(COLUMN(),IF(ISNUMBER(A1:G1),COLUMN(A1:G1)))-1)))

returns 1, as expected.

=COLUMNS(IF(ISNUMBER(A1:G1),A1:G1,OFFSET(A1,0,MATCH(COLUMN(),IF(ISNUMBER(A1:G1),COLUMN(A1:G1)))-1)))

returns 7, as expected, yet

=SUM(IF(ISNUMBER(A1:G1),A1:G1,OFFSET(A1,0,MATCH(COLUMN(),IF(ISNUMBER(A1:G1),COLUMN(A1:G1)))-1)))

returns 33 instead of 26.


Any thoughts?

Jerry

 

[Daniel.M]

Hi Jerry,

1.
Are you sure you didn't mean to have COLUMN(A1:G1) instead of COLUMN() ?

=SUM(IF(ISNUMBER(A1:G1),A1:G1,N(OFFSET(A1,0,MATCH(COLUMN(A1:G1),
IF(ISNUMBER(A1:G1),COLUMN(A1:G1)))-1))))

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.

Regards,

Daniel M.

 

[Bernard Liengme]

Hi Jerry,
If I array enter
=SUM(IF(ISNUMBER(A1:G1),A1:G1,OFFSET(A1,0,MATCH(COLUMN(),IF(ISNUMBER(A1:G1),COLUMN(A1:G1)))-1)))
in each of A5:L5 (separately) I get values of 18, 24, 24, 30, 30,33,33, 33,
33,....., 33
Using the Evaluate Formula tool (XL2003) these are the results in the
penultimate step:
A5: =SUM(1,3,1,5,6,1)
B5 and C5: =SUM(1,3,3,5,3,6,3)
D5 and E5: =SUM(1,3,5,5,5,6,5)
F5 and subsequent: =SUM(1,3,6,5,6,6,6)

Hope this helps with the mystery!

 

[Jerry W. Lewis]

Bingo!!!!

I did of course mean COLUMN(A1:G1) instead of COLUMN(). With COLUMN(),
the sum was dependent upon where I placed the formula. With
COLUMN(A1:G1) it was independent of location, but still wrong (18
instead of 26). However enclosing OFFSET in N() did indeed make it
behave appropriately.

Thank you for a new trick to add to my collection.
Jerry

 

[Harlan Grove]

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.

 

[Daniel.M]

Thanks Harlan. Very informative as usual.

Have a nice day,

Daniel M.