RANK in WPS Spreadsheet: Rank Scores, Explain Ties and Reverse the Order
Use WPS RANK for descending scores and ascending times, anchor the comparison range, and distinguish tied ranks from a fixed-size shortlist.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
Quick answer: RANK gives a number’s position among numeric values. In the dated WPS Windows lesson, order 0 or omitted ranks the largest value first; a nonzero order ranks the smallest first. Equal numbers share a rank, and subsequent ranks have gaps.
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 matching scope
=RANK(number, ref, [order])
number is the value to rank, ref is the comparison array or range, and order controls direction. Nonnumeric values in the referenced comparison set are ignored. The source does not provide a complete error or direct-argument coercion table.
Examples use English function names and comma separators. Square brackets mark an optional argument and are not typed. Use real numeric inputs, not values that only look numeric because of their display format.
Example 1: create descending ranks without moving the records
Enter this table in A1:B6:
| Participant | Score |
|---|---|
| Ari | 90 |
| Bea | 70 |
| Cal | 90 |
| Dev | 50 |
| Eli | 70 |
In C2 enter the formula below, then fill through C6:
=RANK(B2,$B$2:$B$6,0)
Expected ranks are 1, 3, 1, 5 and 3. The two scores of 90 occupy the first two positions and both receive rank 1. The next two positions belong to 70, so both receive rank 3. The final score receives rank 5.
The dollar signs keep the comparison set fixed as the target changes from B2 to B6. Without the anchors, filling the formula can shift the comparison range and silently change the question each row answers.
RANK returns a number; it does not sort the table. Ari and Cal still occupy their original rows even though both rank first.
Example 2: smaller completion times should rank first
On another sheet, enter times 18, 15, 15, 24 and 20 in B2:B6. In C2 enter and fill down:
=RANK(B2,$B$2:$B$6,1)
Expected ranks are 3, 1, 1, 5 and 4. Two participants share the fastest time of 15. Using order 0 instead would reward the slowest time, which is the opposite of the intended rule.
The source describes any nonzero order as ascending. Using an explicit 1 makes that intent easier to read than an arbitrary nonzero number.
Advanced example: a rank cutoff can include more than three records
Return to the score table. In D2 enter and fill down:
=IF(C2<=3,"Include","")
This includes Ari, Bea, Cal and Eli, four records. A rank-at-most-three rule includes everyone tied at the boundary; it does not promise exactly three names.
If an exact headcount is required, define a secondary policy, such as a second assessment score, before selecting records. The ranking function cannot invent a fair tie-breaker from equal scores alone.
For comparison, RANK.AVG assigns the average occupied position to ties. That different statistic does not by itself create a unique ordering either.
Advanced check: positive transformations preserve rank
Create =B2+10 in E2 and =B2*2 in F2, filling both through row 6. Rank each transformed column against its own anchored range with order 0.
Both expected rank lists remain 1, 3, 1, 5 and 3. Adding a constant or multiplying by a positive constant preserves the order and the ties.
Multiplying by -1 reverses the numeric order. The negated scores -90, -70, -90, -50 and -70, ranked descending, give 4, 2, 4, 1 and 2. This is an independent ordering check, not a reason to omit the explicit direction argument in a shared workbook.
Reference types and practical limits
On a separate sheet, enter numeric 90, text ‘70, numeric 50, and the label pending in A2:A5. =RANK(50,A2:A5,0) has expected result 2, using the two numeric observations. Converting the text 70 into a real number changes that result to 3.
The leading apostrophe is an entry aid for storing text and is not part of a numeric value. Inspect underlying types when a visible score seems to be missing from the comparison set.
The dated source demonstrates RANK.EQ as another way to obtain tied competition ranks. See RANK.EQ for its separately documented identity and source caveat; similarity on these examples is not a guarantee of every current-build edge case. Missing target values, error cells and empty comparison sets remain unverified here.
References and verification
- WPS Academy: RANK, 2019-05-06 (Chinese; Windows Personal).
- Editorial testing standards.
Expected outputs are independent calculations, not recorded native results. Current-build limits beyond the cited documentation remain unverified.