Excel Functions Tutorials
- Statistical Function in Excel
- AVERAGE Excel Function
- Average Formula in Excel
- AverageIF in Excel
- AVERAGEIFS Function in Excel
- CORREL Excel Function
- Correlation Matrix in Excel
- Correlation vs Covariance
- COUNT Excel Function
- Count Formula in Excel
- COUNTA Excel Function
- COUNTIF Excel Function
- COUNTIF Formula in Excel
- COUNTIFS Function in Excel
- COUNTIF with Multiple Criteria
- COUNTIF Examples
- FORECAST Excel Function
- Forecast Formula in Excel
- FREQUENCY Excel Function
- Frequency Formula in Excel
- GROWTH Excel Function
- Growth Formula in Excel
- LARGE Excel Function
- LINEST Excel Function
- Linear Regression in Excel
- Lognormal Distribution in Excel
- MAX Excel Function
- Max Excel Formula
- Mean vs Median
- MEDIAN Excel Function
- MEDIAN Formula in Excel
- MIN in Excel
- MODE Excel Function
- NORM.S.INV Function in Excel
- NORMDIST in Excel
- PERCENTILE Excel Function
- Percentile Formula in Excel
- Percentile Rank Formula
- Poisson Distribution in Excel
- P-Value in Excel
- QUARTILE Excel Function
- RANK Function in Excel
- SLOPE Function in Excel
- SMALL Function in Excel
- Standard Deviation in Excel
- Standard Deviation Formula in Excel
- TREND Function in Excel
- T-TEST in Excel
- Chi Square Test in Excel
- Variance vs Standard Deviation
- Weibull Distribution in Excel
- Financial Functions in Excel (17+)
- Logical Functions in Excel (15+)
- TEXT Functions in Excel (29+)
- Lookup Reference in Excel (44+)
- Maths Functions in Excel (52+)
- Date and Time Function in Excel (22+)
- Information Functions in Excel (5+)
- Excel Charts (48+)
- Excel Tools (98+)
- Excel Tips (178+)
- VBA (162+)
RANK Function in Excel (Table of Contents)
Excel RANK Function
Rank Function is a built-in function in MS Excel. It falls under the category of STATISTICAL functions in Excel. The function is used to get the rank of a given number from the list of numbers.
RANK Formula in Excel
Below is the RANK Formula in Excel
The function has three arguments out of which first two are required and the last one is optional.
- number = A number for which the rank is to be found out.
- ref = the range of numbers w.r.t which the rank of a given number is to be found
- order = the order (ascending or descending) in which the numbers should be ranked. 0 = Descending order; 1 = Ascending order. If the order is omitted, the default value is zero, i.e. descending order.
How to Use RANK Function in Excel?
The given function is a Worksheet (WS) function. As a Worksheet function, it can be entered as a part of the formula in a worksheet cell. Refer to the examples explained in this article as you proceed to learn more.
Let’s look at the examples given below. Each example covers a different use case implemented using the RANK function in Excel.
Example #1 – Find Steve’s Rank in Maths
- In the above example, Marks of the Students in Maths and Science subject are shown. To find Steve’s rank in Maths, we would use the Excel RANK function.
- The first parameter is the input value of which the rank is to be calculated. Here, B6 represents Steve’s marks in Maths i.e. 68.
- The 2nd parameter, the total cells containing Maths marks are from B1 to B11. So, the range is B1:B11 that comprises of all students’ marks in Maths.
- The highest value represents the rank 1 and the lowest representing the last rank. So, the 3rd parameter is skipped, and Excel treats it as 0, the default order as descending
- RANK (B6, B1:B11) returns the rank of the student named Steve in subject Maths as 8, 50 being the lowest (B2) and 99 (B10) being the highest marks in Maths subject.
Example #2 – Find the Lowest Runtime from the Race Record
- In the above example, different Time values are given. We have to find the lowest runtime from the given records.
- The time values range from cell B1:B6. So, the cell range is named as a table and given the name ‘mytime’. It acts as an alias for B1:B6.
- Unlike example #1, here, the lowest runtime indicates the 1st rank and the highest runtime indicates the last rank. So, we need to apply ascending order while calculating the rank function in excel. Hence, the 3rd parameter is given as 1.
- So, RANK (B3, mytime, 1) returns the lowest runtime as the rank 1 from the given race times.
- As a result, 4:30 (cell B3), the lowest one is recorded as rank 1 and the 9:00 (cell B5), the longest one is recorded as the last i.e. 4th rank.
Example #3 – Value Not Present
- As shown in the above figure, the 1st parameter i.e. the value for which the rank is to be calculated is NOT present in the range of cells given in the 2nd parameter, then the function returns #NA! indicating that the value is invalid i.e. not present in the given range of reference cells.
- As shown in the above figure, number 59 is not present in the range and hence #NA! is returned as result shown in cell C8.
- One must correct the 1st parameter in such a case.
Example #4 – Text Formatted Numerical Values
- As shown in the above figure, the 1st parameter i.e. the value for which the rank is to be calculated is given in the text format i.e. “5” as shown in the above figure. The value does not remain a number as it is quoted in double quotes and hence treated as a text.
- Since the rank cannot be calculated over text data, the function returns #N/A! error indicating that the value is Not Available in the given range of cells. The resultant cell C6 has #N/A returned as the result of the function.
- One must correct the 1st parameter in such a case.
Things to Remember
- The minimal parameter the Excel RANK function takes is 2.
- The 3rd parameter of the RANK function in excel is optional and zero (descending order) by default. If specified as 1, the function treats it as ascending order.
- There is no RANKIF function to perform a conditional ranking similar to COUNTIF in Excel. It needs the usage of additional Excel function to achieve the same.
- When the given number for which the rank is to be calculated (1st parameter) is not present in the range of cells given as a reference to the function (2nd parameter), then #N/A! error occurs.
- The function does not support any text values or the text representation of the numeric values as the RANK cannot be applied to text data. If provided so, the function returns #N/A error, as explained.
This has been a guide to RANK Function in Excel. Here we discuss how to use the RANK function in excel along with practical examples and downloadable excel templates. You may also look at these useful functions in excel –