Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
indirect address offset in array formula
When using array formulas with the above functions I got what I didn't
expect, some of them work some of them it seems don't: 1. =ADDRESS({1\2\3},{1\2\3}) works fine when array - entered in a 3- row/1-col table, displaying as expected :A1, B2, C3 2. =INDIRECT({"a1"\"b2"\"c3"}) does not get results except ErrValue when array - entered in a 3-row/1-col table (but analogous ADDRESS works, so is it a matter of arguments being numerical ?) - I wolud espeially like this form of INDIRECT to work but it does not 3. similarily when I wanted to get the diagonal of a 4x4 matrix starting at A13 by using =OFFSET (A13;{0\1\2\3};{0\1\2\3}) array - entered in a 4-row/1-col table I didn't get the result except ErrValue (bad arguments ?) So why is ADDRESS working array-entered getting array as an result and INDIRECT and OFFSET does not get the result as an array when array- entered. Is it a mistake of me incorrectly entering these formulas ? Thanks for suggestions Tom |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
indirect address offset in array formula
I'm not answering your question(s), but is this helpful?
Assume your |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
indirect address offset in array formula
I'm not answering your question(s), but is this helpful?
Assume your matrix is in a1:d4. In E5 put =CHOOSE(COLUMN(E5)-COLUMN($E5)+1,$A$1,$B$2,$C$3,$D$4) and copy to the right. Or in E6 put =CHOOSE(ROW(E6)-ROW(E$6)+1,$A$1,$B$2,$C$3,$D$4) and copy down. Dave D-C Tom wrote: When using array formulas with the above functions I got what I didn't expect, some of them work some of them it seems don't: 1. =ADDRESS({1\2\3},{1\2\3}) works fine when array - entered in a 3- row/1-col table, displaying as expected :A1, B2, C3 2. =INDIRECT({"a1"\"b2"\"c3"}) does not get results except ErrValue when array - entered in a 3-row/1-col table (but analogous ADDRESS works, so is it a matter of arguments being numerical ?) - I wolud espeially like this form of INDIRECT to work but it does not 3. similarily when I wanted to get the diagonal of a 4x4 matrix starting at A13 by using =OFFSET (A13;{0\1\2\3};{0\1\2\3}) array - entered in a 4-row/1-col table I didn't get the result except ErrValue (bad arguments ?) So why is ADDRESS working array-entered getting array as an result and INDIRECT and OFFSET does not get the result as an array when array- entered. Is it a mistake of me incorrectly entering these formulas ? Thanks for suggestions Tom |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
indirect address offset in array formula
I entered the formulas as you suggested and it works OK, but one thing
is still disturbing to me: when using this CHOOSE function I seem to need to manually enter all addresses of where diagonal cells are, and the reason I wanted to somehow employ array-formula was to avoid such manualities. As for that diagonal thing, I finally thought of an array returning array formula which is (entered in 1row/4cols): {=INDEX(A1:D4;COLUMN(1:4);COLUMN(1:4))} which does the job. But still, I would like to use that tricky INDIRECT thingy as array returning but it won't :-( Tom Dave D-C wrote: I'm not answering your question(s), but is this helpful? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problems with using TEXT, INDIRECT and ADDRESS within an array for | Excel Worksheet Functions | |||
Using SUM, ADDRESS, INDIRECT in an array not working | Excel Worksheet Functions | |||
named range in sum formula (indirect, offset, worksheet name) | Excel Worksheet Functions | |||
Array reference using indirect address | Excel Worksheet Functions | |||
improve formula offset and indirect | Excel Worksheet Functions |