Spearmans Rank

[dodgymanc]

Is there a formula or function on Excel that allows me to calculate Spearmans Rank Correlation Coefficient?

 

[jexcel3]

thanks for the info...and have a great weekend!!

 

[planetpj]

Aladin,
Following you pattern, I think instead of =IF(ISNUMBER(Q3), it should be the formula below.

E3:

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

Thanks

 

[Aladin Akyurek]

Aladin,
Following you pattern, I think instead of =IF(ISNUMBER(Q3), it should be the formula below.

E3:

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

Thanks

Yeah...

ISNUMBER(Q3) --> ISNUMBER(B3)

Who knows how Q3 has got there! A moderator could correct for that in order to avoid errors when people try to use the set up.

 

[JeffShaffer]

Here is an Excel UDF that I created for the Spearman Ranking Correlation Coefficient. Example, "=Spearman(C3:C12, D3:D12)"

Public Function Spearman(rangeX As Range, rangeY As Range) As Variant

' Spearman Ranking Correlation Coefficient - created by Jeffrey A. Shaffer
' = Spearman (Range of X variables, Range of Y variables)
' d refers to the difference in the rank orders
' 6 is a constant in the formula
' r = 1 - 6 * sum of (dSquared/N(NSquared-1))

If rangeX.Count <> rangeY.Count Then
MsgBox "Total number of X and Y variables does not match."
End If

Dim denominator As Integer
Dim numerator As Integer
Dim dSquared As Integer
dSquared = 0
denominator = rangeX.Count * ((rangeX.Count ^ 2) - 1)

For iCount = 1 To rangeX.Count
dSquared = dSquared + ((WorksheetFunction.Rank(rangeX(iCount), rangeX) - WorksheetFunction.Rank(rangeY(iCount), rangeY)) ^ 2)
Next iCount
numerator = dSquared * 6

Spearman = 1 - (numerator / denominator)
End Function

 

[jexcel3]

Thanks for the help!!!

john

 

[madhusudan]

hi i am new to this forum . I am not going to post a direct link to a resouce but still i am putting it as text as i have found 3 example spreadsheets
http://freespreadsheet.net/2015/04/spearman-rank-correlation-in-excel.html and yes this is mine blog

 

[kaps1997]

Is there a formula or function on Excel that allows me to calculate Spearmans Rank Correlation Coefficient?

ANYONE LOOKING FOR SPEARMAN'S RANK EXCEL, LOOK NO FURTHER

I have made one, works perfectly

https://www.dropbox.com/s/li58r2cmjv2n7ru/Spearmans Rank.xlsx?dl=0

Simply put your values in and it will give you a running coefficient and plot the graph