# Ranking for spearemnas rank formulae

#### killdeathandblood

##### New Member
Hi im doing GCSE Mathematics and i need to use spearemans rank several times. I tried aformulae for it but it didnt work so i ended up doing it myself but this isnt practical i really need a formuale that will work. Here is ifomation on what happened when i tried this formulae.

formulae : =IF(ISNUMBER(A3),AVERAGE(RANK(A3,\$A\$3:\$A\$14),RANK(A3,\$A\$3:\$A\$14)+COUNTIF(\$A\$3:\$A\$14,A3)-1),"")

what happened :
Engine Size Milleage Rank for Engine size
1.4 14000 8.5
2.5 20000 1
1.6 30000 5.5
1 37000 11.5
1.6 33000 5.5
1.8 24000 3
2.3 30000 2
1.4 51000 8.5
1.6 24000 5.5
1.6 42000 5.5
1.4 33000 8.5
1 51000 11.5
1.1 43000 10
2 25000 #N/A
0.9 45000 #N/A
1.1 90000 10
1.8 74000 3
2 65000 #N/A
1.2 47000 #N/A
0.95 50000 #N/A
1.4 32000 8.5
1.8 71000 3
1.1 60000 10
1.1 40000 10
1.4 64000 8.5
2 14730 #N/A
1.8 58000 3
1.4 29000 8.5
2 55000 #N/A
1.2 13000 #N/A
1.8 27000 3
1.6 17000 5.5
2 66000 #N/A
1.8 52000 3
1.4 9500 8.5
1.8 2000 3
1.4 18500 8.5
2 22000 #N/A
2 6500 #N/A
1.6 50000 5.5
1.4 5000 8.5
2 30000 #N/A
1.3 50000 #N/A
1.4 21000 8.5
1 37000 11.5
1400 71000 #N/A
1.4 5000 8.5
1.1 52000 10
6.7 70000 #N/A
1.4 43000 8.5
1.8 52000 3

below is my actual data and my spearmans rank that i did by hand

Engine Size Milleage Rank for Engine size Rank For Milieage d d2
1.4 14000 7 33 26 676
2.5 20000 2 29 27 729
1.6 30000 6 22 16 256
1 37000 11 19 8 64
1.6 33000 6 20 14 196
1.8 24000 5 26 21 441
2.3 30000 3 22 19 361
1.4 51000 7 12 5 25
1.6 24000 6 26 20 400
1.6 42000 6 17 11 121
1.4 33000 7 20 13 169
1 51000 11 12 1 1
1.1 43000 10 16 6 36
2 25000 4 25 21 441
0.9 45000 13 15 2 4
1.1 90000 10 1 9 81
1.8 74000 5 2 3 9
2 65000 4 6 2 4
1.2 47000 9 14 5 25
0.95 50000 12 13 1 1
1.4 32000 7 21 4 16
1.8 71000 5 3 2 4
1.1 60000 10 8 2 4
1.1 40000 10 18 8 64
1.4 64000 7 7 0 0
2 14730 4 32 28 784
1.8 58000 5 9 4 16
1.4 29000 7 23 14 196
2 55000 4 10 6 36
1.2 13000 9 34 25 625
1.8 27000 5 24 19 361
1.6 17000 6 31 25 625
2 66000 4 5 1 1
1.8 52000 5 11 6 36
1.4 9500 7 35 28 784
1.8 2000 5 38 33 1089
1.4 18500 7 30 23 529
2 22000 4 27 23 529
2 6500 4 36 32 1024
1.6 50000 6 13 7 49
1.4 5000 7 37 30 900
2 30000 4 22 18 324
1.3 50000 8 13 5 25
1.4 21000 7 28 21 441
1 37000 11 19 8 64
1400 71000 7 3 4 16
1.4 5000 7 37 30 900
1.1 52000 10 11 1 1
6.7 70000 1 4 3 9
1.4 43000 7 18 11 121
1.8 52000 5 11 6 36
0.38239819 rs = 1-(6S136492/513-51) Sum of d2 13649

### Excel Facts

To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

##### MrExcel MVP
Not getting any #N/A's at all...
Spearman's Rank Correlation Coefficient.xls
ABCDEFGHIJ
1Rank OfMethod 2
2XYXYdd-squared
31.41400020.532-11.5132.25r-squared0.022210392
42.520000129-28784degrees of freedom31
51.6300001522.5-7.556.25Fs0.704161878
613700030.51812.5156.25p0.407813451
71.6330001519.5-4.520.25
81.8240001027.5-17.5306.25
92.330000222.5-20.5420.25
101.45100020.510.510100
111.6240001527.5-12.5156.25
121.6420001516-11
131.43300020.519.511
statspearman (3)

Using Method 2 described in: http://www.mrexcel.com/board2/viewtopic.php?t=45884

D3, copied down:

=IF(ISNUMBER(A3),AVERAGE(RANK(A3,\$A\$3:\$A\$35),RANK(A3,\$A\$3:\$A\$35)+COUNTIF(\$A\$3:\$A\$35,A3)-1),"")

E3, copied down:

=IF(ISNUMBER(B3),AVERAGE(RANK(B3,\$B\$3:\$B\$35),RANK(B3,\$B\$3:\$B\$35)+COUNTIF(\$B\$3:\$B\$35,B3)-1),"")

F3, copied down:

=D3-E3

G3, copied down:

=F3*F3

J3:

=RSQ(E3:E35,D3:D35)

J4:

=COUNT(D3:D35)-2

J5:

=J4*(J3/(1-J3))

J6:

=FDIST(J5,1,J4)

#### killdeathandblood

##### New Member
So why am i Getting #N/A

#### barry houdini

##### MrExcel MVP
Are you actually using the range?

\$A\$3:\$A\$14

Don't you need to extend that as you have more than 12 rows of data?

#### killdeathandblood

##### New Member
ahhh thank you i undersand

Replies
3
Views
403
Replies
7
Views
167
Replies
6
Views
994
Replies
4
Views
231
Replies
8
Views
227

1,171,588
Messages
5,876,322
Members
433,193
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back