Comparing ranking

[pytelium]

I have 3 cols of data,at the moment H,N AND W.

JOHN 1 3.25
JOHN 2 8
JOHN 3 6
JOHN 4 2.75
JOHN 5 11
JOHN 6 15
MARY 1 8
MARY 2 5
MARY 3 5
MARY 4 2.75
MARY 5 11
MARY 6 17
MARY 7 13

N is the actual ranking. W is the predicted results,unranked. Lowest is
best in the predicted results,thus 2.75 is ranked 1st,3.25 is ranked 2nd
etc.

I want to produce another column which will show the numerical
difference
between the actual ranking and the predicted ranking,the first row
will be 1 -2 =-1.

I also want to show for each person in another extra column ,the
percentage of the total below each number in the predicted column. Thus
3.25 finished first, 5 out of 6 are below,so show 83%,on the second row
4/6 =67%.

 

[Pete]

Is it deliberate that the boys are listed first (and in order) followed
by the girls?

Using your example and assuming data starts in row 2 with headers in
row 1, enter the following in X2 and copy down for the boys:

=N2 - RANK(W2,W$2:W$7,1)

and in X8 down for the girls:

=N8 - RANK(W8,W$8:W$14,1)

Adjust the ranges to suit your data. It would help the second part if
you had two cells somewhere which contained the number of boys and the
number of girls - assume Z1 (= boys) = 6 and Z2 (= girls) = 7. Then you
can put this formula in Y2 and copy down for boys:

=(Z$1-N2)/Z$1 (format as percentage), and for girls in Y8 downwards:

=(Z$2-N8)/Z$2, formatted as percentage.

Hope this helps,

Pete