Ranking function problem

**JoeN** · 04-13-2013, 11:40 AM

Hi

I'm having a problem with the RANK.AVG function. For one list of data, it works as normal: any pieces of data that hold the same rank results in an average rank for both of them. However, when I try this on another set of data, it functions exactly the same as the standard RANK function in that no averaging takes place. This is extremely frustrating, I've been checking everything for around an hour and can see no reason why this would occur, any ideas?

**daddylonglegs** · 04-13-2013, 11:48 AM

Hello JoeN,

The results for RANK.AVG can be the same as those for RANK depending on the data to rank, e.g. if there are no repeated values, can you give an example - what numbers are you ranking, what results do you get?

[Also check to see that numbers don't have underlying decimals - RANK.AVG will consider the underlying values in the cells, not the displayed values......]

**JoeN** · 04-13-2013, 11:57 AM

Well there's 80 pieces of data which is why I didn't initially post. However, I do know for certain that there are repeated values. I also checked it in an online calculator for Spearman's Rank (which is what I'm trying to work out), which provides the same results as mine for the first set of data, and provides averages of ranks for repeated values in the second set. For some reason, this isn't the case in excel.

If the numbers are same, e.g. 3.7 and 3.7, excel will simply rank them as 1 and 2, instead of 1.5

**daddylonglegs** · 04-13-2013, 12:21 PM

Are you sure that there aren't decimal values that aren't displayed? For example your two 3.7s might actually be one 3.71 and one 3.72. If the cells are formatted to show one decimal place then they will both display 3.7 but the underlying values will be used by RANK.AVG and they will be ranked differently.

**JoeN** · 04-13-2013, 12:29 PM

Yes, all the decimal places are being displayed. I suppose there must be something deeper going wrong here, and seeing as I have another resource that can do it for me I wouldn't want to waste anymore of your time. Thanks for the quick responses

**daddylonglegs** · 04-13-2013, 01:55 PM

OK, no problem - I'm interested in why it doesn't work for you, though......any chance of posting some data showing what doesn't work?

**JoeN** · 04-13-2013, 02:27 PM

Sure (formula: =RANK.AVG(M2, M$2:M$81, 1)

10.16774725
15.72501801
1a)7.003190285
8.541730987
5.814551816
3.3446537
8.495420397
4.291447978
3.962128229
13.2088093
3.735720902
2.938149635
11.2071627
9.745806319
2a)3.720284038
6.55037563
7.893382731
10.51250386
6.884841
5.181640424
4.502418442
9.776680045
5.891736133
7.198723886
3a)2.078830915
5.619018215
7.636101677
4.806010085
4a)1.67232685
9.12833179
2.243490789
3.946691366
12.54502418
4.713388906
1.039415457
3.493876711
7.018627148
6.627559946
1.445919522
5.680765668
11.40269631
7.666975404
8.824740146
7.080374601
5.860862406
4.306884841
2b)3.720284038
3.056498919
5a)4.759699496
9.956776783
4.384069157
4.595039621
7.877945868
6.056396007
5.572707626
8.433672944
9.684058866
3.360090563
4.790573222
3.29834311
3b)2.078830915
3.58649789
1b)7.003190285
9.879592467
5.361737162
6.102706597
2.727179171
2.274364516
5.799114953
2.454461253
2.500771843
4b)1.67232685
5.768241227
2.094267778
4.15766183
5b)4.759699496
2.063394052
8.721827725
2.258927653
7.620664814

74
80
53
65
45
18
64
28
26
79
24
15
76
70
22
50
62
75
52
38
31
71
47
57
7
41
59
37
3
68
9
25
78
33
1
20
55
51
2
42
77
60
67
56
46
29
23
16
35
73
30
32
61
48
40
63
69
19
36
17
6
21
54
72
39
49
14
11
44
12
13
4
43
8
27
34
5
66
10
58

Sorry, not really sure how the formatting for tables worked. I hope that is clear enough.. (bolded = should have been averaged)