How to Use the RANK Function in Excel
The RANK function in Excel and Google Sheets is designed to easily assign a rank to a specific number within a data set. This rank reflects the position of the number compared to others in the data set, where lower values receive a higher rank if sorting in descending order, and vice versa.
Syntax:
The syntax for the RANK function is consistent across both Excel and Google Sheets:
RANK(number, ref, [order])
number
: The specific number whose rank you want to determine.ref
: The array or range of numbers against which the number is compared.order
(optional): Determines the order in which ranks are assigned.0
(default) or omitted: Numbers are ranked in descending order, with the largest number receiving a rank of 1.1
: Numbers are ranked in ascending order, with the smallest number receiving a rank of 1.
Example:
Consider a set of numbers in cells A1 to A5 in Excel or Google Sheets:
Data |
---|
25 |
50 |
75 |
40 |
60 |
To find the rank of the number 50 within this dataset:
=RANK(50, A1:A5, 0)
This formula calculates the rank of 50. As the fourth-largest number, 50 is ranked 3, following 75 (rank 1) and 60 (rank 2).
Omitting the [order] parameter or setting it to 0 sorts the data in descending order, while setting it to 1 sorts in ascending order.
The RANK function is extremely useful for quickly assessing the position of individual values within a larger set of numerical data, applicable in various practical contexts such as academic grading, sales ranking, or other statistical analyses.
More information: https://support.microsoft.com/en-us/office/rank-function-6a2fc49d-1831-4a03-9d8c-c279cf99f723