If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Array Formula - Use of OFFSET function with array argument
Hi All,
I am trying to use the OFFSET function with an array argument as follows: A1 = "Alan" A2 = "Bob" C1 = "Charles" C2 = "Doug" This is my formula: {=OFFSET(A1,0,{0;2},Height,1)} When I set HEIGHT equal to 2, I was expecting / hoping that it would return an array as follows: ={"Alan","Bob";"Charles","Doug"} However, it actually only returns: ={"Alan";"Charles"} I guess that the height argument is not working in conjunction with the array argument? Does anyone know why this is the case, and is there a way around it (without using a UDF or VBA). Thanks, Alan. |
#2
|
|||
|
|||
Array Formula - Use of OFFSET function with array argument
"Alan" wrote...
I am trying to use the OFFSET function with an array argument as follows: A1 = "Alan" A2 = "Bob" C1 = "Charles" C2 = "Doug" This is my formula: {=OFFSET(A1,0,{0;2},Height,1)} When I set HEIGHT equal to 2, I was expecting / hoping that it would return an array as follows: ={"Alan","Bob";"Charles","Doug"} However, it actually only returns: ={"Alan";"Charles"} I guess that the height argument is not working in conjunction with the array argument? Does anyone know why this is the case, and is there a way around it (without using a UDF or VBA). OFFSET returns Range objects, but when it would return multiple range objects, as it would using your formula, it's almost as if it returns an array of Range objects. Whatever it does return, it can only be handled by functions that specifically expect Range arguments, e.g., CELL, INDEX, N and T. Actually, I suppose it could also be handled by OFFSET itself, but that way leads to madness. Anyway, INDEX(OFFSET(A1,0,{0;2},2,1),1) returns {"Alan";"Charles"} when entered into a 2 row by 1 column range. If you evaluate it in the formula bar, it returns only Alan. INDEX(OFFSET(A1,0,{0;2},2,1),2) returns {"Bob";"Doug"} also when entered into a 2 row by 1 column range. You'd need to use the rather odd INDEX(OFFSET(A1,0,{0;2},2,1),{1,2}) to return {"Alan","Bob";"Charles","Doug"} to a 2 row by 2 column range. Note that INDEX only works when returning the results to a range. This is due to Excel's implicit indexing. I don't fully understand it, but the developer notes for the Gnome Project's gnumeric spreadsheet include a discussion. If you want to use all values at once, then you need to rewrite the OFFSET call as OFFSET(A1,{0,1},{0;2},1,1), then wrap the result in N if all entries would be numbers, of in T if all entries would be text. There's no good way to handle this when entries could be mixed. Given your data, try =T(OFFSET(A1,{0,1},{0;2},1,1)) -- To top-post is human, to bottom-post and snip is sublime. |
#3
|
|||
|
|||
Array Formula - Use of OFFSET function with array argument
"Harlan Grove" wrote in message
... OFFSET returns Range objects, but when it would return multiple range objects, as it would using your formula, it's almost as if it returns an array of Range objects. Whatever it does return, it can only be handled by functions that specifically expect Range arguments, e.g., CELL, INDEX, N and T. Actually, I suppose it could also be handled by OFFSET itself, but that way leads to madness. Anyway, INDEX(OFFSET(A1,0,{0;2},2,1),1) returns {"Alan";"Charles"} when entered into a 2 row by 1 column range. If you evaluate it in the formula bar, it returns only Alan. INDEX(OFFSET(A1,0,{0;2},2,1),2) returns {"Bob";"Doug"} also when entered into a 2 row by 1 column range. You'd need to use the rather odd INDEX(OFFSET(A1,0,{0;2},2,1),{1,2}) to return {"Alan","Bob";"Charles","Doug"} to a 2 row by 2 column range. Note that INDEX only works when returning the results to a range. This is due to Excel's implicit indexing. I don't fully understand it, but the developer notes for the Gnome Project's gnumeric spreadsheet include a discussion. If you want to use all values at once, then you need to rewrite the OFFSET call as OFFSET(A1,{0,1},{0;2},1,1), then wrap the result in N if all entries would be numbers, of in T if all entries would be text. There's no good way to handle this when entries could be mixed. Given your data, try =T(OFFSET(A1,{0,1},{0;2},1,1)) Hi Harlan, Thank you so very much for your response - most enlightening! I will have a play around with this, but I think you have fundamentally solved the problem for me. Thanks again, Alan. |
Thread Tools | |
Display Modes | |
|
|