https://bugs.documentfoundation.org/show_bug.cgi?id=58874
Eike Rathke <er...@redhat.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
CC| |er...@redhat.com
Hardware|x86-64 (AMD64) |All
Version|3.5.4 release |Inherited From OOo
Summary|compatibility with excel |OFFSET function in array
|formula |context compatibility with
| |Excel
--- Comment #11 from Eike Rathke <er...@redhat.com> ---
(In reply to GerardF from comment #8)
> It is not only when OFFSET is inside SUBTOTAL.
> =OFFSET(A1;ROW(1:4);0)
> result in an array of 4 cells (A1:A4) in Calc
> result in 4 arrays of 1 cell in Excel.
Thanks for analysing and the example. I'm not sure that's all there is to it,
but it may explain why in Excel it's not possible to enter
{=OFFSET(A1,{2,4},0)} as a two-cells array formula (results are all #VALUE!),
whereas Calc has no problem with.
Currently there are no means to return (and further process) a vector of
arrays, each one element in this example but {=OFFSET(A1:A3,{2,4},0)} probably
should return 2 arrays of 3 rows each (for A3:A5 and A5:A7), which actually
already almost happens, the final Calc result is a 2 columns x 3 rows matrix,
but it gets a little weird with {=OFFSET(A1:B3,{2,4},0)} ...
Squeezing such "vector of arrays" (if that really is what there is to it) into
the existing interpreter structures would be quite challenging..
However, in Excel the support of that seems to be very special cased, for
example with A1:A5={1;2;4;8;16} the expression =SUMPRODUCT(OFFSET(A1,{2,4},0))
(or =SUMPRODUCT(OFFSET(A1,{2;4},0)) for {2;4} column vector just in case it
mattered) simply does not work and returns 0, not even an error, within or
without array context. Also =SUM(OFFSET(A1,{2,4},0)) does not yield the
expected result of 20 but 4 instead, entered as array formula it returns the
array {4,16}, so treating the two arrays as independent, both results the same
as with =SUBTOTAL(9,OFFSET(A1,{2,4},0)). To me this is all a mess and I'm not
sure if we aren't just chasing a corner case of undefined behaviour and
implementation detail. It may make sense for SUBTOTAL to treat the array
results independently, but otherwise?
Btw, Gnumeric for those examples
a) =SUMPRODUCT(OFFSET(A1,{2,4},0)) gives 4
b) {=SUMPRODUCT(OFFSET(A1,{2,4},0))} gives 0
c) =SUBTOTAL(9,OFFSET(A1,{2,4},0)) gives 4
d) {=SUBTOTAL(9,OFFSET(A1,{2,4},0))} gives 20
e) =SUM(OFFSET(A1,{2,4},0)) gives 4
f) {=SUM(OFFSET(A1,{2,4},0))} gives 20
Hooray.
Calc gives 20 for a) and b), which to me is the only logically correct result,
and c)-f) are identical with Gnumeric.
--
You are receiving this mail because:
You are the assignee for the bug._______________________________________________
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
