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.


"Jerry W. Lewis" wrote in message
...
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(A
1: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(C OLUMN(),IF(ISNUMBER(A1:G1),COL
UMN(A1:G1)))-1)))

returns 1, as expected.


=COLUMNS(IF(ISNUMBER(A1:G1),A1:G1,OFFSET(A1,0,MATC H(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(CO LUMN(),IF(ISNUMBER(A1:G1),COLU
MN(A1:G1)))-1)))

returns 33 instead of 26.


Any thoughts?

Jerry