indirect address offset in array formula

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Tom - 28 Jan 2008 17:42 GMT

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

Reply to this Message

Dave D-C - 28 Jan 2008 21:57 GMT

I'm not answering your question(s), but is this helpful?
Assume your

Reply to this Message

Dave D-C - 28 Jan 2008 23:32 GMT

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

>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:
[quoted text clipped - 13 lines]
>Thanks for suggestions
>Tom

Reply to this Message

Tom - 29 Jan 2008 16:48 GMT

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

> I'm not answering your question(s), but is this helpful?

Reply to this Message