Hi David, On Thursday, 2008-03-13 13:56:35 +0000, David King wrote:
> 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}.
Yes, correct. OFFSET does return a reference, that then is dereferenced
if it was the final result of a formula.
> 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.
Indeed, they are defaulted to the dimension of the first reference.
> 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.
Effectively you're now trying to confuse the interpreter ;-)
No, the cause seems to be that for the case of dimension arguments being
given, internally always a range reference is returned. Didn't verify,
but I think that returning a single reference for the case of a 1x1
dimension it could work as you expected. We could call this a bug. Note
however that your example isn't very meaningful, and in Excel generates
an error anyway and doesn't return any value there ...
> To add to my confusion:
>
> {=OFFSET(A1:A2;0;0)} shows {6|5}
>
> and:
>
> {=OFFSET(A1:A2;0;0;1;1)} shows just {6}
This seems to be solved according to your other mail.
Eike
--
OOo/SO Calc core developer. Number formatter stricken i18n transpositionizer.
SunSign 0x87F8D412 : 2F58 5236 DB02 F335 8304 7D6C 65C9 F9B5 87F8 D412
OpenOffice.org Engineering at Sun: http://blogs.sun.com/GullFOSS
Please don't send personal mail to the [EMAIL PROTECTED] account, which I use
for
mailing lists only and don't read from outside Sun. Use [EMAIL PROTECTED]
Thanks.
pgpAEOlawXUl0.pgp
Description: PGP signature
