Ranking for spearemnas rank formulae

killdeathandblood

New Member
Joined
Oct 6, 2006
Messages
3
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

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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)
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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?
 

Forum statistics

Threads
1,136,272
Messages
5,674,757
Members
419,525
Latest member
helensesc

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top