Tom’s Tutorials For Excel: Ranking a List in Random Order

Tom’s Tutorials For Excel: Ranking a List in Random Order

Here’s an example of setting up your worksheet to randomly rank a list of items. In this first set of pictures, the list of names is randomly ranked three times, simply by hitting the F2 key on any cell in the worksheet, and pressing Enter.

In the next pair of side-by-side screen shots, Step 1 is to enter the RAND() function into the cells of a helper column. In Step 2, the formula for the ranking column, =RANK(B3,$B$3:$B$12), is entered into cell C3 and copied down to cell C12.

Finally, as seen in this last par of side-by-side screen shots that produce the final result, helper column B is optionally hidden.

Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,
7 comments on “Tom’s Tutorials For Excel: Ranking a List in Random Order
  1. Dear Sir !
    I M From Nepal . I M flow Your facebook page and I M daily Seen a your Excel formula . I Know very types excel formula of Excel but sir ! I have only one request . of Excel . In a class 7 subject are read but at last we calculation final result of Excel. But.
    How sir display for formula for it Calculation it mean any students have fail 2 subject display No. 2 or display “**” or any students have fail 3 subject display No. 3 or display “***” or any students have fail 4 subject display No. 4 or display “****” or any students have fail 5 subject display No. 5 or display “*****” It Remarks cell on so many one continue . Please develop for calculation solution above problem .
    It is my Heartly Request . sir plz sir! plz sir !!!!

    • Tom Urtis says:

      If you can please provide an example of your data, and the range that the data occupies, and what your expected result is based on that data, I can help you.

  2. sewasew says:

    I have a students roaster data. each student has 5subjects, 1st semester & 2nd semester results in columns B, C and D. 1st semester averages at C10, C18, C26 … how can I calculate their rank?

    • Tom Urtis says:

      It depends on how you expect this to look. Do you want the rankings to be on every 8th cell, or do you want to create a table elsewhere with no empty cells between the results, and rank those that way. Unsure of your expected results and how they should be displayed, and the full range where the list of students begins and extends to.

  3. Ger de Waard says:

    Dear Tom, how can i just determine / count the number of ties in an excel sheet. I am trying to develop a Kruskal-Wallis test in Excel and i got everything sorted except for the H-ties test statistic, which gives me a p-value for with ties. I have the formula but it requests the number of ties to correct the without H teststatistic with.
    Hospital_1 Hospital_2 Hospital_3
    6 28 10,5
    30 20 29
    2 33 16
    14,5 10,5 3
    9 32 21,5
    23 24,5 1
    12 8 7
    13 26 4,5
    21,5 31 27
    18 19 14,5
    4,5 24,5 17

Leave a Reply

Your email address will not be published. Required fields are marked *