Ranking Table in Excel

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

jamiepinsky101

Thread Starter
Joined
Jan 20, 2013
Messages
19
Hey Guys

I'm new to the forums and could use some help. Im trying to create a ranking system for my store's salesman. Basically we are judging 4 columns (sales, productivity,services and CLR sales). all data would be entered manually, or possibly from another sheet, but thats not the issue. What i would like my spreadsheet to do is when i enter data into one of the columns i would like it to sort the data and apply a rank to it (highest being 1, 2nd highest being 2..etc) and do it for each of the 4 columns. Any help would be greatly appreciated.

Thank you and look forward to your replies

Jamie

< edited by ETAF - moderator - to remove names >
Removed all sample spreadsheets as they contained names - replaced here with a dummy version - in case any one else has a solution
 

Attachments

etaf

Wayne
Moderator
Joined
Oct 2, 2003
Messages
65,457
quite a few ways to do that - RANK()

how will this work
when i enter data into one of the columns i would like it to sort the data
if you want a rank in descending order that can be done on another sheet or area using an array formula - BUT which of the four columns are you going to use for that order

i'll have a play with the example

this will do a ranking order
=OFFSET(Sheet1!A$2,MATCH(SMALL(Sheet1!C$2:C$5,ROW()-ROW(B$2)+1),Sheet1!C$2:C$5,0)-1,0)

Simpy use RANK

i have attached example and added the 4 columns for each area you wanted to measure
(sales, productivity,services and CLR sales)
 

jamiepinsky101

Thread Starter
Joined
Jan 20, 2013
Messages
19
example

when i enter all the sales data in the sales column, i would like it to sort the data and rank them from largest to smallest and then do the same for the next 3 columns

the rank would go into the columns labeled 25%
 

etaf

Wayne
Moderator
Joined
Oct 2, 2003
Messages
65,457
i edited my post
when i enter all the sales data in the sales column, i would like it to sort the data and rank them from largest to smallest and then do the same for the next 3 columns
so this will just re-order them 4 times

can you show how this would show in the spreadsheet
 

etaf

Wayne
Moderator
Joined
Oct 2, 2003
Messages
65,457
I have had a play, and do not know if this near to what you need

But I have ranked the different areas in columns P,Q,R and S
then I have added the rankings up - to get an overall ranking "points" in column T - you could apply a waiting here for the different rating - almost like the old/new formula 1 scoring, so 10point for a 1 , 8 for a 2 - -and apply different points to the different rankings

Then as i added up - the lowest would need to be ranked 1st - and as i want to recreate the list in the order of ranking, we need a unique ranking value so if 2 or more people have the same value - then we dont want them to be 3,3,3 we want 3,4,5 - Column U

Then In cells B28-K35 , i have formulas to sort the data , based on the Unique Ranking
in cells K28 - K35, I have used a non unique ranking - so if people see the data and they have the same results dont complain about being a different number

does that make sense at all

see what you think
 

jamiepinsky101

Thread Starter
Joined
Jan 20, 2013
Messages
19
thank you so much etaf

thats exactly what i needed it to do.
would it be possible to post the ranking results in the second sheet? using data from the first sheet?

also is it possible to assign a color based off the ranking? (example if there are 6 salesman in a dept, 1,2 would be green, 3,4 would be yellow and 5,6 would be red?
 

etaf

Wayne
Moderator
Joined
Oct 2, 2003
Messages
65,457
would it be possible to post the ranking results in the second sheet? using data from the first sheet?
yes

also is it possible to assign a color based off the ranking? (example if there are 6 salesman in a dept, 1,2 would be green, 3,4 would be yellow and 5,6 would be red?
yes

if i get a chance tonight , otherwise , tomorrow UK time zone

is everything else ok - before I make those changes - just want to make sure its all OK

we can use conditional formatting to test for 1or2 and change colour

if you had more salesman would this change ?
 

jamiepinsky101

Thread Starter
Joined
Jan 20, 2013
Messages
19
everything else is fine

the only thing i was curious about is how would i apply the ranking for all the salesman (not just for ht)?
i tried to copy and paste the names then the formula's but it not work.

also as we hire new staff how would i be able to add them in order to have them ranked as well?

the number of staff in a dept could change, we want to keep a ratio of staff in certain colors, example (7 staff would be 2 green, 3 yellow, 2 red) (5 staff could be 2,2,1)
 

jamiepinsky101

Thread Starter
Joined
Jan 20, 2013
Messages
19
one more thing i was noticing etaf

i was just doing some editing and i went to erase the columns marked 25%, to clean up the spreadsheet, but it seems as though your formula's are using that info, that info was entered manually, my goal through all of this was to just enter sales data and the rest would be done for me.

thanks

i
 

etaf

Wayne
Moderator
Joined
Oct 2, 2003
Messages
65,457
OK,

I can change to increase the range to be the most employees you will ever have plus a few
, but it seems as though your formula's are using that info
they are using a lookup number , so with that deleted the number changes

I have put something together quickly

So you need just

Dept
User
Sales
Productivity
Service
CLR

in the data sheet
and then the Ranking sheet provides all the info as I have

have a quick look at the attached - are the colours OK - I'll have to think about the ratio of numbers and colours a bit

I can add the dept into the ranking sheet

I can also increase the range so it can take any number of employees -
200 - 500 - 1000
what is the most ever likely , it doesn't matter just a bit of code
 

jamiepinsky101

Thread Starter
Joined
Jan 20, 2013
Messages
19
that looks really good etaf!!

thank you very much

realistically i dont think we'll ever go past 10-15 salesman per dept (thats really stretching it)

adding the dept to the sorting would be great as well.
last thing, would it be possible to add month to the sorting as well? so we can keep track of previous months?








i really appreciate your help.
 

etaf

Wayne
Moderator
Joined
Oct 2, 2003
Messages
65,457
I have started to clean-up- but before , i spend loads of time on this - i think i need to know - exactly what you want to include on the DATA entry sheet
and then I can work everything else out -

i started playing with up to 500 rows - and cleaned up the code - but just one column change could involve redoing the formulas - and noticed the hidden rows, which meant a bit of a re-write


i have updated - its has errors - but just delete the columns you dont need or add columns and what you need
 

etaf

Wayne
Moderator
Joined
Oct 2, 2003
Messages
65,457
OK - seen your reply

How many depts @15 salesman

can you show what you mean by Month ?
 

jamiepinsky101

Thread Starter
Joined
Jan 20, 2013
Messages
19
im uploading what i would like the data sheet to look like, that would be ideal.

if sorting my month is too much work, then forget it...
but i would need it to be sorted by dept.
 

jamiepinsky101

Thread Starter
Joined
Jan 20, 2013
Messages
19
if i were to add another salesman into the data sheet, would it automatically add him to the ranking sheet?
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top