What's the difference between RANK.AVG and RANK.EQ?

RANK.AVG assigns average ranks to tied values, while RANK.EQ assigns identical ranks and skips subsequent numbers. For example, three tied values at positions 1, 2, 3 receive rank 2 with RANK.AVG but all receive rank 1 with RANK.EQ (skipping rank 2 and 3). RANK.AVG is fairer for competitive scenarios where ties should be treated equally.

Can RANK.AVG handle negative numbers?

Yes, RANK.AVG works with negative numbers, positive numbers, and zero. The ranking is based on numerical value magnitude, not sign. In descending order, larger values (including less negative numbers) rank higher. For example, -5 ranks higher than -10 in descending order.

Is RANK.AVG available in Google Sheets?

RANK.AVG is not directly available in Google Sheets. However, Google Sheets offers RANK function with similar syntax. For average ranking functionality in Google Sheets, use COUNTIFS to count values and create custom formulas, or use RANK combined with manual adjustments for tied values.

How do I rank data in ascending order with RANK.AVG?

Use the order parameter set to 1: =RANK.AVG(number, ref, 1). This assigns rank 1 to the smallest value and increases for larger values. For example, ranking test scores where lower scores should rank higher, or ranking wait times where shorter durations rank better.

What happens when all values in the range are identical?

When all values are identical, RANK.AVG assigns the average of all possible ranks to every entry. For a range of 5 identical values, each receives rank 3 (average of 1, 2, 3, 4, 5). This reflects that all entries are equally ranked.

Complete Guide to RANK.AVG: Handling Tied Rankings in Excel

Intermediate

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

The RANK.AVG function is a powerful statistical tool in Excel that assigns rankings to numerical values while intelligently handling tied data points. Unlike RANK.EQ, which assigns the same rank to equal values and skips subsequent ranks, RANK.AVG distributes an average rank among all tied entries, providing a more balanced approach to ranking datasets. This function is essential for professionals working with competitive scoring, performance evaluations, sales rankings, and any scenario where fair treatment of equal values matters. RANK.AVG became available in Excel 2010 and remains a cornerstone function for data analysts, HR professionals, and business intelligence specialists. Understanding how to leverage RANK.AVG ensures accurate statistical analysis and prevents misleading rankings that could distort decision-making processes. Whether you're ranking employee performance, student test scores, or product sales figures, mastering this formula will elevate your Excel proficiency and data analysis capabilities.

Syntax & Parameters

The RANK.AVG function follows this syntax: =RANK.AVG(number, ref, [order]). The first parameter, 'number' (required), specifies the value you want to rank within your dataset. This can be a single cell reference or a specific numerical value. The 'ref' parameter (required) defines the entire range of numbers against which your target number will be compared—this is typically an array or cell range containing all values to be ranked. The 'order' parameter (optional, defaults to 0) controls ranking direction: use 0 for descending order (highest values get rank 1) or 1 for ascending order (lowest values get rank 1). When ties occur, RANK.AVG calculates the average of the ranks that would be assigned to those tied values. For example, if three values tie for ranks 5, 6, and 7, each receives rank 6 (the average). This distinguishes RANK.AVG from RANK.EQ, which assigns identical ranks without averaging. The function returns a numerical value representing the rank position, and you can nest it within other formulas for complex analytical workflows.

number

Number to rank

ref

Reference range

order

0=descending, 1=ascending

Optional

Practical Examples

Employee Performance Ranking with Tied Scores

=RANK.AVG(B2,$B$2:$B$6,0)

This formula ranks the performance score in B2 against all scores in the range B2:B6 in descending order (0). When Employees B and C both score 92, instead of assigning ranks 1 and 2 with one skipped, RANK.AVG assigns both employees rank 1.5 (the average of 1 and 2). Employee A with 85 receives rank 3, Employee E with 88 receives rank 2, and Employee D with 78 receives rank 5.

Sales Competition Leaderboard

=RANK.AVG(C3,$C$3:$C$7,0)

Applied to Rep 3's revenue of $125,000, this formula ranks against all revenues in descending order. Reps 1 and 5 both have $150,000 (tied for ranks 1-2, so each gets 1.5). Reps 2 and 3 both have $125,000 (tied for ranks 3-4, so each gets 3.5). Rep 4 with $98,000 receives rank 5.

Student Test Score Rankings

=RANK.AVG(D4,$D$4:$D$9,0)

For Student 4 with score 76, this formula ranks in descending order. Students 1 and 3 (both 95) share ranks 1-2, receiving 1.5 each. Students 2 and 5 (both 88) share ranks 3-4, receiving 3.5 each. Student 6 (82) gets rank 5, and Student 4 (76) gets rank 6.

Key Takeaways

RANK.AVG assigns average ranks to tied values, ensuring fair treatment when multiple entries share identical scores or values
The function requires three parameters: number (the value to rank), ref (the range to rank against), and order (0 for descending, 1 for ascending)
Always use absolute references for the ref parameter when copying formulas to maintain consistent ranking against the entire dataset
RANK.AVG differs from RANK.EQ in that it distributes average ranks among ties rather than assigning identical ranks and skipping numbers
Combine RANK.AVG with other functions like IF, COUNTIF, and SUMPRODUCT to create sophisticated ranking and filtering systems for business analysis

Pro Tips

Always use absolute references ($) for the ref parameter when copying formulas down. This ensures each row ranks against the entire dataset, not expanding ranges. Example: =RANK.AVG(B2,$B$2:$B$100,0) not =RANK.AVG(B2,B$2:B$100,0)

Impact : Prevents incorrect ranking calculations and ensures consistent reference ranges across all rows, saving debugging time and ensuring data accuracy.

Combine RANK.AVG with LARGE or SMALL to identify top/bottom performers. Use =RANK.AVG(LARGE($A$1:$A$100,1),$A$1:$A$100,0) to rank the highest value, or nest with IF statements for conditional ranking.

Impact : Enables sophisticated analysis workflows where you can automatically identify and rank extreme values, useful for outlier detection and performance benchmarking.

Use RANK.AVG with COUNTIF to create tier-based classifications. For example, IF(RANK.AVG(...)<=3,'Top Tier',IF(RANK.AVG(...)<=10,'Mid Tier','Bottom Tier')) assigns performance tiers based on rankings.

Impact : Transforms raw rankings into business-meaningful categories, making dashboards and reports more actionable and easier to interpret for stakeholders.

Test your RANK.AVG formulas with intentional duplicates to verify average rank calculation. Create test data with known ties to validate the formula before applying to production data.

Impact : Builds confidence in your formula logic and catches errors early, preventing incorrect analyses from reaching decision-makers or being embedded in critical reports.

Useful Combinations

Rank with Conditional Filtering

=IF(C2>50, RANK.AVG(C2,$C$2:$C$10,0), "Below threshold")

Combines RANK.AVG with IF to rank only values exceeding a threshold. This filters data before ranking, useful for analyzing performance tiers or qualifying participants. Values below 50 show 'Below threshold' instead of a rank.

Rank with Percentile Analysis

=RANK.AVG(D3,$D$3:$D$15,0)/COUNTA($D$3:$D$15)

Divides the RANK.AVG result by the count of values to create a percentile ranking between 0 and 1. This converts ordinal ranks to relative positioning, helping identify top performers as a percentage of total participants.

Rank with Conditional Aggregation

=SUMPRODUCT((RANK.AVG($E$2:$E$20,$E$2:$E$20,0)<=3)*($E$2:$E$20))

Uses RANK.AVG within SUMPRODUCT to sum values that rank in the top 3 positions. This identifies and aggregates high-performing entries, useful for calculating total revenue from top performers or combined scores of leading competitors.

Common Errors

#REF!

Cause: The ref parameter contains an invalid or deleted cell reference, or the range syntax is incorrect (e.g., missing colon separator or referencing a non-existent worksheet).

Solution: Verify the range reference uses correct syntax with absolute references ($A$1:$A$10). Check that all referenced cells exist and the worksheet hasn't been deleted. Use the Name Box to validate range references.

#VALUE!

Cause: The 'number' parameter or values within the 'ref' range contain non-numeric data, text, or logical values that Excel cannot process as numbers.

Solution: Ensure the number parameter references a cell containing only numeric values. Check the ref range for text entries, spaces, or formatting issues. Use IFERROR to handle mixed data: =IFERROR(RANK.AVG(number,ref,order),"N/A")

#NUM!

Cause: The 'order' parameter contains a value other than 0 or 1, or the function cannot process the data structure provided.

Solution: Verify the order parameter is either 0 (descending) or 1 (ascending). Confirm the ref range contains valid numeric data. Check for circular references or corrupted cell values using Find & Replace to locate problematic characters.

Troubleshooting Checklist

1.Verify the 'number' parameter references a single cell with numeric data, not a range or text value
2.Confirm the 'ref' parameter uses absolute references ($A$1:$A$100) and includes all values to be ranked
3.Check that the 'order' parameter is either 0 (descending) or 1 (ascending), not other values
4.Ensure no cells in the ref range contain errors (#N/A, #REF!, #DIV/0!) that would propagate to RANK.AVG results
5.Validate that data types are consistent (all numeric values in the range, no text mixed with numbers)
6.Test with a simple known dataset to confirm the formula works before applying to complex datasets

Edge Cases

All values in the range are identical

Behavior: RANK.AVG assigns the average of all possible ranks to every value. For 5 identical values, each receives rank 3 (average of 1,2,3,4,5)

This is expected behavior reflecting that all entries are equally ranked. Useful for identifying situations where no differentiation exists in the dataset

The number parameter is not in the ref range

Behavior: RANK.AVG still calculates a rank based on the value's position relative to the range values. If the number is smaller than all range values in descending order, it receives the lowest rank

Solution: Ensure the number parameter references a cell within the ref range, or use IFERROR to handle values outside the range

This can occur with dynamic data or when ranking new entries not yet added to the main dataset

The ref range contains only one value

Behavior: RANK.AVG assigns rank 1 to that single value, regardless of whether the number parameter matches it

Edge case unlikely in practice but important for formula robustness. Consider using COUNTA to validate range size before ranking

Limitations

•RANK.AVG only returns ordinal or average ordinal ranks; it cannot directly assign percentile values or custom ranking scales without additional formulas
•The function cannot rank based on multiple criteria or secondary sort keys; complex multi-level ranking requires nested IF statements or helper columns
•RANK.AVG is not available in Google Sheets, requiring workarounds or alternative functions for users in that platform, limiting cross-platform compatibility
•The function processes entire ranges each time it's called, which can impact performance on very large datasets (100,000+ rows) compared to pre-calculated ranking systems

Alternatives

RANK.EQ

Simpler to use when you don't need average ranks for ties; returns integer ranks only. Faster calculation for large datasets.

When: Use when tied values should share the same rank and you want to skip subsequent ranks, such as tournament standings where ties are acceptable.

PERCENTRANK

Returns percentile ranking (0 to 1) instead of ordinal ranks, showing relative position as a percentage of the dataset.

When: Ideal for comparing values across different-sized datasets or when you need to express rankings as percentiles for statistical analysis.

COUNTIFS with manual calculation

Provides complete control over ranking logic and can implement custom tie-breaking rules beyond average ranking.

When: Use when you need specialized ranking logic, such as breaking ties by secondary criteria or applying weighted ranking systems.

Compatibility

✓ Excel

Since 2010

=RANK.AVG(number, ref, [order]) - Available in Excel 2010, 2013, 2016, 2019, 2021, and Microsoft 365

✗Google Sheets

Not available

✓LibreOffice

=RANK.AVG(number, ref, [order]) - Supported in LibreOffice Calc with identical syntax to Excel

Frequently Asked Questions

Master Excel's most powerful statistical functions with ElyxAI's interactive formula builder and real-time verification tools. Transform your data analysis workflow and eliminate formula errors instantly.

RANK.EQ

PERCENTILE.INC