Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

Thanks for clarifying your situation. I can't reproduce the problem with the RANK() function here in my limited testing (v4.1.0.4 under Crunchbang 11), however I may not be doing the same thing as you. Here is a screenshot of what I produced:

screenshot of split times

The "elapsed" column is the split between column B and D. The formula in F2 is =RANK(E2;E$2:E$4;1) and this is copied to F3 and F4. Does that look right?

Thanks for clarifying your situation. I can't reproduce the problem with the RANK() function here in my limited testing (v4.1.0.4 under Crunchbang 11), however I may not be doing the same thing as you. Here is a screenshot of what I produced:

screenshot of split timesscreenshot of split times

The "elapsed" split column is the split between column B and D. D minus B. The formula in F2 is =RANK(E2;E$2:E$4;1) and this is copied to F3 and F4. Does that look right?

Thanks for clarifying your situation. I can't reproduce the problem with the RANK() function here in my limited testing (v4.1.0.4 under Crunchbang 11), however I may not be doing the same thing as you. Here is a screenshot of what I produced:

screenshot of split times

The split column is column D minus B. The formula in F2 is =RANK(E2;E$2:E$4;1) and this is copied to F3 and F4. Does

EDIT: It appears that look right?

identical values are given the same rank, thus equal first placings are both denoted with "1", and then the ranking skips to 3. If there are then equal third placings both will be denoted with a "3", and then the ranking skips to 5, and so on. The LO help entry for this function does not mention how equal values are handled, however the AOO help page for the same function does indicate that there are "issues."

The suggested AOO workaround (append +COUNTIF(E$2:E2;E2)-1 in my example) does produce unique values based on order of appearance. For example, if I copy the three rows shown in my example, so there are equal firsts, equal seconds, and equal thirds, (and expand the range from E2:E4 to E2:E7) the results display as 1,3,5,1,3,5. If I then add the workaround to the formula (e.g., =RANK(E2;E$2:E$4;1)+COUNTIF(E$2:E2;E2)-1 for the first entry) then the results display as 1,3,5,2,4,6, which would seem better.

Thanks for clarifying your situation. I can't can now reproduce the problem with the RANK() function here in my limited testing (v4.1.0.4 using v4.1.0.4 under Crunchbang 11), however I may 11. Initially, I used whole numbers converted to times in the manner you described, but this did not be doing the same thing as you. reveal any problem with the function. Here is a screenshot of what I produced:this method:

screenshot of split times

The split column is column D minus B. The formula in F2 is =RANK(E2;E$2:E$4;1) and this is copied to F3 and F4.

EDIT: It appears that identical values are given the same rank, thus equal first placings are both denoted with "1", and then the ranking skips to 3. If there are then equal third placings both will be denoted with a "3", and then the ranking skips to 5, and so on. The LO help entry for this function does not mention how equal values are handled, however the AOO help page for the same function does indicate that there are "issues."

The suggested AOO workaround (append +COUNTIF(E$2:E2;E2)-1 in my example) does produce unique values based on order of appearance. For example, if I copy the three rows shown in my example, so there are equal firsts, equal seconds, and equal thirds, (and expand the range from E2:E4 to E2:E7) the results display as 1,3,5,1,3,5. If I then add the workaround to the formula (e.g., =RANK(E2;E$2:E$4;1)+COUNTIF(E$2:E2;E2)-1 for the first entry) then the results display as 1,3,5,2,4,6, which would seem better.

UPDATE: Using time values from your example file (refer comments below), in particular rows 26,32,33,38 and columns Z,AE,AG,AH, I can now reproduce the issue. In a new Calc file I manually keyed in the start (25k mark) and stop (30k mark) times, and used a simple subtraction of the start time from the stop time to obtain the elapsed time (5k split). A basic ranking of these elapsed times does not appear to give correct results as shown in this screenshot:

RANK function problem

The ranks IMO should show 1,1,4,3, yet for some reason the first two entries are determined to be different. We need to raise a bug and provide a simple example file like the one I have prepared. I will confirm this problem if you raise the bug. Please post the bug number back here in the format "fdo#123456" so that we can do this.

Thanks for clarifying your situation. I can now reproduce the problem with the RANK() function here using v4.1.0.4 under Crunchbang 11. Initially, I used whole numbers converted to times in the manner you described, but this did not reveal any problem with the function. Here is a screenshot of this method:

screenshot of split times

The split column is column D minus B. The formula in F2 is =RANK(E2;E$2:E$4;1) and this is copied to F3 and F4.

It appears that identical values are given the same rank, thus equal first placings are both denoted with "1", and then the ranking skips to 3. If there are then equal third placings both will be denoted with a "3", and then the ranking skips to 5, and so on. The LO help entry for this function does not mention how equal values are handled, however the AOO help page for the same function does indicate that there are "issues."

The suggested AOO workaround (append +COUNTIF(E$2:E2;E2)-1 in my example) does produce unique values based on order of appearance. For example, if I copy the three rows shown in my example, so there are equal firsts, equal seconds, and equal thirds, (and expand the range from E2:E4 to E2:E7) the results display as 1,3,5,1,3,5. If I then add the workaround to the formula (e.g., =RANK(E2;E$2:E$4;1)+COUNTIF(E$2:E2;E2)-1 for the first entry) then the results display as 1,3,5,2,4,6, which would seem better.

UPDATE: Using time values from your example file (refer comments below), in particular rows 26,32,33,38 and columns Z,AE,AG,AH, I can now reproduce the issue. In a new Calc file I manually keyed in the start (25k mark) and stop (30k mark) times, and used a simple subtraction of the start time from the stop time to obtain the elapsed time (5k split). A basic ranking of these elapsed times does not appear to give correct results as shown in this screenshot:

RANK function problem

The ranks IMO should show 1,1,4,3, yet for some reason the first two entries are determined to be different. We need to raise a bug and provide a simple example file like the one I have prepared. I will confirm this problem if you raise the bug. Please post the bug number back here in the format "fdo#123456" so that we can do this.

EDIT: Bug fdo#69068 has been raised.