There seem to be some inconsistencies in the RANK() function in Calc as of LO 7.4.6 (since at least LO 4.0, if not before that). For the purpose of this report: * Rank(Value;Data[;Type]) * L.Value is the minimum value in Data. * G.Value is the maximum value in Data. The following items are presented in increasing magnitude/importance of problematic results (starting from "no problem" towards "serious problem"). 0.1. When Value is "string" or refers to a string, then the result is #Value!. This seems adequate. 0.2. When Value is "string" or refers to a string, and it is included in Data, then the Ranks corresponding to (other) valid values in Data discard the existence of the string value. This seems adequate. 0.3. When Value refers to an empty cell, then the result is #Value!. This seems adequate; although someone could argue in favor of a different result such as #N/A. 1. When Value < L.Value, or when Value > G.Value, then the result is #Value!. IMO, it should be #N/A, as in other spreadsheet software tools. 2. When Type is a number <> zero, and when 'L.Value < Value > G.Value', ((and) Value is not listed in Data), the result is "-1" (without quotation marks). I couldn't find official Release Notes on this important change (which can affect further calculations, preventing interoperability with other spreadsheet tools). This result is not seen in LO 3.3, (nor in AOO, GSheets, Excel). Using other spreadsheet software tools, this results in #N/A. 3. When Type = 0 (zero), and when 'L.Value < Value > G.Value', ((and) Value is not listed in Data), the result is still a positive integer number, which is INCORRECT! 4. When using RANK.EQ() instead of RANK(), when 'L.Value < Value > G.Value', ((and) Value is not listed in Data), the result is still a positive number, which is INCORRECT! Moreover, when there are duplicate values in Data, the results can fall beyond the maximum rank. I'll be attaching a sample file soon.
Created attachment 186403 [details] Sample ods showing inconsistencies in function RANK()
Possibly each of the issues would deserve its own report, but let's go like this for now. Please provide explanations for each of the issues as they are reflected in your example file. Testers should not need to think. Testing with 3.5, I see all the cells with yellow background colour have either #VALUE! (L-P) or #NAME? (S-W). Set to NEEDINFO. Change back to UNCONFIRMED after you have provided the information.
(In reply to Buovjaga from comment #2) > Possibly each of the issues would deserve its own report, but let's go like > this for now. The function needs to be reviewed, probably as a whole. Splitting the issues would make it more difficult, IMHO. > > Please provide explanations for each of the issues as they are reflected in > your example file. Testers should not need to think. I wished every reporter would take the same approach. Unfortunately for this case, we need someone that understands what RANK() is supposed to do, in order to understand why I marked some cells with yellow. > > Testing with 3.5, I see all the cells with yellow background colour have > either #VALUE! (L-P) or #NAME? (S-W). If you test with LO 4.0 or with any current version (7.4 or 7.5), you will see the difference. If you can also test equivalent formulas in some other spreadsheet tool, the difference is even more clear. A_ Columns B:C use RANK() on Data, with the respective Values taken directly from the same Data range. Besides valid values within Data, there are also "a", "1" (potential valid value, but not within the range of values in Data) and an empty cell. B_ Columns F:G use RANK.AVG(). C_ Column K is a set of possible values for the Value argument for RANK(). D_ Columns L:M use column K as Value for RANK(), with Data being A3:A7. E_ Columns O:P use RANK.AVG(). F_ Columns S:T is similar to "D_" but with duplicate values in Data, A3:A10. G_ Columns V:W use RANK.AVG(). Now I'll try with each relevant item from comment 0. Let's hope its enough explanation. 0.n > self explanatory. 1. For Values 1 and 10 in attachment 186403 [details], the result should be #N/A. 2. In attachment 186403 [details], when Type is 1 and Value is between 1 and 10 (but not them), the result (since LO 4.0) is "-1". It should had been #N/A. This is different than with any other spreadsheet tool, and different than L0 < 4.0. I couldn't find any reference to this change in the Release Notes. This (distinct) behavior can affect additional calculations, so I would had expected some documentation (including the reasoning, especially when it is different than other spreadsheet tools). 3. Similar case as point 2, but with Type = 0. The result (positive numbers) is simply incorrect, and worse than point 2. This should had been #N/A. 4. Embarrassing "Oops!" for me. I apologize for describing this incorrectly in comment 0. It should had been "RANK.AVG" instead of "RANK.EQ" in this point – the latter is equivalent to RANK(). I used RANK.AVG() instead of RANK() as used in prior points, and the result was even worse than with RANK(). In attachment 186403 [details], see columns V:W (in addition to some others). The maximum rank should had been 7. Here we not only have incorrect positive results where there shouldn't be (as in point 3) but the results can be greater than 7.
(In reply to ady from comment #3) > 2. In attachment 186403 [details], when Type is 1 and Value is between 1 and > 10 (but not them), the result (since LO 4.0) is "-1". It should had been Correction. Between 2 and 9, but not them.
(In reply to ady from comment #3) > D_ Columns L:M use column K as Value for RANK(), with Data being A3:A7. > > E_ Columns O:P use RANK.AVG(). Sorry again. O:P is is similar to "D_" but with duplicate values in Data, A3:A10. > > F_ Columns S:T is similar to "D_" but with duplicate values in Data, A3:A10. S:T is like "D_" with RANK.AVG(). > > G_ Columns V:W use RANK.AVG(). V:w is with RANK.AVG() with duplicate values, A3:A10. At this point, with so many mistakes I made in my descriptions, I should probably just close this report and start a new one without the mistakes. @Buovjaga (Ilmari), please advise if you agree that I should just do that.
(In reply to ady from comment #5) > At this point, with so many mistakes I made in my descriptions, I should > probably just close this report and start a new one without the mistakes. > > @Buovjaga (Ilmari), please advise if you agree that I should just do that. Yes, it's fine to close. Then we could show it to Eike. Thanks for the efforts.
(In reply to Buovjaga from comment #6) > (In reply to ady from comment #5) > > At this point, with so many mistakes I made in my descriptions, I should > > probably just close this report and start a new one without the mistakes. > > > > @Buovjaga (Ilmari), please advise if you agree that I should just do that. > > Yes, it's fine to close. New bug 154627 replacing this one. Closing this as INVALID. Please change it to whatever fits better if needed.