RANK.EQ

What it does

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

RANK.EQ(number, ref, [order]) returns the position number would occupy in ref if ref were sorted, counting from the best end.

Let S = {v_1, …, v_n} be the numeric values in ref (a multiset — repeats are kept). Then

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

The rank is a strict count plus one. That single formula is the whole function, and it determines the tie behaviour without any separate rule: because the comparison is strict, every member of a tied block counts zero of its own peers, so all of them receive the same rank — the best one the block occupies.

This is competition ranking, the 1, 2, 2, 4 convention. It is what a scoreboard does: two silver medals and no bronze. Two properties follow, and the second is the one that surprises people:

1. Ties share the best rank of their block. Three values tied at what would be positions 4, 5, 6 all rank 4.
2. The ranks are not a permutation of 1..n. After a block of m ties at rank r, the next distinct value ranks r + m, not r + 1. Ranks are skipped, and the sum of the ranks over all of ref is not n(n+1)/2. Anything downstream that assumes a rank vector is a permutation — a Spearman correlation computed by hand, for instance — is wrong on tied data and needs RANK.AVG instead.

number must occur in ref. RANK.EQ is not an interpolating position function: it does not tell you where a value would go, it tells you where a value that is present is. A number absent from ref yields #N/A, not a fractional or bracketing rank. That is the behaviour that distinguishes RANK.EQ from PERCENTRANK.INC, which does interpolate.

Arguments

The default is descending, which is worth stating explicitly because it is the opposite of the default a reader coming from a sort dialog expects. RANK.EQ(x, A) answers "how high does x place", with 1 meaning largest.

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 — a positive integer, always an exact integer value.

• number not present in ref. #N/A.
• ref empty of numbers. #N/A.
• number present more than once. All occurrences receive the same rank; the multiplicity affects the ranks of later values, not this one.
• number in ref but reached by a different route. The match is exact IEEE-754 equality of the collected doubles — see Numerical notes. A value that displays identically but differs in the last bit does not match.
• Errors in ref propagate as themselves.
• ref given as a rectangular grid is flattened; RANK.EQ does not care about shape.

How to implement

Numerical notes

RANK.EQ performs no arithmetic beyond counting, so there is no rounding error anywhere in it. Every numerical question about this function is a comparison question, and there are two.

Signed zero and the strict comparison. -0.0 == 0.0 is true and -0.0 > 0.0 is false, so the two zeros are one value for both the match test and the rank count. That is the right answer and it is worth noting only because the sort-free formulation makes it automatic — an implementation that sorted and then scanned would have to be careful, and this one does not sort at all.

The counting form is O(n) per call and exact. Because the rank is computed as a count rather than by sorting and locating, a column of RANK.EQ formulas over the same ref is O(n^2) overall. That is a performance fact rather than a correctness one, but it is the reason large rank columns are slow, and it is not fixable inside the function.

The order truncation. trunc(order) before the zero test means the ascending/descending boundary is not at zero but across the open interval (-1, 1), which all truncates to 0 and therefore selects descending. Nothing documents this, and a caller passing a computed fraction gets the opposite of what the "any nonzero value" phrasing promises.

Rust code

12 rank_eq_fn "RANK.EQ" (2, 3) [V "number", F "ref", V "order"] |s, a, sv| { rank(s, a, sv, false) }

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.AVG is the sibling that resolves ties by averaging. For a tied block of m values starting at competition rank r, RANK.AVG returns r + (m-1)/2, the midrank. The two functions agree exactly when number has no duplicates in ref. RANK.AVG is the one to use whenever the ranks feed further statistics, because its ranks always sum to n(n+1)/2.
• RANK is the legacy Compatibility-category name. Microsoft's replacement guidance points RANK at RANK.EQ, and the two are documented as computing the same quantity. The Handbook does not treat that as an identity. A legacy alias and its modern name are two registered entry points, and Excel is free to route them to two different code paths; that they agree is a claim requiring evidence. No such evidence is in the Handbook's record. The one place the question has teeth is the order flag and the non-numeric-number case, where the older function's behaviour is even less documented.
• SMALL and LARGE are the inverses: LARGE(ref, RANK.EQ(x, ref)) recovers x when x occurs in ref, because competition rank and the descending order-statistic index are the same number. The corresponding identity for RANK.AVG fails on ties, since its rank need not be an integer.
• PERCENTRANK.INC answers the same question on a [0, 1] scale and does interpolate for values not present, which is exactly the behaviour RANK.EQ refuses.
• COUNTIF reproduces the definition directly: COUNTIF(ref, ">"&number) + 1 is the descending rank, without the presence check. Readers who want a rank for an absent value write this, and should know they have given up the #N/A diagnostic in the process.

Named variants

No named variants are recorded yet.