Created attachment 183357 [details] Sorting array formula example In the attachment, the data range "range1" (A1:A15) is sorted in C1:C15 using array formulas like > {=INDEX(range1;MATCH(LARGE(COUNTIF(range1;">"&range1);ROW(C1:C1));COUNTIF(range1;">"&range1);0))} The results in Excel start with sorted cells containing data, then zeroes; in Calc, zeroes are first. It seems to me that Calc is correct in this case (because indeed, empty cells are those that have most other cells greater than them); but this is an interop problem; and anyway, maybe I miss some detail (I always am confused by array evaluation).
Created attachment 183363 [details] Formula analysis Hi Mike... Using the function wizard to analyze the function I came to a similar conclusion. It seems Calc is right because an empty cell is "smaller" than a cell with text. In the sample file, cell A4 is empty hence it has 7 cells that are greater than it (which are the cells with values). The weird bit is that in Excel, testing if an empty cell is "smaller" than a cell with value also returns TRUE (as well as in Calc). But in the array function the test returns a different result, as if an empty cell is "larger" than a cell with value. Hence the problem is in the COUNTIF(range1;">"&range1) part, but IMO Excel is doing it wrong.
Created attachment 183364 [details] Comparison of Calc and Excel See the attached image for more details. I used the same array formula in both files: =COUNTIF(A1:A15;">"&A1:A15) The formula is applied as an array formula over the range A1:A15. Notice how the results are different. However, note below that in both Calc/Excel an empty cell is "smaller" than a cell with text.
For Criteria comparison operators empty being smaller than any content seems natural to me. However, maybe Excel does that differently because Sort (that actually sorts cells) sorts empty cells to the end of the range (which also Calc does), i.e. sort column A to see.
Created attachment 183368 [details] Formula to sort arrays of data. Seems to me that such formula in any case is not of too much confidence. Excel: Introducing something in the empty cells between data (a4, a8) produce a result sort list with replicated values at the end. If there are numbers, the second it's not on the sorted list. Calc: retains the '0' at the top, but if numbers in a4,a8 only a4 it's on the sorted list but duplicated. Attached a sample file that deals with numbers, text, blank cells (at the end), working with calc and excel. Based on 'ken johnson' answer https://forum.openoffice.org/en/forum/viewtopic.php?p=290729#p290729
Hello, It's been a while since this issue was reported. Could you please try to reproduce it with the latest version of LibreOffice from https://www.libreoffice.org/download/libreoffice-fresh/ ? I have set the bug's status to 'NEEDINFO'. Please change it back to 'UNCONFIRMED' if the bug is still present in the latest version.
Dear Mike Kaganski, This bug has been in NEEDINFO status with no change for at least 6 months. Please provide the requested information as soon as possible and mark the bug as UNCONFIRMED. Due to regular bug tracker maintenance, if the bug is still in NEEDINFO status with no change in 30 days the QA team will close the bug as INSUFFICIENTDATA due to lack of needed information. For more information about our NEEDINFO policy please read the wiki located here: https://wiki.documentfoundation.org/QA/Bugzilla/Fields/Status/NEEDINFO If you have already provided the requested information, please mark the bug as UNCONFIRMED so that the QA team knows that the bug is ready to be confirmed. Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-NeedInfo-Ping