Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Array bug?
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(C OLUMN(),IF(ISNUMBER(A1:G1),COLUMN(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),COLUMN(A1:G1)))-1))) returns 33 instead of 26. Any thoughts? Jerry |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
Hi Jerry,
If I array enter =SUM(IF(ISNUMBER(A1:G1),A1:G1,OFFSET(A1,0,MATCH(CO LUMN(),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! -- Bernard www.stfx.ca/people/bliengme remove caps from email "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(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(C OLUMN(),IF(ISNUMBER(A1:G1),COLUMN(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),COLUMN(A1:G1)))-1))) returns 33 instead of 26. Any thoughts? Jerry |
#4
|
|||
|
|||
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 Daniel.M wrote: 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 |
#5
|
|||
|
|||
"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. |
#6
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
Where is the bug in my array? | Excel Discussion (Misc queries) | |||
Array Manipulation | Excel Worksheet Functions | |||
Formula to list unique values | Excel Worksheet Functions | |||
VBA Import of text file & Array parsing of that data | Excel Discussion (Misc queries) |