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

RANK and RANK.EQ are identical: they give tied values the same rank, then skip ranks after the group (e.g., ranks 1, 2, 2, 4). RANK.AVG handles ties by averaging their rank positions, so ties at positions 2–3 both get rank 2.5. Use RANK.AVG when tie-breaking matters; use RANK for standard competition ranking.

Why does RANK.AVG return a decimal?

Decimals appear when the number you're ranking is tied with other values. RANK.AVG averages the positions those tied values occupy. If two values tie at ranks 4 and 5, RANK.AVG returns 4.5 for both. If there are no ties, you get an integer.

How do I rank multiple columns at once?

Use RANK.AVG in a helper column for each column you want to rank, or wrap it in BYROW/BYCOL for array formulas in newer Excel and Google Sheets versions. For example, =BYROW(data, LAMBDA(row, RANK.AVG(row, data, 0))) ranks each row against all rows.

Can I rank text values with RANK.AVG?

No—RANK.AVG only works with numbers and dates. Text values cause #VALUE! error. If you need to rank text alphabetically, use MATCH to find position, or convert text to numeric codes and rank those instead.

RANK.AVG function

Returns the average rank of a value within a dataset, splitting tied positions equally among all duplicate values that match.

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

Generate a RANK.AVG formula

Describe what you need. The generator will reach for RANK.AVG where RANK.AVG is the right tool, and tell you when it is not.

Arguments

How RANK.AVG reads its arguments

Argument	Required	Description
number	Required	The value to rank (required). Must be numeric or a date value. Non-numeric values trigger #VALUE! error.
ref	Required	The array or range containing values to rank against (required). Can be a range like A1:A10 or an array constant like {20,15,12,8,5}.
order	Optional	Direction to rank: 0 for descending (default), 1 for ascending. Omitting this argument defaults to descending order.

Returns

A number representing the average rank position of the value within the reference array.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Rank a quantity against all stock levels

Ingredient	Supplier	Unit	Qty	Expiry
Tomatoes	Supplier A	kg	15	2026-09-20
Flour	Supplier B	kg	8	2026-10-15
Olive Oil	Supplier A	liters	12	2026-12-01
Butter	Supplier C	kg	5	2026-09-25
Basil	Supplier B	bundles	20	2026-09-17

=RANK.AVG(15,C2:C6,0)

Result: 2

Tomatoes has a quantity of 15 kg, which is the second-highest stock level (Basil at 20 is highest). Since 15 is a unique value with no ties, RANK.AVG returns 2, the same as RANK would.

2. Handle tied quantities with average rank

Qty Levels

=RANK.AVG(15,{20,15,15,12,5},0)

Result: 2.5

When two quantities match at 15 (positions 2 and 3 in descending order), RANK.AVG averages their ranks: (2+3)/2 = 2.5. This demonstrates RANK.AVG's key difference from RANK, which would assign both the same rank of 2.

3. Rank expiry dates from soonest to latest

Ingredient	Expiry Date
Tomatoes	2026-09-20
Flour	2026-10-15
Olive Oil	2026-12-01
Butter	2026-09-25
Basil	2026-09-17

=RANK.AVG(D2,D2:D6,1)

Result: 2

With order=1 (ascending), dates are ranked from earliest to latest. Tomatoes at 2026-09-20 is the second-soonest to expire after Basil at 2026-09-17. RANK.AVG returns 2, helping identify which stock needs rotation first.

Common errors

Which RANK.AVG error are you seeing?

Error	Why it happens	How to fix it
#N/A	The value in the number argument does not exist in the ref array. For example, =RANK.AVG(7,{20,15,12,8,5}) searches for 7 but it is not present.	Check that the value actually exists in your dataset. Verify the value is spelled/typed identically and has the same data type (number vs. text).
#VALUE!	The number argument or values in ref contain non-numeric data. For instance, =RANK.AVG('text',{20,15,12,8,5}) or =RANK.AVG(15,{'a','b','c'}).	Ensure both number and all values in ref are numeric or dates. Remove or convert text values. Use VALUE() or DATEVALUE() to convert if needed.
#REF!	The ref range references a deleted range or invalid cell address, such as =RANK.AVG(15,DeletedSheet!$A$1:$A$5).	Verify the range exists and the sheet name is correct. Use the Name Box to navigate to and re-select the correct range, then update the formula.

Tips and when to use something else

Use RANK.AVG when you need fair ranking of tied values (e.g., employee performance scores). Use RANK when you want all ties to share the lowest rank in the group.
RANK.AVG always returns a decimal when ties exist; consider rounding the result with ROUND() if you need integer output.
For large datasets, pair RANK.AVG with FILTER() or array formulas to rank subsets (e.g., rank each supplier's inventory separately).
If you need percentile ranks instead of positions, use PERCENTRANK or PERCENTRANK.INC—don't try to derive percentiles from RANK.AVG.

Related functions

PERCENTRANK.INCReturns the percentile rank of a value within a dataset as a decimal from 0 to 1, showing where it stands relative to all other values.LARGELARGE returns the k-th largest value in an array, where k=1 is the maximum, k=2 is the second-largest, and so on.SMALLSMALL returns the k-th smallest value from an array, where k=1 gives the minimum and higher k values give progressively larger results.MATCHMATCH returns the relative position of a lookup value within a one-dimensional range, optionally using exact, less-than, or greater-than matching.

Frequently asked questions

What's the difference between RANK, RANK.AVG, and RANK.EQ?: RANK and RANK.EQ are identical: they give tied values the same rank, then skip ranks after the group (e.g., ranks 1, 2, 2, 4). RANK.AVG handles ties by averaging their rank positions, so ties at positions 2–3 both get rank 2.5. Use RANK.AVG when tie-breaking matters; use RANK for standard competition ranking.
Why does RANK.AVG return a decimal?: Decimals appear when the number you're ranking is tied with other values. RANK.AVG averages the positions those tied values occupy. If two values tie at ranks 4 and 5, RANK.AVG returns 4.5 for both. If there are no ties, you get an integer.
How do I rank multiple columns at once?: Use RANK.AVG in a helper column for each column you want to rank, or wrap it in BYROW/BYCOL for array formulas in newer Excel and Google Sheets versions. For example, =BYROW(data, LAMBDA(row, RANK.AVG(row, data, 0))) ranks each row against all rows.
Can I rank text values with RANK.AVG?: No—RANK.AVG only works with numbers and dates. Text values cause #VALUE! error. If you need to rank text alphabetically, use MATCH to find position, or convert text to numeric codes and rank those instead.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17