Excel RANK function
The RANK function returns the rank of a number compared to other numbers in the same list. The same rank will be assigned to duplicate values in the list.
RANK function VS. RANK.EQ function
Starting from Excel 2010, the RANK function has been replaced by the RANK.EQ function. Although they can both be used to return the rank of a number against a list of numbers, the RANK.EQ function is more recommended for future use, because the RANK function may not be available in future versions of Excel.
RANK(number, ref [order]))
- Number (required): The number for which you want to find the rank;
- Ref (required): A range that contains the numbers to rank against. It can be entered as an array, a reference to a cell containing a number, or a list of numbers.
- Order (optional): A number used to specify how to rank the number. There are two types of rankings:
-- 0 or omitted: tells Excel to rank “number” in descending order;
-- 1 or any non-zero values: tells Excel to rank “number” in ascending order.
1.If there are non-numeric values in the argument “ref”, they will be ignored;
2.If there are duplicate values in the list, this function will give them the same rank;
3.The #N/A! error occurs if the number you supplied for “number” does not exist in “ref”;
4.When ranking the list in descending order, the largest number in the list will be ranked #1. For ascending order ranking, the smallest number in the list will be ranked #1;
5.It is not necessary to sort the list before using the RANK function, because the rank of the numbers will be their positions after sorting.
It returns a numeric value.
There is a class report card as shown in the screenshot below. To calculate the rank of each score compared to other scores within the report, you can apply the RANK function to get it done.
Select a cell (says D6 in this case) next to the first score, enter the formula below and press Enter to get the rank of the first score in the list. Select this result cell and drag its AutoFill Handle down to get ranking of other scores.
1) In this case, the RANK function assigns the same rank (6) to the number 77 that appears twice in the list. Meanwhile, you can see that none of the numbers have a rank of 7. That is because the presence of the duplicate values affects the ranks of subsequent numbers.
2) The formula above ranks all scores in descending order. To rank the scores in ascending order, you can apply the following formula:
Excel QUARTILE.INC function
The QUARTILE.INC function returns the quartile for a given set of data based on a percentile range of 0 to 1 inclusive.
Excel QUARTILE function
The QUARTILE function calculates the quartile for a given set of data.
Excel QUARTILE.EXC function
The QUARTILE.EXC function returns the quartile for a given set of data based on a percentile range of 0 to 1 exclusive.
Excel RANK.AVG function
The RANK.AVG function returns the rank of a number compared to other numbers in the same list.
Excel RANK.EQ function
The RANK.EQ function returns the rank of a number compared to other numbers in the same list.
The Best Office Productivity Tools
Would you like to complete your daily work quickly and perfectly? Kutools for Excel brings
300 powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) and save
80% time for you.
- Designed for
1500 work scenarios, helps you solve
80% Excel problems.
- Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands.
- Become an Excel expert in 3 minutes. No longer need to remember any painful formulas and VBA codes.
30-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years.
- One second to switch between dozens of open documents!
- Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
- Increases your productivity by
50% when viewing and editing multiple documents.
- Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Firefox, And New Internet Explorer.