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!
Hi @keme1
You probably didn’t see my latest comment, where I come to conclusion that it’s logical that rank is among existing values… And I found solution to calculate eventual rank
Though you got me back thinking and testing your solution in two columns also, only to find out that both solutions don’t calculate properly :). And even though it seems logical they should, but they don’t.
You can check
Rank-err.ods (84.6 KB)
where I’ve column I with my calculation, column J with rank of column D only to find out if H & I columns actually calculate properly, but they were both wrong in some examples as you can see in columns K
So back to looking for proper formula.
No …It would now be time for you to explain which values you want to classify statistically, instead of throwing something on top of already existing crazy constructs
I would like to find eventual rank (of 2year average) based on yearly averages.
And I double check =COUNTIF($D$1:$D$315, ">" & $E2) + 1 formula actually gives correct results (one could call it EventualRANK).
Earlier when I was checking/counting manually I’ve automatcially assumed that if say value is larger than value from rank 88th it should be 87th, but that is wrong I should check if my value to be ranked is smaller than 87th and if so it’s rank is 88th.
Solution based on @keme1 gave some oscillations on results in some cases. I didn’t go check why exactly, as COUNTIF is easier for me with no extra column needed.
Rank-err.ods (89.9 KB)
I can’t check why exactly, as I do not see anything that makes sense to label as “oscillation” within the sample dataset. I can suggest a likely reason why, though:
My suggested solution tries to find the “nearest match”. If the value is exactly in the middle between two entries in the list, they are equally close and the choice by logic is arbitrary, However, the selection should be consistent because the sequence of operations establishes a priority direction.
It appears as if you deliver a deliberately unclear description. It may be easier to understand the issue if you explain where your “some cases” can be found, and perhaps describe in more explicit terms what “some oscillations” are: