Welcome Guest
Log In | Register )
You last visited January 16, 2017, 9:40 pm
All times shown are
Eastern Time (GMT-5:00)

Excel Rank formula

Topic closed. 11 replies. Last post 2 years ago by theo1946.

Page 1 of 1
PrintE-mailLink
Avatar
GA
United States
Member #158157
August 13, 2014
99 Posts
Offline
Posted: April 12, 2015, 2:50 am - IP Logged
0123456789
300003914149

 

hello i check and couldn't find a thread. but can someone who understands excel better than me tell me what formula i could use to rank these?

i want to rack the numberline in the top 0-9 based on amounts of times hit. highest to lowest please. and if there is a tie, i was trying to put the higher number come first. but i dont know how to. any spare change for the poor? No Pity!

    CARBOB's avatar - FL LOTTERY_LOGO.png
    ORLANDO, FLORIDA
    United States
    Member #4924
    June 3, 2004
    5961 Posts
    Offline
    Posted: April 12, 2015, 4:46 am - IP Logged

    Don't know if this is what you need. I copied the numbers, then paste special, transpose, column I &column J,  then sort column J high to low. 

     

    714
    814
    69
    99
    03
    53
    10
    20
    30
    40

      SergeM's avatar - slow icon.png
      Economy class
      Belgium
      Member #123700
      February 27, 2012
      4035 Posts
      Offline
      Posted: April 12, 2015, 10:26 am - IP Logged
      0123456789
      300003914149

       

      hello i check and couldn't find a thread. but can someone who understands excel better than me tell me what formula i could use to rank these?

      i want to rack the numberline in the top 0-9 based on amounts of times hit. highest to lowest please. and if there is a tie, i was trying to put the higher number come first. but i dont know how to. any spare change for the poor? No Pity!

      Add a row for rank.

      Add column for numbers, add row for rank.
      Fill with data by rank, by number, else '-'.

      DIGIT0123456789
      FREQ.300003914149
      RANK5777753113
      NRS\RANK12345678910
      17-6-0-1---
      28-9-5-2---
      3------3---
      4------4---
      5----------
      6----------
      7----------
      8----------
      9----------
      10----------

        SergeM's avatar - slow icon.png
        Economy class
        Belgium
        Member #123700
        February 27, 2012
        4035 Posts
        Offline
        Posted: April 12, 2015, 2:08 pm - IP Logged
        NAF
        7, 1814
        2, 12, 15, 2713
        5, 812
        1, 1911
        17, 21, 35, 4010
        14, 24, 30, 34, 38, 42, 459
        3, 11, 13, 20, 25, 33, 418
        9, 29, 317
        4, 23, 26, 28, 37, 396
        6, 10, 22, 444
        32, 36, 433
        162

        Another model done with php.

          Avatar
          GA
          United States
          Member #158157
          August 13, 2014
          99 Posts
          Offline
          Posted: April 12, 2015, 4:06 pm - IP Logged
          NAF
          7, 1814
          2, 12, 15, 2713
          5, 812
          1, 1911
          17, 21, 35, 4010
          14, 24, 30, 34, 38, 42, 459
          3, 11, 13, 20, 25, 33, 418
          9, 29, 317
          4, 23, 26, 28, 37, 396
          6, 10, 22, 444
          32, 36, 433
          162

          Another model done with php.

          thank you very much for your response, but i am not sure i understand you. 

          I have number 0-9 listed example of placement: cell A1-J1 the first ten cells.

          Below it i have the amount of times each number hits in A2-J2.

           

          Now i want to rank  0 1 2 3 4 5 6 7 8 9. in the ranking order from most hit to least hit in A3-J3. 

          IN my example above it would come like 8 7 9 6 5 0 4 3 2 1. but i dont know how to rank it like that in excel or what code to start with in A3.

           

          and noticeably if two or more numbers have a tie, like 8 & 7 hit 14 times, the order would be highest first. so order 8 7.

          thank you very much i just wanted to explain more clearly.

            Avatar
            Florida
            United States
            Member #66575
            October 30, 2008
            3549 Posts
            Offline
            Posted: April 12, 2015, 4:16 pm - IP Logged

            Maybe you can alter / adjust this formula

            =(IF((H17>0),RANK(H17,$H$17:$Q$17),(RANK(H17,$H$17:$Q$17,1)+COUNTIF($H$17:$Q$17,">0")))+COUNTIF($H$17:H17,H17))-1

              lakerben's avatar - Lottery-061.jpg
              New Mexico
              United States
              Member #86099
              January 29, 2010
              11158 Posts
              Online
              Posted: April 12, 2015, 5:21 pm - IP Logged

              Maybe you can alter / adjust this formula

              =(IF((H17>0),RANK(H17,$H$17:$Q$17),(RANK(H17,$H$17:$Q$17,1)+COUNTIF($H$17:$Q$17,">0")))+COUNTIF($H$17:H17,H17))-1

              Nice work Carlig!

              US Flag

                Avatar
                GA
                United States
                Member #158157
                August 13, 2014
                99 Posts
                Offline
                Posted: April 13, 2015, 1:28 am - IP Logged

                As in this example, i just want to understand what does what in the formula. thanks again.

                 

                 

                  Avatar
                  GA
                  United States
                  Member #158157
                  August 13, 2014
                  99 Posts
                  Offline
                  Posted: April 13, 2015, 4:44 pm - IP Logged

                  As in this example, i just want to understand what does what in the formula. thanks again.

                   

                   

                  does anyone who knows excel know how to rank this example?

                    theo1946's avatar - spherewall
                    Bakersfield, Ca
                    United States
                    Member #89877
                    April 17, 2010
                    202 Posts
                    Offline
                    Posted: April 13, 2015, 6:15 pm - IP Logged

                    does anyone who knows excel know how to rank this example?

                    Did you try carlig's formula? it worked for me.

                      Avatar
                      GA
                      United States
                      Member #158157
                      August 13, 2014
                      99 Posts
                      Offline
                      Posted: April 13, 2015, 7:19 pm - IP Logged

                      it didn't work for me, i don't know which part of the formula to change to which particular cell, if someone could do it with the example ill see what does what.

                        theo1946's avatar - spherewall
                        Bakersfield, Ca
                        United States
                        Member #89877
                        April 17, 2010
                        202 Posts
                        Offline
                        Posted: April 14, 2015, 1:25 pm - IP Logged

                        it didn't work for me, i don't know which part of the formula to change to which particular cell, if someone could do it with the example ill see what does what.

                        =(IF((A2>0),RANK(A2,$A$2:$J$2),(RANK(A2,$A$2:$J$2,1)+COUNTIF($A$2:$J$2,">0")))+COUNTIF($A$2:A2,A2))-1

                        You will need to change the column in the formula or copy and paste this formula to cell a3 and then copy cell a3  in b3 thru j3 with the formula paste.

                        0123456789
                        300003914149
                        5789106312

                        4

                        By the way,

                        Thank You CarliG