Excel: League Standings....Pls Help 1

[Forri]

Hi

SO i have a worksheet having aa column with driver names and a column with a total score.

I would like to make a standings which automatically change when the new scores are added! Therefore i will have a name on the 1st posiion and antoher name on the second position!

how can i do this? is there a simple equation or something?

Thanks
Nick

 

[jonsi]

Try using the the RANK function.

 

[Forri]

Great Jonsi

now that will give me the rank...now i would like to make a list of names starting from the first positon till last according to this rank list!

How can this be done...sorry but tis is a one off project i have to do in excel...never worked with excel before (professionally at least)

Thanks
Ncik

 

[xlbo]

If you want the position against your base data then go for RANK (as Jonsi has said)
If, however, you want to create a seperate "Top 10" list or similar, have a look at the LARGE function in conjunction with the INDEX and MATCH functions

eg if scores on Sheet1 in col Z with driver name in col A then on sheet 2, in cell A2, enter 1
in B2 enter
=LARGE(Sheet1!$Z$2:$Z$1000,A2) - this will return the highest score

Now, to return the name, replace the above formula with

=INDEX(Sheet1!A:A,MATCH(LARGE(Sheet1!$Z$2:$Z$1000,A2),Sheet1!Z:Z,0))

This matches the scores column against the largest score and returns the matching driver's name

Rgds, Geoff

Yesterday it worked. Today it is not working. Windows is like that.

Please read FAQ222-2244 before you ask a question

 

[jonsi]

forgot to mention the following

If you want your list to RANK from the lowest to the highest (where the lowest score gets the highest rank) your formula should be =RANK(B1,$B$1:$B$10,1)

If you want it the other way round (where the highest score gets the highest rank your formula should be =RANK(B1,$B$1:$B$10,0)

you can now do a data sort on the Rank column in Ascending order

 

[xlbo]

oops - should've mentioned assumptions in my last post are:

Driver names in colA on sheet 1
Driver scores in colZ on sheet 1
data in rows 2:1000 with headers in row 1 (all on sheet1)

Rgds, Geoff

Yesterday it worked. Today it is not working. Windows is like that.

Please read FAQ222-2244 before you ask a question

 

[Forri]

Great Geoff worked brilliantly! Star for you..

The only thing that remains is that if the 4th psotion has a vale of 20 and the 5th positon has a value of 20 than the same name appears!

Well...

Tahnks anyways...great stuff cheers
Nick

 

[xlbo]

yeh - what I'd do with that is add a new column that compares scores and if they are equal, add 0.01 to one of them arbitrarily just to make a slight difference to the LARGE function

Rgds, Geoff

Yesterday it worked. Today it is not working. Windows is like that.

Please read FAQ222-2244 before you ask a question

 

[Forri]

Aah great idea! thanks