https://bugs.documentfoundation.org/show_bug.cgi?id=172489
Bug ID: 172489
Summary: Calc's FILTER() is exhibiting weird behaviour?
Product: LibreOffice
Version: 26.2.3.2 release
Hardware: All
OS: Linux (All)
Status: UNCONFIRMED
Severity: normal
Priority: medium
Component: Calc
Assignee: [email protected]
Reporter: [email protected]
Description:
First, I'm sorry if this isn't a bug. I'm just noting some anomalies here,
Anyway...
1. It seems that if I do a mass FILTER() function just under what is basically
a header, it will fill in the blank spaces with a #N/A error. (But if I do this
beside, without a header in the cells above) Then the filled data is fine, and
empty on the unused cells.
2. Now I'm getting fairly niche, I'm sure. If I set one of my filters to check
for "Non-modular" in its list, it will fill up the entire range with the (1)
PSU's data that matches this value. -- There's a chance this might just be from
only finding 1 match in the filter..
I'm pulling from some dabbling/experimenting with putting some PC Building
Simulator parts into a spreadsheet (and preferring not to rely on Google)
context.
Steps to Reproduce:
1. Build a database. ($DB.$A$2:$C$10, for example.)
Name1 Stat-B StatC
Name2 Stat-B StatC1
Name3 Stat-B StatC1
2. Have a unique cell for at least (1) column for FILTER().
StatC is unique in this case.
3. Find a separate, empty space. (Either a separate sheet or 5 columns over.)
-- In my case, Sheet1.A1:C3 (+9.)
4. Create a FILTER() function. This is now happening to me consistently, even
without a header nearby.
FILTER($A$2:$C$10,
(($F$1="")+($B$2:$B$10=$F$1))*
(($G$1="")+($C$2:$C$10=$G$1)),
"VOID")
5. Use the indicated cells ($F$1 and $G$1) as a filter reference point. --
Start alternating between Stat-B, StatC, StatC1, and a blank cell.
Actual Results:
It seems to vary at times, I have these options:
F1/G1 blank: =FILTER() returns the database (sometimes) properly.
One filter at E2 returns the database without errors.
Second filter at E15 returns the full database, but also leaves #N/A
errors at cells E24-G24.
F1=Stat-B,G1 blank: =FILTER() returns the full database, then fills up the rest
of its range with #N/A errors.
E2 FILTER leaves #N/A errors at E5:G7.
E15 FILTER leaves #N/A errors at E18:G24.
F1 blank, G1=StatC: =FILTER() fills its entire range in the first row's data.
(A2:C2)
E2 ranges from E2:G7
E15 ranges E15:G24. (I don't know why there's a difference in range
with the same literal filter data.)
F1 blank, G1=StatC1: Similar to B1=Stat-B, where it gets the filters properly,
but leaving #N/A errors behind.
E2 grabs Name2/Name3 in cells E2:G3 and fills E4:G7 with #N/A errors.
E15 grabs the same Name2/Name3 in cells E15:G16 with #N/A errors
filling E17:G24.
F1=Stat-B and G1=StatC produces the same results as F1 blank/G1=StatC.
F1=Stat-B and G1=StatC1 produces identical results as F1 blank/G1=StatC1.
Expected Results:
That the FILTER() would only return results that match all the filter criteria,
or if there's no match, return VOID.
Reproducible: Always
User Profile Reset: Yes
Additional Info:
For whatever reason, if I set the values F1/G1 first, *then* set E2's data to
be a =FILTER() function, then E2 will properly grab the data, and never again
update.
Further, having deleted the data and re-setting the FILTER() data has, for
whatever reason, allowed FILTER() to output data in its missing cells.
(E8:E10.) Except, there's the strange part, for a 10-cell range, it should have
been E2:E11.
Further, the E2 cell, when I right click the cell property field to copy/edit
it, (and then I get the warning "I can only edit part of an array") I right
click-> select all, will try to select E1:E10, instead of E2:E11. -- This is
counting the F1/G1 filter cells for some reason.
--
You are receiving this mail because:
You are the assignee for the bug.
