Bug 40033 - "Automatic" array formulas
Summary: "Automatic" array formulas
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected)
3.3.2 release
Hardware: x86 (IA32) Linux (All)
: medium enhancement
Assignee: Not Assigned
Depends on:
Reported: 2011-08-12 01:02 UTC by fQT5rjII
Modified: 2012-01-17 13:55 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Note You need to log in before you can comment on or make changes to this bug.
Description fQT5rjII 2011-08-12 01:02:30 UTC
Dear Sir or Madam,
LO calc supports the copying of array formulas as described in http://help.libreoffice.org/Calc/Array_Functions#Copying_Array_Formulas . 

However I'm missing the automatic adjustment of array formulas: If you "drag" a "normal" formula, say "=SUM(A1:A$100)" to the cell below, the formula reads "=SUM(A2:A$100)". That's what I'm looking for for array formulas.

It could be that I just don't know how to do that. In that case, the user interface could be enhanced. If Libreoffice is missing this feature, I'd love to see it implemented in a future version.

Keep up with your good work!

PS: Also see https://answers.launchpad.net/ubuntu/+source/libreoffice/+question/167629
Comment 1 Jeffrey 2011-08-13 20:48:23 UTC
Failed to reproduce on LibreOffice 3.4  340m1(Build:103) for OpenSuse Linux. 

Dragging formula for SUM works, except it shifts the cell range from A1 to A100 to A2 to A101, which works as expected.
Comment 2 fQT5rjII 2011-08-18 03:43:44 UTC
Perhaps I need to clarify: The "dragging" of "=SUM(…)" works fine. I'm looking for "dragging" of /array/ formulas like "{=SUM((A1:A100>=90)*(A1:A100<=120)*(A1:A100))}". does that work on your LO 3.4, Jeffrey?
Comment 3 Reto 2011-09-20 12:44:00 UTC
(In reply to comment #2)
> Perhaps I need to clarify: The "dragging" of "=SUM(…)" works fine. I'm looking
> for "dragging" of /array/ formulas like
> "{=SUM((A1:A100>=90)*(A1:A100<=120)*(A1:A100))}". does that work on your LO
> 3.4, Jeffrey?

I tried it on LO 3.4.3 and it does not work neither. Having the array formula in cell D1 and dragging it to D2 I get an error like #NAME? in both cells because the function changes to "sum" instead of "SUM". Beside, it looks like it would not copy incrementially but expand the array to the next cell.
Comment 4 Reto 2011-09-20 14:02:06 UTC
Addition to comment 3:
The error #NAME? only results if functions are involved with the array formula. If only cell addresses are involved there is no error, but it still does not copy incrementially but only expands the array to the next cell. It seems like this is wanted? Maybe it is!
Comment 5 Regina Henschel 2011-10-02 12:14:23 UTC
For array formulas simple dragging adjusts the output array. To get copy-drag you have to hold Ctrl-key while dragging.
Comment 6 fQT5rjII 2011-10-03 00:52:00 UTC
After an update to 3.4.3, it indeed seems to be fixed :)

PS: @Regina: In my case, it's the other way around (ctrl adjusts, simple dragging copies) :)
Comment 7 Reto 2011-10-03 14:57:03 UTC
I agree, copy-drag works as expected when holding the Ctrl-key while dragging. I didn't carefully read the help for "Copying Array Formulas".

But simple dragging doesn't work properly. It works if the array formula does not contain functions like SUM. Otherwise I get an error: #NAME? in every cell of the array range because the function changes from "SUM" to "sum". It can be fixed by editing the array range and changing the function back from "sum" to "SUM". But this is rather a workaround, it should work in the first step.