+ Reply to Thread
Results 1 to 5 of 5

Ranking Formula PLEASE HELP!!!

  1. #1
    Registered User
    Join Date
    12-12-2012
    Location
    California, USA
    MS-Off Ver
    Excel 2007
    Posts
    15

    Ranking Formula PLEASE HELP!!!

    Good Morning
    I have a formula that I use for ranking our sales reps based on their sales. The formula I have in place now, works, except that when there are multiple sales reps with the same sales amount they should all be the next ranked number and then resume ranking starting with the next ascending number. If that doesnt make sense, please see the example below, The last column is what it should be and the RANK column is what the formula is currently doing.

    Here is the formula: =RANK(N6,$N$6:$N$67,0)+COUNTIF($N$6:$N$67,N6)-1

    RSR Total RANK Should Be:
    Ron Valdez 228 1 1
    Kiran Patel 218 2 2
    Carl Misch 216 3 3
    Franc DiStefano 207 4 4
    Jason Young 203 5 5
    Ross Randolph 200 6 6
    Abi Dickerson 198 9 7
    Dean Goodman 198 9 7
    Paul O'Keefe 198 9 7
    Nick Mercado 189 10 10

  2. #2
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Ranking Formula PLEASE HELP!!!

    You'll get the results you expect if you just use the RANK function (or am I missing something?):

    =RANK(N6,$N$6:$N$67)
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    3,256

    Re: Ranking Formula PLEASE HELP!!!

    What if you use only the first part:
    =RANK(N6,$N$6:$N$67,0)
    Willem
    English is not my native language sorry for errors
    Please correct me if I'm completely wrong

  4. #4
    Registered User
    Join Date
    12-12-2012
    Location
    California, USA
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Ranking Formula PLEASE HELP!!!

    Thank you all so much! That worked perfect! I guess i was over thinking it!

  5. #5
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Ranking Formula PLEASE HELP!!!

    You're welcome. We appreciate the feedback!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1