RANK.AVG

What it does

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

RANK.AVG(number, ref, [order]) returns the midrank of number within ref: the average of the positions the tied block containing number occupies in the sorted order.

Let S be the multiset of numeric values in ref, let r be the competition rank

order = 0 or omitted (descending):   r  =  #{ i : v_i >  number }  +  1
order nonzero      (ascending):      r  =  #{ i : v_i <  number }  +  1

and let m = #{ i : v_i == number } be the multiplicity of number. The tied block occupies positions r, r+1, …, r+m-1, whose mean is

RANK.AVG  =  r  +  (m - 1) / 2

This is fractional ranking, the 1, 2.5, 2.5, 4 convention. When m = 1 the correction term vanishes and RANK.AVG agrees exactly with RANK.EQ; the two functions differ only on values that repeat.

The reason midranks exist is a conservation property that competition ranks lack. Summing the midrank over every element of ref gives

sum of midranks  =  1 + 2 + … + n  =  n(n+1)/2

for every data set, tied or not, because averaging within a block redistributes the block's positions without changing their total. Competition ranking skips positions and does not conserve the sum. That conservation is exactly what the rank-based statistics need: Spearman's rank correlation, the Wilcoxon rank-sum and signed-rank tests, the Mann–Whitney U statistic and the Kruskal–Wallis test are all defined on midranks, and all of them lose their null distributions if fed competition ranks on tied data. If the ranks are going into further arithmetic, RANK.AVG is the correct function and RANK.EQ is not.

Two consequences of the formula:

1. The result need not be an integer. A block of even size lands on a half-integer; a block of size m shifts the rank by (m-1)/2, which is a multiple of 1/2 and nothing finer. RANK.AVG returns exactly r or exactly r + k/2, never anything more exotic.
2. The result is unchanged by which member of the block you ask about. Every element of the tied block gets the same midrank, because r and m depend on the value, not on its position in ref.

Like RANK.EQ, RANK.AVG requires number to occur in ref. It reports where a present value sits; it does not interpolate a position for an absent one. An absent number yields #N/A.

Arguments

The default is descending: 1 is the largest value.

ref is consumed by scanning; text, logical values and blank cells in a scanned range are skipped rather than converted, so n counts numeric cells only. See coercion and lifting.

Try it

Quirks

Result and edge cases

Returns Number — an exact integer or exact half-integer.

• ref empty of numbers. #N/A.
• A block spanning the whole list. If every value in ref equals number, then r = 1 and m = n, so the answer is (n+1)/2 — the midpoint. Every element is equally ranked, which is the correct statement about a constant sample.
• Errors in ref propagate as themselves.
• ref given as a rectangular grid is flattened.

How to implement

Numerical notes

RANK.AVG does one division, by two, and nothing else. That makes it about as numerically clean as a worksheet function can be, and it is worth saying why precisely.

The correction term is exact. (m - 1) / 2 with m a small integer is a division by a power of two, which is exact in binary64 for every m up to 2^53. Adding it to the integer-valued competition rank is also exact, because r + (m-1)/2 needs at most one bit below the binary point and r is far from the top of the significand. So RANK.AVG returns the mathematically exact midrank, with no rounding, for any list Excel can hold. There is no accuracy question here at all — which is unusual enough in the statistical category to be worth recording.

The conservation identity is a usable self-check. Because the midranks of a complete list sum to n(n+1)/2 exactly, and because that sum is computed in exact arithmetic as argued above, a column of RANK.AVG over its own ref must sum to exactly n(n+1)/2 with no tolerance. If it does not, the tie detection disagreed with itself somewhere. This is a cheap and strong invariant, and it is the one a vector suite for this function should assert first.

Cost. Two passes over ref per call — one for the strict count, one for the multiplicity — so a column of RANK.AVG formulas over a shared ref is O(n^2).

Rust code

13 rank_avg_fn "RANK.AVG" (2, 3) [V "number", F "ref", V "order"] |s, a, sv| { rank(s, a, sv, true) }

No implementation has been published with a Handbook test suite for this function.

Version differences

No comparison between specific Excel builds is recorded here yet. This does not establish that all builds behave alike.

Alternatives

Relationships

• RANK, the legacy Compatibility-category name, corresponds to RANK.EQ, not to this function. RANK.AVG has no legacy predecessor: it arrived with the dotted names because averaged ranking was not previously available in Excel at all. That asymmetry matters when reading migration guidance — the .EQ/.AVG pair is one old function plus one genuinely new one, not a split of one old function into two.
• CORREL over two RANK.AVG columns is Spearman's rank correlation coefficient, and is the reason RANK.AVG exists in a spreadsheet. Excel publishes no SPEARMAN; this composition is the standard route, and it is only correct with averaged ranks.
• COUNTIF reproduces the definition: COUNTIF(ref,">"&number) + (COUNTIF(ref,"="&number)+1)/2 is the descending midrank, without the presence check.
• PERCENTRANK.INC answers the same question on a [0, 1] scale and does interpolate for absent values.
• SMALL and LARGE: the inverse identity that holds for RANK.EQ fails here, because a midrank on tied data is not a valid order-statistic index.

Named variants

No named variants are recorded yet.