Ask Your Question

Rank function misbehaving with time values [closed]

asked 2013-09-01 00:18:23 +0200

Clack Valve gravatar image

I'm trying to rank split times from a marathon, but the RANK() function does not give equal rank to equal times. I've tried feeding the function absolute times (e.g. 18:15) and it works fine, but not when fed a calculated time (e.g. 15k time minus 10k time). Any thoughts.

edit retag flag offensive reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp
close date 2015-11-07 23:22:50.491132


Please clarify what "a calculated time (e.g. 15k time minus 10k time)" means. Does "15k" refer to a specific time or a distance run? If you could provide an example of the time displayed (and underlying number) that results from this calculation that would be helpful e.g., "18:15:00" displayed = 0.7604166667. Also please indicate your operating system and LO version.

oweng gravatar imageoweng ( 2013-09-01 03:07:41 +0200 )edit

The results of the race give the time for each runner at 5 kilometres, 10 kilometres, etc. I therefore, for example, subtract the 10k time from the 15k to get the time taken for that 5k split. The elapsed time column is formatted H:MM:SS and the split time column as MM:SS. I then wish to use the RANK() function to compare runners for each 5k split. I'm on 4.05 with Windows 7 and also on Ubuntu 10.4.

Clack Valve gravatar imageClack Valve ( 2013-09-01 15:12:47 +0200 )edit

Later] I may have a clue: initially I laboriously typed times in as e.g. "1:24:18" and ranking worked correctly. I then added a column J allowing me to enter just 12418 and used the function IF(J2>0,TIME(TRUNC(J2/10000),MOD(TRUNC(J2/100),100),MOD(J2,100)),"") to get the time for column K. Ranking now gives different ranks to equal times.

Clack Valve gravatar imageClack Valve ( 2013-09-01 15:16:07 +0200 )edit

2 Answers

Sort by » oldest newest most voted

answered 2013-09-07 01:58:49 +0200

m.a.riosv gravatar image

Well, I think rounding is the key, we are dealing with the precision for decimal numbers and their binary representation.

Enter in a cell: 00:00:01
change the format to a number with 16 decimal positions: 0,0000115740740740

With operations without rounding we can have differences in the last numbers of the precision, that can change the RANK() between two numbers in appearance equals.

Round with seven decimal places must be enough to preserve up to hundredths.

image description

edit flag offensive delete link more


I think you and @horst are right. I can understand that rounding can be an issue in certain circumstances. Even so, I would expect a pair of date/time cells with "00:17:24", which equates to the number 0.0120833333333333 (presumably repeating) to be equatable. Under v4.1.0.4 the number 0.0120833333333333 if resulting from a date is not equatable with itself, while if it is entered manually (as a number) it is equatable. A lot of people are going to consider that a bug.

oweng gravatar imageoweng ( 2013-09-07 02:54:23 +0200 )edit

answered 2013-09-02 02:00:09 +0200

oweng gravatar image

updated 2013-09-08 00:15:35 +0200

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 ... (more)

edit flag offensive delete link more


That's fine if the split times are all different. The problem occurs when some of the splits are the same.

If, say, the best three splits are the same they should be ranked 1,1,1 with the next best ranked 4. This sometimes happens.

However, sometimes (in the same sheet) these splits are producing 1,2,3,4. I even have a mixture of correct and incorrect: 1,2,2 for three equal values. Weird

Clack Valve gravatar imageClack Valve ( 2013-09-04 01:49:55 +0200 )edit

Ok. Thanks for clarifying. I will update my answer.

oweng gravatar imageoweng ( 2013-09-05 08:51:31 +0200 )edit

Your 1,3,5,1,3,5 example is exactly what I want to happen. Rank() does not always do that. I have a screen shot of it misbehaving, but don't have enough karma to upload. It's here if you'd like to look. Blue highlight - behaving: 1,1,1,1,5. Red - misbehaving : 1,2,2.

Clack Valve gravatar imageClack Valve ( 2013-09-05 22:40:03 +0200 )edit

I have marked your question up so you should have enough karma now to post images. It will probably not be possible to determine what is happening without examining the ODS. All I can suggest is you double-check the ranges in your AH column. If the ranges look OK, then please raise a bug as it should not be doing that. Include your example file (redacted as necessary). If you do raise a bug, please post the number back here.

oweng gravatar imageoweng ( 2013-09-05 23:51:02 +0200 )edit

Thank you. I'll do that.

Clack Valve gravatar imageClack Valve ( 2013-09-06 00:13:43 +0200 )edit

Hi @Clack Valve, I think it is a rounding problem in the fraction of the second which is not displayed, but it counts for the ranking. I tried to reproduce it from your picture, but it was ok. Can you please confirm my formula col AH(5k) = AE(30k) - Z(25k).

horst gravatar imagehorst ( 2013-09-06 17:21:17 +0200 )edit


I don't think rounding is an issue as all times are entered to a resolution of 1 second (hidden column AD2 contains 14947 for 1:49:47 in column AE2).

AG2(5k time) = IF(OR(AE2="RTD",AE2=""),"",AE2-Z2) (allows for retired runners)


AH2(5k split rank) = IF(OR(AE2="RTD",AE2=""),"",RANK(AG2,AG$2:AG$70,1)) (there are 69 runners)

The file (redacted) is at

if you want the lot.


Clack Valve gravatar imageClack Valve ( 2013-09-06 23:34:07 +0200 )edit

Thanks for providing your file. I am going to update my answer again. I can reproduce this problem using a limited set of values from your file.

oweng gravatar imageoweng ( 2013-09-07 00:47:14 +0200 )edit

I have raised the bug with bugzilla. Bug ID is 69068.

Clack Valve gravatar imageClack Valve ( 2013-09-07 15:40:03 +0200 )edit

So for now I'll use AG2(5k time) = IF(OR(AE2="RTD",AE2=""),"",ROUND((AE2-Z2,7))

Thanks, guys.

Clack Valve gravatar imageClack Valve ( 2013-09-07 17:14:56 +0200 )edit

Question Tools

1 follower


Asked: 2013-09-01 00:18:23 +0200

Seen: 928 times

Last updated: Sep 08 '13