https://bugs.documentfoundation.org/show_bug.cgi?id=166576
Bug ID: 166576
Summary: Array Formulas with column references like B:B or
named columns are very slow even for short columns
Product: LibreOffice
Version: 25.2.3.2 release
Hardware: All
OS: All
Status: UNCONFIRMED
Severity: normal
Priority: medium
Component: Calc
Assignee: [email protected]
Reporter: [email protected]
Description:
Array formulas are one of the ways, consistency of the formula across
a range can be enforced.
When using them that way however, combined with column references,
severe performance issue occur, as the calculation will seemingly be
performed for all rows of the sheet, not just for the subset of the
sheet containing data.
Steps to Reproduce:
1. Prepare a reference data column.
1.1. Enter =RAND() into A1.
1.2. Select A1:A20000
1.3. Sheet > Fill Cells > Fill Down
1.4. Ctrl+C, Ctrl+Shift+V > Values Only to convert the formulas
to fixed numbers.
1.5. Data > Define Range: Define the range "A:A" (the whole column)
as range named "input".
2. Test the performance of regular cell formulas.
2.1. Enter =input*input/input into cell B1.
2.2. Select column B (the range B:B).
2.3. Ctrl+D to "Fill Down"
This should happen near instantaneously.
3. Test the performance of an array formula.
3.1. Select cell C1
3.2. Ctrl+F2
3.3. Enable the “Array” checkbox.
3.4. Type out =input*input/input
Actual Results:
You should observe small hiccups as the “Result” field gets updated.
When typing something slightly more complicated like
=IF(ISNUMBER(input), input, "-")
the hiccups should grow significantly more severe.
Expected Results:
While it makes sense, that an array formula would calculate the whole
array, it would be useful to have some option for limiting the array
formula to consider only ranges, where input data exists.
This is what the ISNUMBER(input) tries to do, but only in the sense
that it doesn't produce an output number, but produces a placeholder
non-numeric string.
Reproducible: Always
User Profile Reset: Yes
Additional Info:
What I was trying to achieve was essentially:
1. Define a whole column as named range, in order to easily check
the name by selecting the column.
2. Use it in a formula, that is guaranteed to be consistent for
the whole column.
In order to improve the performance of the “array formula and column
ranges”-solution, I was asking on reddit to see if there is a way to
limit the number of rows in a sheet, since limiting the sheet to, say,
4000 rows would vastly improve the performance of the resulting
operations. See https://www.reddit.com/r/libreoffice/comments/1kf82c8
__________________________________________________
Version: 25.2.3.2 (X86_64) / LibreOffice Community
Build ID: bbb074479178df812d175f709636b368952c2ce3
CPU threads: 12; OS: Linux 6.4; UI render: default; VCL: gtk3
Locale: en-US (en_US.UTF8); UI: en-US
Flatpak
Calc: threaded
--
You are receiving this mail because:
You are the assignee for the bug.
