- Applies to: Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel for the web, Excel 2019, Excel 2016, Excel 2019 for Mac, Excel 2013, Excel 2010, Excel 2007, Excel 2016 for Mac, Excel for Mac 2011, Excel Starter 2010.
In this post, you will learn why the RANK function will be a useful asset to you and all about using the RANK function effectively. It will explore the meaning of this function, its formula syntax, some important conditions to remember when using the RANK function, and also an example to test it for yourself at your own leisure.
Summary of the RANK Function
The RANK function simply captures the rank of a number in a group of numbers. More specifically, the rank of a number refers to its size compared to the other values in a given list. (For example, if you actually sorted the list then the number’s rank would merely be its place.)
Important: This function has been updated with one or several new functions that might supply enhanced accuracy and whose names more effectively highlight their usage. Despite this function still being accessible for backward compatibility, you should prioritise employing the new functions from this point onwards, since this function could be exempt from newer versions of Excel.
Syntax of the RANK Function
The RANK function syntax contains the specific arguments (the first two arguments are vital for using the RANK function effectively):
- Number – Required. A particular number’s rank that you are looking for.
- Ref – Required. A selection of, or a reference to, a list of numbers. Non-numeric values in ref are omitted.
- Order – Optional. A number elaborating how to rank number. If order is 0 (zero) or skipped, Microsoft Excel ranks number like ref was a list arranged in descending order. If order is any nonzero value, Microsoft Excel ranks number just like ref was a list formatted in ascending order.
- RANK assigns duplicate numbers the same rank. However, the existence of duplicate numbers determines the ranks of following numbers. For instance, in a list containing integers sorted in ascending order, if the number 10 recurs twice and has a rank of 5, then 11 would be assigned rank of 7 (no number would include a rank of 6).
- For some purposes, one may prefer to use a definition of rank that considers ties. This is crucial in using the RANK function effectively and obtaining high quality results for your work. In the previous example, one would seek an amended rank of 5.5 for the number 10. This can be achieved by inserting this specific correction factor to the value yielded by RANK. This correction factor is relevant both for the case where rank is programmed in descending order (order = 0 or omitted) or ascending order (order = nonzero value). Correction factor for tied ranks=[COUNT(ref) + 1 – RANK(number, ref, 0) – RANK(number, ref, 1)]/2. In the following example, RANK(A2,A1:A5,1) equals 3. The correction factor is (5 + 1 – 2 – 3)/2 = 0.5 and the revised rank that takes ties into account is 3 + 0.5 = 3.5. If number occurs only once in ref, the correction factor will be 0, since RANK would not have to be adjusted for a tie.
Copy the sample data in the following table, and paste it into cell A1 of a brand new Excel worksheet. For formulas to display results, choose them, press F2, and then press Enter. If you have to at any point, then you can extend the column widths to have a full picture of the whole dataset.
|=RANK(A3,A2:A6,1)||Rank of 3.5 in the list above (3)||3|
|=RANK(A2,A2:A6,1)||Rank of 7 in the list above (5)||5|