## Excel RANK.AVG function

The RANK.AVG function returns the rank of a number compared to other numbers in the same list. The average rank will be assigned to duplicate values in the list. This function is available in Excel 2010 and later versions.

#### RANK.AVG function VS. RANK.EQ function

Both the RANK.AVG function and the RANK.EQ function can be used to return the rank of a number in a list of numbers. The difference between them is that the RANK.AVG function assigns the average rank to duplicate values, while the RANK.EQ function assigns the top rank to duplicate values.

#### Syntax

RANK.AVG(number, ref [order]))

#### Arguments

• 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.

#### Remarks

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 average 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.

#### Return value

It returns a numeric value.

#### Example

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.AVG 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 rankings of other scores.

=RANK.AVG(C6,\$C\$6:\$C\$18,0)

Notes:

1) In this case, the RANK.AVG function assigns the average rank value (6.5) to the number 77 that appears twice in the list to occupy the 6th and 7th positions. Meanwhile, you can see that none of the numbers have a rank of 7. This is because the presence of 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:

=RANK.AVG(C6,\$C\$6:\$C\$18,1)

#### Related Functions

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 function
The RANK 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

#### Kutools for Excel - Helps You To Stand Out From Crowd

 Popular Features: Find/Highlight/Identify Duplicates  |  Delete Blank Rows  |  Combine Columns or Cells without Losing Data  |  Round without Formula ... Super VLookup: Multiple Criteria  |  Multiple Value   |  Across Multi-Sheets  |  Fuzzy Lookup... Adv. Drop-down List: Easy Drop Down List  |  Dependent Drop Down List  |  Multi-select Drop Down List... Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Unhide Columns  |  Compare Columns to Select Same & Different Cells ... Featured Features: Grid Focus  |  Design View  |  Big Formula Bar  |  Workbook & Sheet Manager  |  Resource Library (Auto Text)  |  Date Picker  |  Combine Worksheets  |  Encrypt/Decrypt Cells  |  Send Emails by List  |  Super Filter  |  Special Filter (filter bold/italic/strikethrough...) ... Top 15 Toolsets:  12 Text Tools (Add Text, Remove Characters ...)  |  50+ Chart Types (Gantt Chart ...)  |  40+ Practical Formulas (Calculate age based on birthday ...)  |  19 Insertion Tools (Insert QR Code, Insert Picture from Path ...)  |  12 Conversion Tools (Numbers to Words, Currency Conversion ...)  |  7 Merge & Split Tools (Advanced Combine Rows, Split Cells ...)  |  ... and more

Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...

Supports Office/Excel 2007-2021 and 365   |   Available in 44 Languages   |   Easy to Uninstall Completely

Full-Featured 30-Day Free Trial  |  60-Day Unconditional Money-Back GuaranteeRead MoreFree Download

#### Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)

• 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, Edge and Firefox.
Full-Featured 30-Day Free Trial  |  60-Day Unconditional Money-Back GuaranteeRead More Free Download