ranking

S

Soz

Hello,
In column "A" I have peoples names and in column by I have their points.
It is possible for some people to have the same points. I would like to be
able to rank them and show their position in another column, ie. one with
most points top of the list and one with lowest points at the point. I have
tried a couple of formulae but without success, can someone help me? Do I
need a macro? Unfortunately not had any experiences with macros.
 
B

Bob Phillips

=RANK(B1,$B$1:$B$6)

Add this to a new column, change B6 to suit the end of your data, and then
you can sort on this column.
 
S

Stefi

Hi Soz,

column "A": names column "B": points
row 1:header
In cell C2 enter =IF(ISBLANK(B2);"";RANK(B2;$B:$B))

Fill down in column C until the expected maximum number of names, then sort
the table by column C! Re-sort the table after changes were made to it!

Regards,
Stefi


„Soz†ezt írta:
 
S

Soz

Hi Stefi and thanks for the reply but after reading my message I think it is
not clear what I would like. I already have this but what I would like to do
is to have in another column a list of the names with the one with the most
points at the top and the points he has in the next column.

Can you help?
Soz
 
A

Arvi Laanemets

Hi

I'd use ODBC query from your table, sorted by points - am best on separate
sheet.

To create an ODBC query, you have to define a static named range, which
includes all your data, and has single header row at top.

P.e. you have your table on sheet Sheet1, names in column A (A1="Names"),
and points in column B (B1="Points"). You have the table filled until row
100, but it's possible you have to add some rows in future - but probably
you will never have more than 1000 rows.

You create IInsert>Name>Define) a named range p.e. MyTable with Refers To
field
=Sheet1!$A$1:$B$1000
, and save the workbook;
From Data menu, you select 'Get External Data'>'New Database Query' (when
you don't have such selection, then insert MS Office install CD, and add
this feature);
Choose 'Excel Files' as Data Source type, and in next window point your
excel workbook as datasource. Select created named range as table, and
transfer columns Name and Points to right window. Next;
Select field Name, and set condition for it to 'Is Not Null'. Next;
Set 'Sort By' to Points. Next;
Finish. When you didn't activate leftmost upper cell of wanted result table
before uou started with creating query, then you have the option to do this
now. You also can set properties for query here. OK.



Arvi Laanemets
 
B

Bob Phillips

Can u not just sort them?

--
HTH

Bob Phillips

Soz said:
Hi Stefi and thanks for the reply but after reading my message I think it is
not clear what I would like. I already have this but what I would like to do
is to have in another column a list of the names with the one with the most
points at the top and the points he has in the next column.

Can you help?
Soz
Click to expand...
 
Top