+ Reply to Thread
Results 1 to 8 of 8

Ranking by Category w/o Skipping Rank

  1. #1
    Registered User
    Join Date
    09-23-2016
    Location
    Los Angeles, CA
    MS-Off Ver
    Not Sure
    Posts
    1

    Ranking by Category w/o Skipping Rank

    Hello All,

    I have a big deliverable due at work today and I am having the most horrendous time with my sumproduct formula for rank. I need a formula that can provide a rank by category and not skip rank values.

    =SUMPRODUCT(--($E2=E$2:E$100),--($AV2<AV$2:AV$100)/COUNTIF(AV$2:AV$100,AV$2:AV$100&""))+1)

    E is my category column

    AV has the values I need to rank.

    I'm getting errors or just numbers that are VERY off.

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,409

    Re: Ranking by Category w/o Skipping Rank

    Hi aoballer and welcome to the forum,

    I think you are looking for this type of formula:

    =RANK(B2,$B$2:$B$21,0)+COUNTIF($B$2:B2,B2)-1

    Shown in:
    https://www.extendoffice.com/documen...e-rank.html#a1
    See attachment for example:
    Rank without dups.xlsx
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Valued Forum Contributor
    Join Date
    06-21-2013
    Location
    Bangladesh
    MS-Off Ver
    Excel 2000, Excel XP, Excel 2002, Excel 2007, Excel 2010, Excel 2013
    Posts
    975

    Re: Ranking by Category w/o Skipping Rank

    What are you trying to do with this part?
    Please Login or Register  to view this content.

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

    Re: Ranking by Category w/o Skipping Rank

    Is this what you had in mind?

    Data Range
    A
    B
    C
    1
    Cat1
    40
    1
    2
    Cat1
    50
    2
    3
    Cat1
    50
    2
    4
    Cat2
    30
    1
    5
    Cat2
    40
    2
    6
    Cat2
    70
    3
    7
    cat3
    20
    1
    8
    Cat3
    100
    2
    9
    Cat3
    100
    2
    10
    ------
    ------
    ------


    This array formula** entered in C1 and copied down:

    =SUM(IF(FREQUENCY(IF(A$1:A$9=A1,IF(B$1:B$9<B1,B$1:B$9)),B$1:B$9),1))+1

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

    I have the data sorted just to make it easier to see what results you'll get.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

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

    Re: Ranking by Category w/o Skipping Rank

    sanram...

    Do you ever sleep?

  6. #6
    Valued Forum Contributor
    Join Date
    06-21-2013
    Location
    Bangladesh
    MS-Off Ver
    Excel 2000, Excel XP, Excel 2002, Excel 2007, Excel 2010, Excel 2013
    Posts
    975

    Re: Ranking by Category w/o Skipping Rank

    Quote Originally Posted by Tony Valko View Post
    sanram...

    Do you ever sleep?
    Sleeping kills time.

  7. #7
    Registered User
    Join Date
    11-21-2013
    Location
    zimbabwe
    MS-Off Ver
    Excel 2003
    Posts
    93

    Re: Ranking by Category w/o Skipping Rank

    Quote Originally Posted by Tony Valko View Post
    Is this what you had in mind?

    Data Range
    A
    B
    C
    1
    Cat1
    40
    1
    2
    Cat1
    50
    2
    3
    Cat1
    50
    2
    4
    Cat2
    30
    1
    5
    Cat2
    40
    2
    6
    Cat2
    70
    3
    7
    cat3
    20
    1
    8
    Cat3
    100
    2
    9
    Cat3
    100
    2
    10
    ------
    ------
    ------


    This array formula** entered in C1 and copied down:

    =SUM(IF(FREQUENCY(IF(A$1:A$9=A1,IF(B$1:B$9<B1,B$1:B$9)),B$1:B$9),1))+1

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

    I have the data sorted just to make it easier to see what results you'll get.
    How about this ?
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    thanks for your reply

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

    Re: Ranking by Category w/o Skipping Rank

    Quote Originally Posted by soledad View Post
    How about this ?

    =1+SUMPRODUCT((A1=A$1:A$9)*(B1>B$1:B$9))
    That formula will skip ranks. The OP wants consecutive ranks with no skips. Like this...

    1...21
    1...21
    1...21
    1...87

    The OP wants...

    1...21...1
    1...21...1
    1...21...1
    1...87...2

    Your formula returns...

    1...21...1
    1...21...1
    1...21...1
    1...87...4

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 3
    Last Post: 02-08-2016, 06:55 PM
  2. Ranking values and ranking duplicates the same rank
    By 302arpks in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 03-05-2015, 01:50 PM
  3. Ranking Visible Cells with Ties without skipping Rank
    By Whoap in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-19-2014, 01:26 PM
  4. Ranking by category
    By bigmurn in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-03-2012, 04:02 PM
  5. RANK, duplicate ranking but no gaps in rank
    By arron laing in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-06-2012, 07:15 AM
  6. Replies: 6
    Last Post: 06-01-2010, 06:19 PM
  7. Ranking by Category - Part II
    By Ronald Hooper in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 01-24-2005, 03:10 PM

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