New update 7.2.1.2 ruined my array calculations

Boaesh1 · September 25, 2021, 10:13pm

Column B = Names
Column K = A calculated %
Column V = Ranked (column K) from high to low

Previous Array formula that now returns errors due to the latest update. It used to display the name associated with the ranked % in column V

Column U = INDEX($B$10:$B$51,SMALL(IF($K$10:$K$51=V10,ROW($K$10:$K$51)-ROW($K$9)),COUNTIF($V10:V$10,V10)))

I am no expert and have no idea how to fix this. Someone helped me with the array formula I previously had working.

Hoping someone can look at it, and somehow manage to tell me what I need to do to get it to work once again.

Thank you in advance.
Test.ods (40.9 KB)

erAck · September 25, 2021, 10:50pm

You did not say

what version you used before
what version you updated to
the exact data the formula operates on

Please edit your question and attach a sample document.

Boaesh1 · September 25, 2021, 11:01pm

Done. Still no solution. I am patiently awaiting. My documents are useless without a fix.

erAck · September 26, 2021, 6:50pm

That is a bug that will be fixed in 7.2.2. The problem in the attached document is that the COUNTIF($V10:V$10,V10) to COUNTIF($V$10:V18,V18) expressions yield a 0 result, for which passed as Rank to SMALL() it then rightly complains with an error. You can inspect that by invoking the Function Wizard (Ctrl+F2) on such formula and looking at its Structure page and by stepping through with the Next button.

Btw, the

=IF(ISERROR(INDEX($B$10:$B$18;SMALL(IF($K$10:$K$18=V10;ROW($K$10:$K$18)-ROW($K$9));COUNTIF($V10:V$10;V10))));"";INDEX($B$10:$B$18;SMALL(IF($K$10:$K$18=V10;ROW($K$10:$K$18)-ROW($K$9));COUNTIF($V10:V$10;V10))))

can be simplified to

=IFERROR(INDEX($B$10:$B$18;SMALL(IF($K$10:$K$18=V10;ROW($K$10:$K$18)-ROW($K$9));COUNTIF($V10:V$10;V10)));"")

which in the non-error case needs to do the calculation only once.

Update:
Bug being worked on is tdf#144740 that can be tracked.

Boaesh1 · September 26, 2021, 7:11pm

Just to clarify, there are no immediate solutions to the problem until the bug is fixed in 7.2.2.? If that’s a yes, would you happen to know when 7.2.2. is scheduled for release?

Hrbrgr · September 26, 2021, 7:20pm

LibreOffice is available in the following prerelease versions:

Download LibreOffice | LibreOffice - Free Office Suite - Based on OpenOffice - Compatible with Microsoft

Have a look at:
ReleasePlan

Boaesh1 · September 26, 2021, 7:28pm

Thanks. Downloaded. Installed.

erAck · September 26, 2021, 7:53pm

Remove the number format (0.00%) from V10:V18 (set to General) and then force recalculation of U10:U18 (hit F9 on each individual cell, or hard recalculate the entire document with Shift+Ctrl+F9).
In the currently available pre-release the bug is not fully fixed.

Boaesh1 · September 26, 2021, 8:14pm

Updated to 7.2.2.1 from 7.2.1.2 and though it’s partially working. Not all the names are displayed as they use to. I’m attaching another example with with the 7.2.2.1. version of the problem.
Test.ods (43.4 KB)

Boaesh1 · September 26, 2021, 9:55pm

I’ve reverted back to 7.1.6.2 until this is resolved. I can’t start the work week with a broken database.

Thanks for taking the time.

EarnestAl · September 27, 2021, 1:41am

Good move. I am still using 7.1.5.2 for work.
I have later versions installed for testing or using some new features too.

Boaesh1 · December 26, 2021, 7:21pm

Installed version 7.2.4. The issue I was having with the database are non existent with this version. If anyone was having similar issues I would recommend this version.