|
Re: 1st, 2nd, 3rd Place etc.....
I think you missed the "etc ...", Aladin.<bg>
--
Regards,
RD
---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Aladin Akyurek" <akyurek@xs4all.nl> wrote in message
news:41E0FE12.3040802@xs4all.nl...
> I suspect that you want to create a Top N list, with N set to 3...
>
> Let A2:B9 house, including the labels Name and Score:
>
> {"Name","Score";
> "dawn",23;
> "damon",23;
> "bob",25;
> "chris",22;
> "christine",25;
> "ian",32;"john",35}
>
> The foregoing just shows sample records, where each record consists of a
> row of two cells.
>
> In C2 enter & copy down:
>
> =RANK(B3,$B$3:$B$9)+COUNTIF($B$3:B3,B3)-1
>
> This calculates a unique rank based on the scores.
>
> In D1 enter: 3
>
> which indicates that you want a Top 3 list.
>
> In D2 enter: Top N
>
> which is just a label.
>
> In D3 enter & copy down:
>
>
=IF(ROW()-ROW(D$3)+1<=$D$1+$E$1,INDEX($A$3:$A$9,MATCH(ROW()-ROW(D$3)+1,$C$3:
$C$9,0)),"")
>
> This builds a Top N list of names. Note that this formula refers to $E$1
> that houses a formula.
>
> E1:
>
> =MAX(IF(INDEX(B3:B9,MATCH(D1,C3:C9,0))=B3:B9,C3:C9))-D1
>
> which must be confirmed with control+shift+enter instead of just usual
> enter.
>
> This formula calculates the ties of the Nth (3rd) value itself.
>
> In E2 enter: Associated Score
>
> which is just a label.
>
> In E3 enter & copy down:
>
> =IF(D3<>"",INDEX($B$3:$B$9,MATCH(ROW()-ROW(E$3)+1,$C$3:$C$9,0)),"")
>
> The results are for the sample data are:
>
> {"john",35;
> "ian",32;
> "bob",25;
> "christine",25}
>
> JohnT wrote:
> > I have an idea how to do this but it requires a series of
> > nested ifs and i'm sure there is a better way......in
> > column A i have a list of names, column B is their scores
> > and in C i would like to rank them in 1st, 2nd, 3rd etc....
> > any ideas????
> >
> > (thanks in advance)
|