Hello all
This follows from issue 86658, which I raised and Eike closed as
invalid (with much helpful comment). I am a contributor to the
OOo Wiki, having written a number of HowTos, etc. Eike has
suggested I move further discussion of the issue to this mailing
list.
The bit I'd like to tackle is:
A1:A3 have {6|5|9}
C1 has {=OFFSET($A$1;{0|1};0)} (array formula) => C1=6 C2=5
D1 has {=OFFSET($A$1;{0|1};0;1;1)} (array formula) => D1=6
D2=#VALUE!
I am attempting to document how array formulas work. Often (eg in
Excel) they are described as evaluating the formula for each
element of the array, placing the result in each element of the
output array.
This works with eg SQRT({4;9}) which returns {2;3}. It works with
OFFSET($A$1;{0|1};0) which shows {6|5}. I'm taking it as a given
that OFFSET returns a reference (not a value as described in the
current Help) - so I would explain this as returning {A1|A2},
dereferenced to {6|5}.
In this vein I expected OFFSET($A$1;{0|1};0;1;1) to return
{A1|A2} too. The last two (size) parameters were omitted in the
first case and I expected to have defaulted to the size of $A$1,
ie 1 x 1. Wrong.
I then thought that perhaps if and the only if last two (size)
parameters are included, a second process happens to resize the
result. Thus the result {A1|A2} becomes {A1}. But that's not
right either.
To add to my confusion:
{=OFFSET(A1:A2;0;0)} shows {6|5}
and:
{=OFFSET(A1:A2;0;0;1;1)} shows just {6}
I don't understand this at all - which somewhat limits my ability
to document it for others ;)
Hoping for enlightment,
David
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
