Excel for Statistics > Excel RANK function (Excel 2013)
Watch the video or read the steps below:
Excel RANK function: Overview
Excel gives you plenty of choices for finding rank:
- The Data Analysis Toolpak.
- The RANK.EQ function (this is equivalent to the old RANK function, which is now obsolete).
- The RANK.AVG function.
The difference between RANK.EQ and RANK.AVG is subtle, and it concerns duplicate ranks. For example, let’s say you had four students with GPAs of 4.0, 3.5, 3.5, and 3.0. RANK.EQ would return the following ranks:
4.0 – 1
3.5 – 2
3.5 – 2
3.0 – 4
Note that the third rank is skipped and the 3.5s both rank at 2. RANK.AVG averages the ranks — which are actually 2 and 3 in this case and would return the average of the 2 ranks (2 and 3):
4.0 – 1
3.5 – 2.5
3.5 – 2.5
3.0 – 4
The inputs for the two rank functions are exactly the same.
How to Use the Excel RANK function: RANK.EQ and RANK.AVG
Step 1: Type your data into a single column.
Step 2: Click the cell immediately to the right of the first data point you want to find a rank for. For example, if you entered data into cells B1:B5, click cell C1.
Step 3: Type “=RANK(“. Excel will give you a dropbox with three options. Double click the option you want (RANK.EQ or RANK.AVG). If you are unfamiliar with entering formulas, click the insert function button (fx) on the toolbar to bring up a formula box where you get detailed hints about how to complete the formula. If you bring up the formula box, skip Step 4.
Step 4: Fill in the rest of the formula, which is =RANK.EQ(data item, data set, descending/ascending order). For example, =RANK.EQ(B2,$B$2:$B$11,0) tells Excel that you want to rank the item in B2 against the data set located in cells B2:B11.
Step 5: Press “Enter.”
How to Use the Excel RANK function: Data Analysis
Step 1: Click the “Data” tab and then click “Data Analysis.”
Step 2: Click “Rank and Percentile” and then click “OK.”
Step 3: Click in the Input Range box and then type the location of your data set (i.e. B1:B99)
Step 4: Click either the columns or rows radio button, depending on how your data is formatted. The most common format is columns. Check the “Labels in first row” box if you have column headers.
Step 5: Select an output range. For example, select an area immediately to the right of your input data.
Step 6: Click “OK.”
Check out our YouTube channel for more Excel help and tips!------------------------------------------------------------------------------
Need help with a homework or test question? With Chegg Study, you can get step-by-step solutions to your questions from an expert in the field. If you rather get 1:1 study help, Chegg Tutors offers 30 minutes of free tutoring to new users, so you can try them out before committing to a subscription.
If you prefer an online interactive environment to learn R and statistics, this free R Tutorial by Datacamp is a great way to get started. If you're are somewhat comfortable with R and are interested in going deeper into Statistics, try this Statistics with R track.
Comments? Need to post a correction? Please post a comment on our Facebook page.