I can’t find a reason that rank function in F column works in some rows and it doesn’t in others.
I’ve tried to round numbers to 2 decimal or 1 decimal, and then some work but others not. I tried to multiply reference and data set by 10 or 100, but dataset I couldn’t, unless making new column, but I would like to avoid that if possible, as I have many other columns in original table.
Rank-err.ods (72.9 KB)
This is because the most values in E have no equivalent in D.
=MATCH(E1;$D$1:$D$315;0) in column G shows the same pattern of #N/A values.
I thought that for rank you don’t need to have a match?
so if I have in A column 10, 15, 44,22, 50, and I would do rank say B1=17 on dataset I should get 3. Correct?
I’ve used rank in many other instances and never experienced this issue. But I see now that bellow row 162 it works, as those values exist in column D as it is yearly average.
Should I use than any other function to get proper ranking?
The rank does not consider a what-if-scenario where the value is inserted in the column.
If you can sort the scale in ascending order, =MATCH(E1;$D$1:$D$315;1) returns the position of the last value being smaller than or equal to E1.
If you can sort the scale in descending order, =MATCH(E1;$D$1:$D$315;-1) returns the position of the last value being greater than or equal to E1.
If you can’t sort the values, some kind of database operation could help.
AFAIK …RANK doesnt need exact matches?!
see this example, non of the values in the middle match exactly somewhere on the left, but the RANK-formula on the right works as expected:
| 1 | 1,5 | 15 |
|---|---|---|
| 2 | 2,5 | 14 |
| 3 | 3,5 | 13 |
| 4 | 4,5 | 12 |
| 5 | 5,5 | 11 |
| 6 | 6,5 | 10 |
| 7 | 7,5 | 9 |
| 8 | 8,5 | 8 |
| 9 | 9,5 | 7 |
| 10 | 10,5 | 6 |
| 11 | 11,5 | 5 |
| 12 | 12,5 | 4 |
| 13 | 13,5 | 3 |
| 14 | 14,5 | 2 |
| 15 | 15,5 | #WERT! |
but it is a complete mystery to me what you are doing with these crazy formulas in columns C, D and E?
I track how long each delivery lasted, and I rank them so I know which materials/suppliers were more efficient, I have other factors in the table, but I removed as much as I could. What you have displayed is also how I understood RANK function
no I can’t store values based on length as they are based on dates, and calculation between them. So how do you comment @karolus example? I also always understood RANK function to work this way. Though I’m not sure when exactly it broke this, but it was working several years fine, so it may have to do with RANK function update logic with updates to LO?
ah now I understand… but wouldn’t you then have to multiply in column C by the sine of the moon phase?
sorry … cannot resist!
I don’t get it with LO 24.2
| 1 | 1,5 | #N/A | =RANK($B1;$A$1:$A$15) |
|---|---|---|---|
| 2 | 2,5 | #N/A | =RANK($B2;$A$1:$A$15) |
| 3 | 3,5 | #N/A | =RANK($B3;$A$1:$A$15) |
| 4 | 4,5 | #N/A | =RANK($B4;$A$1:$A$15) |
| 5 | 5,5 | #N/A | =RANK($B5;$A$1:$A$15) |
| 6 | 6,5 | #N/A | =RANK($B6;$A$1:$A$15) |
| 7 | 7,5 | #N/A | =RANK($B7;$A$1:$A$15) |
| 8 | 8,5 | #N/A | =RANK($B8;$A$1:$A$15) |
| 9 | 9,5 | #N/A | =RANK($B9;$A$1:$A$15) |
| 10 | 10,5 | #N/A | =RANK($B10;$A$1:$A$15) |
| 11 | 11,5 | #N/A | =RANK($B11;$A$1:$A$15) |
| 12 | 12,5 | #N/A | =RANK($B12;$A$1:$A$15) |
| 13 | 13,5 | #N/A | =RANK($B13;$A$1:$A$15) |
| 14 | 14,5 | #N/A | =RANK($B14;$A$1:$A$15) |
| 15 | 15,5 | #N/A | =RANK($B15;$A$1:$A$15) |
youre right, my last sample was from LO7.4…
⇓ this is 24.8: ⇓
| 1 | 1,5 | #N/A |
|---|---|---|
| 2 | 2,5 | #N/A |
| 3 | 3,5 | #N/A |
| 4 | 4,5 | #N/A |
| 5 | 5,5 | #N/A |
| 6 | 6,5 | #N/A |
| 7 | 7,5 | #N/A |
| 8 | 8,5 | #N/A |
| 9 | 9,5 | #N/A |
| 10 | 10,5 | #N/A |
| 11 | 11,5 | #N/A |
| 12 | 12,5 | #N/A |
| 13 | 13,5 | #N/A |
| 14 | 14,5 | #N/A |
| 15 | 15,5 | #N/A |
The bug has been close as not a bug.
https://wiki.documentfoundation.org/Documentation/Calc_Functions/RANK
Warning:
Since LibreOffice 4.0 and until 7.5.2 (included), the function had an implementation error. For numeric values that are not listed within Data, the function returned numeric values, which was incorrect. Since LibreOffice 7.5.3, the implementation is correct, not only for values listed within Data. The “Examples” section includes results for the correct implementation, with a mark for cases in which an incorrect result would be obtained with the prior incorrect implementation.
OK so it seems it was meant to work this way, but was implemented wrongly.
I did however found another way achieve what I wanted with formula
E146=COUNTIF($C$1:$C$315, “>” & $D146) + 1
But now when I think logically, it makes sense that RANK function should work this way, as otherwise you may have many duplicates in the ranking with different numbers. ie in @karolus table 7.1, 7.2, 7.3 would all rank 9th. Actually I was calculating “potential rank” for 2 or 3 year average based on yearly averages. So in my case should be called PotentialRank or EventualRank. We could make new function in LO Calc, based on faulty previous RANK 
In my mind, a ranking is a placement among participants. A value which is not in the list, “did not participate”. I guess this is the reason for the changed working of the function (which to me looks like the correct approach to ranking, but for your purpose seems like a regression).
See my formula mockup in an edited version of your Rank-err.ods (67.7 KB). Is this the ranking result you are looking for? I can’t make a proper assessment of your purpose myself, as I am unsure of exactly what you are after.
I put the formulas aside in available columns, but I assume that they can also be in a separate sheet if you don’t want to add clutter in your source data. I can’t find a way to make it a manageable one-liner formula. Sorry!