1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Ranking Table in Excel

Discussion in 'Business Applications' started by jamiepinsky101, Jan 20, 2013.

Thread Status:
Not open for further replies.
Advertisement
  1. jamiepinsky101

    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
     

    Attached Files:

  2. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,252
    First Name:
    Wayne
    quite a few ways to do that - RANK()

    how will this work
    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)
     
  3. jamiepinsky101

    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%
     
  4. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,252
    First Name:
    Wayne
    i edited my post
    so this will just re-order them 4 times

    can you show how this would show in the spreadsheet
     
  5. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,252
    First Name:
    Wayne
    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
     
  6. jamiepinsky101

    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?
     
  7. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,252
    First Name:
    Wayne
    yes

    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 ?
     
  8. jamiepinsky101

    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)
     
  9. jamiepinsky101

    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
     
  10. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,252
    First Name:
    Wayne
    OK,

    I can change to increase the range to be the most employees you will ever have plus a few
    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
     
  11. jamiepinsky101

    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.
     
  12. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,252
    First Name:
    Wayne
    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
     
  13. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,252
    First Name:
    Wayne
    OK - seen your reply

    How many depts @15 salesman

    can you show what you mean by Month ?
     
  14. jamiepinsky101

    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.
     
  15. jamiepinsky101

    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?
     
  16. Sponsor

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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1086140

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice