Ranking for spearemnas rank formulae

[killdeathandblood]

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

 

[Aladin Akyurek]

Not getting any #N/A's at all...

Spearman's Rank Correlation Coefficient.xls
	A	B	C	D	E	F	G	H	I	J
1				Rank Of					Method 2
2	X	Y		X	Y	d	d-squared
3	1.4	14000		20.5	32	-11.5	132.25		r-squared	0.022210392
4	2.5	20000		1	29	-28	784		degrees of freedom	31
5	1.6	30000		15	22.5	-7.5	56.25		Fs	0.704161878
6	1	37000		30.5	18	12.5	156.25		p	0.407813451
7	1.6	33000		15	19.5	-4.5	20.25
8	1.8	24000		10	27.5	-17.5	306.25
9	2.3	30000		2	22.5	-20.5	420.25
10	1.4	51000		20.5	10.5	10	100
11	1.6	24000		15	27.5	-12.5	156.25
12	1.6	42000		15	16	-1	1
13	1.4	33000		20.5	19.5	1	1
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]

So why am i Getting #N/A

 

[barry houdini]

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]

ahhh thank you i undersand