To comment on the following update, log in, then open the issue:
http://www.openoffice.org/issues/show_bug.cgi?id=86658
User er changed the following:
What |Old value |New value
================================================================================
Status|UNCONFIRMED |RESOLVED
--------------------------------------------------------------------------------
Resolution| |INVALID
--------------------------------------------------------------------------------
------- Additional comments from [EMAIL PROTECTED] Tue Mar 4 17:32:52 +0000
2008 -------
You're making some wrong assumptions.
> The only difference is the inclusion of the row/col size - which default
> to 1 if omitted.
No, the default should be the size of the dimension of the original
array passed. Your example of {=OFFSET($A$1;ROW(A1:A3)-1;0;1;1)}
actually exposes correct behavior, you explicitly told the function to
create a 1x1 array.
> {=ROWS(OFFSET($A$1;0;0;ROW(A1:A3);1))} as an array expression should
> (I'm fairly sure) return {1|2|3} but returns {3}
> Excel97 gets it wrong (differently) too.
This is a nasty case. Let me explain what happens internally:
1. ROW(A1:A3) creates an array {1|2|3}.
2. Because we are in array context, for the OFFSET call a 3x1 iteration
matrix is created.
3. For each element of the iteration matrix the OFFSET function is
called, passing the corresponding parameter of the {1|2|3} matrix
created by ROW, i.e. 1 on the first iteration, 2 on the second
iteration, 3 on the third iteration.
4. Temporary references are created for each iteration, namely A1:A1,
A1:A2 and A1:A3.
5. Of each reference the cell result corresponding to the iteration
(offset-wise) is taken and assigned to the final result matrix, at
the end resulting in values of {A1|A2|A3} dereferenced, giving
a result matrix of {6|5|9}.
6. Applying ROWS({6|5|9}) of course gives the result 3.
Excel somehow handles the internal steps differently. You'll also notice
that if you create a 4x1 array formula in Excel
{=OFFSET($A$1;0;0;ROW(A1:A3);1)} you'll get the result
{#VALUE!|#VALUE!|#VALUE!|#N/A} but nevertheless for
{=ROWS(OFFSET($A$1;0;0;ROW(A1:A3);1))} the result 1. This is
inconsistent, it should be 3.
In Calc, a 4x1 formula {=OFFSET($A$1;0;0;ROW(A1:A3);1)} produces
{6|5|9|#N/A} and {=ROWS(OFFSET($A$1;0;0;ROW(A1:A3);1))} gives 3, which
I consider much more correct..
> {=INDIRECT("A"&ROW(A1:A3))} produces an array result, but
> {ROWS( INDIRECT("A"&ROW(A1:A3)) )} does not. Should give {1|1|1}
> I think.
No, it should not. ROWS(array) returns the number of rows in the array.
So, at the end it turns out that all this is not an issue ;-)
> I don't know if ROWS *should* be able to handle arrays... ODFF does
> not seem to list functions that do and don't. By rumour Microsoft
> have a list of which functions are array-aware in Excel - but it's
> well hidden.
There may be some confusion about what array-aware may actually mean.
Different cases are to be considered:
1. Formula not in array context.
1.1. Parameter accepts range reference => array passed as argument
should be accepted as well.
1.2. Parameter expects single value => upper left of array argument
is taken.
2. Formula in array context.
2.1. same as 1.1.
2.1. Parameter expects single value => iteration matrix is created
and function is executed for each matrix element as described
above.
3. Formula not in array context but a function is used that forces the
formula into array context, for example SUMPRODUCT. Mechanisms of #2
are applied. The functions acting such have a ForceArray prefix for
their parameters in the ODFF specification.
I assume with the "list of which functions are array-aware in Excel" you
were referring to #3, if you can get hold of such a list I would be
interested to see that. We determined the functions only by inspecting
formulas' class tokens in the Excel binary file format. If the list
described something else I would still like to see it ;-)
---------------------------------------------------------------------
Please do not reply to this automatically generated notification from
Issue Tracker. Please log onto the website and enter your comments.
http://qa.openoffice.org/issue_handling/project_issues.html#notification
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
