MS Excel and 'league' tables
MS Excel and 'league' tables
Author
Discussion

UMAR B

Original Poster:

1,484 posts

298 months

Saturday 19th April 2003
quotequote all
Hello People

I need a bit of help with MS Excel. What i want to do is make a league table for all the salesmen we have. just a basic table that shows who is 1st, who is 2nd, etc, i have tried everything i know but can'f figure it out

Can anybody help?

Umar B

Big Al.

69,336 posts

289 months

Saturday 19th April 2003
quotequote all
Hi Umar,
Have you tried just doing a table of rows and columns?

You can then do a sort on say the sales column this will then sort into either ascending or descending order.

If you need any help off line, mail me. I'm not an expert but have used Excel a fair amount over the years.

UMAR B

Original Poster:

1,484 posts

298 months

Saturday 19th April 2003
quotequote all
Hello Al

I have another table which collates all the information, (in this case AMOUNT OF SALES, WARRANTY AND FINANCE) i want to use that table to automatically produce the results on another table. (i know its a bit dificult to explain on here )

The method allow me to not use the 'sort by' function.

The silly thing is have done it before, but is was some time ago and i have a memory like a seive

Big Al.

69,336 posts

289 months

Saturday 19th April 2003
quotequote all
Hi again Umar,
If its not sensitive information, mail me the spread sheet and perhaps we can work it out together.

UMAR B

Original Poster:

1,484 posts

298 months

Saturday 19th April 2003
quotequote all

Big Al. said: Hi again Umar,
If its not sensitive information, mail me the spread sheet and perhaps we can work it out together.


OK, if you mail me through my profile (as i can't send attachments) i mail you back

Umar

hut49

3,544 posts

293 months

Saturday 19th April 2003
quotequote all
Set up the column headers e.g. "Name"; "Sales this Month"; "Sales Cumulative" etc. Enter the data for each Name. Select all the rows including your Header row (not just the column that you want to sort). Go to "DATA" on the menu, select "SORT", use drop down menu to select the column with the data you want to sort, use the radio button to chose descending so the salesperson with the highest sales is at the top, use the radio button to select "Header Row" - press enter, SORTED! Pour yourself a beer and then think about ways you can add targets and performance measures!

Email me if you need help,
Hutch

Big Al.

69,336 posts

289 months

Saturday 19th April 2003
quotequote all
You have mail

Chrisgr31

14,298 posts

286 months

Saturday 19th April 2003
quotequote all
If my reading is right you want the spreadsheet to autosort, so that every week/month whatever you input the sales info, and then it sorts them automatically?

My guess is that you need to use a Macro, but iots so long since I used macros I can't remember where to start!

Might have a look later if you haven't got an answer.

UMAR B

Original Poster:

1,484 posts

298 months

Saturday 19th April 2003
quotequote all
You guys are excellent!

Big Al/Hut49 is was you two who jogged my memory, its all sorted now

One last question. what happens if there are joint scores, i.e if two salesman both have e.g. 20 cars sold? How can i show joint scores e.g Equal 2nd?


ChrisGR31 I forgot about the macros so thank you aswell for reminding me

Ahhhhh the power of PH

I feel a bonus (not a boner) coming on!

Regards

Umar


>> Edited by UMAR B on Saturday 19th April 12:52

hut49

3,544 posts

293 months

Saturday 19th April 2003
quotequote all
Use the RANK function:

Enter the formula: =RANK(number,ref,order) into a cell in the row for salesperson 1.

In the formula above:

'number' is the cell ID (eg B2) for the value that you want to rank the salespersons on (e.g. 20 cars sold in your example)
'ref' is the range of cell IDs that you want to include in the ranking exercise and is expressed eg B2:B10
'order' is how you want to organise the ranking ie use a "0" if you want to rank lowest to highest and a non-zero number if you want to rank highest down to lowest.

So the entry for the cell in row 2 might look like

=RANK(B2,B2:B10,1)

Then copy this cell and paste it down the column for each salesperson.

This would give you each salespersons rank and if there were equal scores for one or more salespersons then they would get the same ranking and the ranking would begin after them with the appropriate gap in numbering.

If you can't make it work - email me and I will email you a mini example.

Good luck

Hutch