Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

reverse ranking

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • reverse ranking

    I have a column of events,there are 7000 in my sheet.The scores for each contestant in the event are in col.2. In col 4 I have the rank printed for each contestant in each event in ascending order.I want to know how to print them in opposite order i.e highest in each event is ranked 1 etc into column 5.
    Attached Files

  • #2
    Re: reverse ranking

    from Excel Help
    RANK
    See Also

    Returns the rank of a number in a list of numbers. The rank of a number is its size relative to other values in a list. (If you were to sort the list, the rank of the number would be its position.)

    Syntax

    RANK(number,ref,order)

    Number is the number whose rank you want to find.

    Ref is an array of, or a reference to, a list of numbers. Nonnumeric values in ref are ignored.

    Order is a number specifying how to rank number.

    If order is 0 (zero) or omitted, Microsoft Excel ranks number as if ref were a list sorted in descending order.


    If order is any nonzero value, Microsoft Excel ranks number as if ref were a list sorted in ascending order.

    Remarks

    RANK gives duplicate numbers the same rank. However, the presence of duplicate numbers affects the ranks of subsequent numbers. For example, in a list of integers, if the number 10 appears twice and has a rank of 5, then 11 would have a rank of 7 (no number would have a rank of 6).

    Examples

    If A1:A5 contain the numbers 7, 3.5, 3.5, 1, and 2, respectively, then:

    RANK(A2,A1:A5,1) equals 3
    RANK(A1,A1:A5,1) equals 5
    D

    Better a bad day on the water than a good day in the office

    Comment


    • #3
      Re: reverse ranking

      Decided to have a quick play and see if i could get rid of the indirect for you...this works as long as the sheet is sorted by event number and replicates the uploaded file
      Code:
      =RANK($B2,OFFSET($B$1,MATCH($A2,$A$2:$A$26,0),
      0,MATCH($A2,$A$2:$A$26,1),1),1)
      and this does the same, but list the order the other way round
      Code:
      =RANK($B2,OFFSET($B$1,MATCH($A2,$A$2:$A$26,0),0,MATCH($A2,$A$2:$A$26,1),1),0)
      D

      Better a bad day on the water than a good day in the office

      Comment


      • #4
        Re: reverse ranking

        Thanks Dave,that works. There is only one problem,each event is descibed by
        3 columns,date,course and time. The sheet is sorted by these 3 fields.If I put in extra column,say something that increases the number by 1 when the time changes,then it should do away with all the indirect columns, I have loads of them in my sheet.Will the $a$26 have to be replaced by the last row in my sheet in the formula e.g $a$7004.

        Also I would like an extra column to to indicate if a contesttant is last,because ranked 4th will be last in some events and rank 14th in others.
        Sorry for so many questions,Dave.

        see attached
        Attached Files

        Comment


        • #5
          Re: reverse ranking

          Try the following...

          First, concatenate Columns A, B, C, and D:

          F2, copied down:

          =A2&"#"&B2&"#"&C2&"#"&D2

          Then, to rank lowest to highest...

          G2, copied down:

          =SUMPRODUCT(--(OFFSET(F$2:F$26,MATCH(F2,F$2:F$26,0)-1,,MATCH(F2,F$2:F$26))=F2),--(E2>OFFSET(E$2:E$26,MATCH(F2,F$2:F$26,0)-1,,MATCH(F2,F$2:F$26))))+1

          To rank highest to lowest, change E2> to E2<

          Hope this helps!

          Comment


          • #6
            Re: reverse ranking

            Thanks a lot,Domenic.
            Just the 2 questions again,
            Will the $a$26 have to be replaced by the last row in my sheet in the formula e.g $a$7004.
            Also I would like an extra column to to indicate if a contestant is last,because ranked 4th will be last in some events and rank 14th in others

            Comment


            • #7
              Re: reverse ranking

              Originally posted by pytelium
              Thanks a lot,Domenic.
              You're very welcome!

              Will the $a$26 have to be replaced by the last row in my sheet in the formula e.g $a$7004.
              Yes. In my example, all $26 references would be replaced with $7004...

              Also I would like an extra column to to indicate if a contestant is last,because ranked 4th will be last in some events and rank 14th in others
              Let K1:L4 contain the following table...

              Code:
              Event	Ranked Last
              1	14
              2	4
              3	4
              Then use the following formula to indicate which contestant is last...

              I2, copied down:

              =IF(G2=VLOOKUP(D2,$K$2:$L$4,2,0),"Last","")

              Adjust the table and references accordingly.

              Hope this helps!

              Comment


              • #8
                Re: reverse ranking

                Domenic

                sorry cant get my head around this last formula at all--putting in last.

                attached sheet,appreciate if you could help.
                Attached Files

                Comment


                • #9
                  Re: reverse ranking

                  Have a look at the attached file. You'll notice that I set up a table containing a list of events, along with their corresponding ranking for last place. Expand/amend this list according to your data and change the table reference within the formula.

                  Hope this helps!
                  Attached Files

                  Comment


                  • #10
                    Re: reverse ranking

                    Morning - looks like this has moved on a bit...so, my solution is similar to domenic in that it now uses the combined column F with the formula
                    Code:
                    =A2&"#"&B2&"#"&C2&"#"&D2
                    then to rank the various event I use the following in H
                    Code:
                    =RANK($E2,OFFSET($E$1,MATCH($F2,$F$2:$F$26,0),0,
                    MATCH($F2,$F$2:$F$26,1),1),0)
                    and to find the last place for each event I would use
                    Code:
                    =IF(H2=MAX(OFFSET($H$1,MATCH($F2,$F$2:$F$26,0),0,
                    MATCH($F2,$F$2:$F$26,1),1),0),"Last","")
                    in column I

                    Replace the $F$26 with the last row (I think this was $F$7004 in your example).

                    Let us know if you have any further problems or questions...

                    D

                    Better a bad day on the water than a good day in the office

                    Comment


                    • #11
                      Re: reverse ranking

                      Thanks Dave and Domenic,you have both been fantastic help.I used Domenics method for the ranking up and down and Dave's for the "last". If I might dare another question:if I wanted to label 2nd last etc in the final column as well as last,is it possible?

                      Comment


                      • #12
                        Re: reverse ranking

                        I guess you would flip the ranking on its head and then 1 = last, 2 = second last etc...end eventually get to 14th last which could also be viewed as first...
                        D

                        Better a bad day on the water than a good day in the office

                        Comment


                        • #13
                          Re: reverse ranking

                          Originally posted by DaveR (UK)
                          Morning - looks like this has moved on a bit...so, my solution is similar to domenic in that it now uses the combined column F with the formula
                          Code:
                          =A2&"#"&B2&"#"&C2&"#"&D2
                          then to rank the various event I use the following in H
                          Code:
                          =RANK($E2,OFFSET($E$1,MATCH($F2,$F$2:$F$26,0),0,
                          MATCH($F2,$F$2:$F$26,1),1),0)
                          From what I can see, the formula should return the desired results. But for some reason, it doesn't rank correctly. Am I missing something?

                          Comment


                          • #14
                            Re: reverse ranking

                            Not quite sure what you mean Domenic...as far as I could see it produced the correct rankings, which were the same as the ones that your forumla provided, what are the results you are getting ??
                            D

                            Better a bad day on the water than a good day in the office

                            Comment


                            • #15
                              Re: reverse ranking

                              I've attached a sample file showing the ranking using both formulas. With regards to your formula, it looks like the range provided by OFFSET for Event 2 is including both rows for Event 3. I don't understand why...
                              Attached Files

                              Comment

                              Trending

                              Collapse

                              There are no results that meet this criteria.

                              Working...
                              X