You can modify the rank() function using the following one:
IF(COUNTIF(A$1:A$6,A1)=1, RANK(A1,A$1:A$6,1),
RANK(A1,A$1:A$6,1)+(COUNTIF(A$1:A$6,A1)-1)/2)
In this setting, the data range is A1:A6 (as the example you mentioned in your
email), and ranking is in the ascending order, e.g. smaller number gets smaller
rank. You can type this function in cell B1, and copy the formula to B1:B6. Let
me know if you need further info.
Jay
Graham D Smith wrote:
>
> I often use MS Excel to perform statistical analyses but I have been unable
> to use it for simple non-parametric procedures (e.g., Friedman's test,
> Mann-Whitney test) because the RANK() function does not deal with ties
> properly. For example, Excel would rank scores as follows;
>
> Score Rank
> 12 3
> 12 3
> 11 2
> 24 5
> 55 6
> 2 1
>
> However, the ranks for the scores of 12 should be 3.5. Does anyone know of
> an Add-In that provides appropriate ranks for tied scores?
>
> Dr Graham D. Smith
> Psychology Division
> School of Behavioural Studies
> University College Northampton
> Boughton Green Road
> Northampton
> NN2 7AL
>
> Tel (01604) 735500 Ext 2393
> Email [EMAIL PROTECTED]
