RANK.EQ in WPS Spreadsheet: Competition Ranks with Explicit Tie Rules
Calculate WPS RANK.EQ with fixed references, explain skipped ranks, and verify descending order against the historical WPS screenshots.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
Quick answer: RANK.EQ assigns equal values the highest position occupied by their tie group. With descending scores 100, 80, 80, 60 and 40, the expected ranks are 1, 2, 2, 4 and 5. The two 80s do not receive separate ranks 2 and 3.
Availability and version history
- Introduced: documented by 2019-05-06 in an official WPS lesson; the first release is not established.
- Availability: the lessons identify WPS Office for Windows Personal, without a numeric build range.
- Scope: exact current supported builds and other WPS clients remain unverified; historical documentation does not establish retirement.
- Checked: October 14, 2026 against function-specific sources. Worked results were independently calculated; no native WPS test was performed.
Syntax and a source contradiction
=RANK.EQ(number, ref, [order])
The official lesson lists a required target and comparison range, an optional order argument, and ignored nonnumeric reference values. This guide uses explicit order 0 for descending ranks.
The lesson’s walkthrough reverses the written descriptions of ascending and descending order. Its own WPS screenshot visibly contains =RANK.EQ(B2,$B$2:$B$10,0) and returns 9 for 50, the smallest of nine scores. That directly supports the descending order-0 examples here. The formal definition agrees, but contains an Excel product-name reference; it is not treated as independent evidence of all WPS defaults or limits.
Omitted-order and nonzero-order behavior are not independently confirmed by that screenshot. Keep those cases separate from the explicit order-0 controls. This is historical product documentation, not a captured test of a current installation.
Example 1: rank an entire score list
Enter 100, 80, 80, 60 and 40 in A2:A6. In B2 enter and fill through B6:
=RANK.EQ(A2,$A$2:$A$6,0)
| Score | Expected rank |
|---|---|
| 100 | 1 |
| 80 | 2 |
| 80 | 2 |
| 60 | 4 |
| 40 | 5 |
The repeated 80 occupies positions 2 and 3, so both instances receive the better rank 2. The next score starts at position 4. This is competition ranking, not dense ranking, which would use consecutive distinct-value ranks 1, 2, 3 and 4.
The absolute reference keeps all five rows in every comparison. Ranking against a shrinking range would create a different result even though the visible target values were unchanged.
Example 2: inspect a three-way tie
On another sheet, enter 12, 9, 9, 9 and 4 in A2:A6 and use the same filled formula.
Expected ranks are 1, 2, 2, 2 and 5. The three values of 9 occupy positions 2 through 4. Their shared competition rank is 2; the next distinct value is fifth.
For those three tied observations, the average occupied position is 3. RANK.AVG calculates that different result. Do not average the repeated RANK.EQ outputs themselves: averaging 2, 2 and 2 still gives 2, not the average occupied position.
Advanced example: audit a shortlist boundary
For the first score list, place this formula in C2 and fill down:
=IF(B2<=2,"Shortlist","")
It selects the score 100 and both scores of 80: three records pass a rank-at-most-two policy. A tie-inclusive threshold can exceed the requested headcount.
If the rule instead requires exactly two records, the workbook needs a separate, explicit tie-breaker. Row order alone should not silently determine the outcome unless that is an accepted part of the selection policy.
Advanced check: recover the occupied position range
For a tie group whose best rank is r and whose frequency is t, the occupied positions run from r through r+t-1. Their arithmetic mean is r+(t-1)/2.
For the two 80s, r=2 and t=2, so the occupied positions are 2 and 3 and their mean is 2.5. For the three 9s, r=2 and t=3, so their mean is 3. These independently derived controls explain exactly how competition ranking differs from average ranking.
This calculation is a mathematical check for the displayed numeric examples. It does not claim that a generic correction formula has been executed in WPS on every reference type.
Nonnumeric entries and lifecycle limits
A comparison range containing numeric 100, text ‘80, and numeric 40 has two numeric observations under the lesson’s reference rule. =RANK.EQ(40,A2:A4,0) has expected result 2 when those entries occupy A2:A4. Converting text 80 into a number changes the expected rank to 3.
The historical RANK lesson already names RANK.EQ on May 6, 2019. Its omission from the reviewed English catalogue does not establish that it was retired or renamed. Current build availability, absent targets, empty ranges and exact native error codes remain unverified.
References and verification
- WPS Academy: RANK.EQ, 2019-06-25 (Chinese; Windows Personal).
- Earlier WPS lesson naming all three rank functions.
- Editorial testing standards.
Expected outputs are independent calculations, not recorded native results. Historical screenshots support only the behavior visibly demonstrated in their documented Windows context.