+ Reply to Thread
Results 1 to 8 of 8

identifying varying data and rank formula

  1. #1
    Registered User
    Join Date
    12-09-2008
    Location
    Australia
    Posts
    27

    identifying varying data and rank formula

    i am trying to work out how to use the rank formula to rank numbers in column B and keep them in unison with Column A.
    So Column A has say 5 1's with column B having different scores then continuing under 1 in A is 2 and so on is there a way to continue the ranking formula without manually changing the cell ranges ??

    so =rank(B1,$B$1:$B$7,1) but can i do that if A =1 and then A=2 etc ?
    so if A=1,rank(B1,$B$1:$B$7,1)

    thanks heaps
    Attached Files Attached Files
    Last edited by percivaltroy; 09-18-2009 at 09:47 AM.

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: identifying varying data and rank formula

    To rank within each category in column A try this formula in C1 copied down

    =SUMPRODUCT((A1=A$1:A$24)*(B1>B$1:B$24))+1

    ...or in Excel 2007 only you can use COUNTIFS, i.e.

    =COUNTIFS(A:A,A1,B:B,"<"&B1)+1
    Last edited by daddylonglegs; 09-17-2009 at 08:00 PM.

  3. #3
    Registered User
    Join Date
    12-09-2008
    Location
    Australia
    Posts
    27

    Re: identifying varying data and rank formula

    daddylonglegs,
    looks good thanks so much !
    just 1 more thing - how can i get the inverse rankings with that formula?
    generally determined by 1 or 0 but im not sure with how you have done it.

    thanks again
    Last edited by percivaltroy; 09-17-2009 at 11:47 PM.

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: identifying varying data and rank formula

    ...how can i get the inverse rankings with that formula?
    in either case (SUMPRODUCT / COUNTIFS) change the operator from > to <

  5. #5
    Registered User
    Join Date
    12-09-2008
    Location
    Australia
    Posts
    27

    Re: identifying varying data and rank formula

    donkeyoye,

    thank you also, greatly appreciated!

  6. #6
    Registered User
    Join Date
    12-09-2008
    Location
    Australia
    Posts
    27

    Re: identifying varying data and rank formula

    guys running what you said was perfect but to my dismay i then come up with a problem where further down column A the numbers 1,2 etc reappear so then that formula runs into the error that it takes into account the above data.

    could i possibly include say another column C that includes words and do these words always have to be the same?
    and what about if 2 words start with first same letter eg mother and the other was maiden etc?

    thanks again
    Attached Files Attached Files

  7. #7
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: identifying varying data and rank formula

    A COUNTIFS function can have numerous criteria tests so yes,

    E1: =COUNTIFS(A:A,A1,B:B,"<"&B1,C:C,C1)+1

    (on an aside although COUNTIFS is pretty efficient I wouldn't advise using entire column references unless really necessary)

    Re: partial matches - you can use Wildcards in COUNTIFS but we'd need more info - I'm not sure I follow exactly, dll might though!

  8. #8
    Registered User
    Join Date
    12-09-2008
    Location
    Australia
    Posts
    27

    Re: identifying varying data and rank formula

    donkeyote,
    no by testing from my page im pretty sure that will work perfectly.
    once again i thank you very much

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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