RANK.AVG in WPS Spreadsheet: Average Tied Positions and Check Rank Totals
Use WPS RANK.AVG to average occupied tie positions, build complete ranking tables, and understand rank totals and tied cutoffs.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
Quick answer: RANK.AVG gives tied numbers the average of the positions they occupy. For descending scores 90, 70, 90, 50 and 70, the expected ranks are 1.5, 3.5, 1.5, 5 and 3.5. It averages positions, not the tied scores.
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 verified direction
=RANK.AVG(number, ref, [order])
The official lesson lists an optional order argument and says nonnumeric values in the comparison reference are ignored. All worked ranking formulas below pass 0 explicitly.
The walkthrough’s order descriptions are reversed. Its embedded WPS screenshot shows =RANK.AVG(B2,$B$2:$B$10,0) returning 9 for the smallest score, 50, in a nine-score table. Another image shows the two scores of 89 receiving 2.5. Together these product-specific demonstrations support descending order 0 and average-position ties.
The formal prose agrees on descending order 0 but includes an Excel product-name reference. Omitted-order defaults and nonzero-order behavior are not independently certified by the screenshots. Examples use English names and comma separators; optional brackets in the syntax are not typed.
Example 1: produce the complete average-rank table
Enter 90, 70, 90, 50 and 70 in A2:A6. In B2 enter and fill down:
=RANK.AVG(A2,$A$2:$A$6,0)
| Score | Occupied positions | Expected average rank |
|---|---|---|
| 90 | 1 and 2 | 1.5 |
| 70 | 3 and 4 | 3.5 |
| 90 | 1 and 2 | 1.5 |
| 50 | 5 | 5 |
| 70 | 3 and 4 | 3.5 |
The comparison range stays fixed while the target reference moves. The decimal halves are meaningful rank values. Formatting them as whole numbers can conceal the difference between average ranking and another tie policy.
For the same scores, RANK.EQ gives 1, 3, 1, 5 and 3. It chooses the best occupied position rather than averaging the tied positions.
Example 2: three tied observations have an integer average
On a separate sheet, enter 12, 9, 9, 9 and 4 in A2:A6 and fill the same formula.
Expected ranks are 1, 3, 3, 3 and 5. The three 9s occupy positions 2, 3 and 4; their mean is exactly 3. An integer result therefore does not prove that a value is untied.
When all four numeric observations equal 8, they occupy positions 1 through 4. Each average rank is 2.5. This is a check on occupied positions, not a claim that all participants have distinct places.
Advanced check: the rank total is preserved
For the first five-score example:
=SUM(B2:B6)
Expected result: 15. The positions 1 through 5 also sum to 15. Replacing each tie group’s positions with their common average preserves that group’s total.
The corresponding RANK.EQ results sum to 13, because choosing the best position for each member does not preserve the original position total. The two functions are not interchangeable in a calculation that relies on a rank-sum identity.
For n numeric observations ranked against that same complete set, the independent average-rank model sums to n(n+1)/2*. This control assumes the targets and comparison set match; ranking just a subset against a larger population need not produce that subset’s triangular total.
Advanced example: a fractional cutoff has a specific meaning
For the first table, =IF(B2<=3,"Include",""), filled down, includes only the two scores of 90. The two 70s have average rank 3.5 and therefore fail a cutoff of 3.
Raising the cutoff to 3.5 includes four records. Neither threshold selects exactly three records. A target headcount needs a separate tie policy rather than arbitrary display rounding of the average ranks.
Reference types and source boundaries
On another sheet, enter numeric 90, text ‘70, numeric 90, and numeric 50 in A2:A5. =RANK.AVG(50,A2:A5,0) has expected result 3 using the three numeric entries. Converting the text 70 into a number makes its expected result 4.
The earlier WPS RANK lesson names average tied ranks on May 6, 2019. That is a documented-by date, not a verified introduction release. Exact native handling of missing targets, reference errors and empty comparison sets remains unverified, as does equivalent behavior in other WPS clients.
References and verification
- WPS Academy: RANK.AVG, 2019-06-24 (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.