RANK.AVG in Zoho Sheet: Average Ranks for Tied Scores
Use Zoho Sheet RANK.AVG to find average ranking positions for ties, compare ascending ranks and build a fair score summary with worked examples.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
Quick answer: RANK.AVG ranks a number in a list and gives tied numbers the average of the positions they occupy. In the example below, two scores of 95 occupy first and second place, so =RANK.AVG(95;B2:B9) has an expected result of 1.5. Two scores of 88 occupy third and fourth place, so the same function returns 3.5 for each 88. The average ranking is a numeric position, not an arithmetic average of the scores themselves.
Evidence boundary: These expectations come from Zoho’s documented RANK.AVG tie rule and independently checked arithmetic. They have not been executed in the Zoho Sheet app.
Availability and version history
- Introduced: Added in February 2020, according to Zoho’s release history; no numbered cloud release is given.
- Availability: Currently documented in Zoho Sheet’s rolling web-service help; no numbered release or retirement endpoint is established.
- Scope: English-language web-service documentation. Mobile and offline-client parity is outside the verified scope.
- Checked: October 14, 2026, against the official RANK.AVG reference. Documentation check; not a new native test.
RANK.AVG syntax and what each argument means
=RANK.AVG(number; number_list; [order])
The exact spelling and argument order are from Zoho’s official RANK.AVG reference:
| Argument | Required? | Description |
|---|---|---|
number |
Yes | The score to rank, either a numeric literal such as 95 or a reference such as B2. |
number_list |
Yes | Range or array to compare the score against, for example B2:B9. |
order |
No | Default 0 ranks highest to lowest. Any nonzero numeric setting ranks lowest to highest. |
The official Zoho syntax uses semicolons as separators and the dot in RANK.AVG is part of the function name. The AVG component describes averaging the occupied rank positions in a tie; do not substitute the ordinary AVERAGE function, which finds the mean of values rather than their relative positions.
Reproducible example scores
Put the following fictional sales-team data into A1:B9.
| Row | A: Representative | B: Score |
|---|---|---|
| 1 | Representative | Score |
| 2 | Ava | 95 |
| 3 | Ben | 88 |
| 4 | Cara | 88 |
| 5 | Dev | 79 |
| 6 | Eli | 70 |
| 7 | Fae | 95 |
| 8 | Gus | 61 |
| 9 | Hana | 82 |
Think of the underlying descending sorted numbers as 95, 95, 88, 88, 82, 79, 70, 61. If the two 95s did not tie, first and second place would be assigned individually. Because they do tie, RANK.AVG splits the difference: (1+2)/2 = 1.5 for each. For two 88s at third and fourth, (3+4)/2 = 3.5 each.
Example 1: two joint leaders with rank 1.5
- Choose a free worksheet cell, such as D2.
- Enter the following exact Zoho-style formula:
=RANK.AVG(B2;B2:B9)
- Compare Ava’s 95 with the eight values. It is tied with Fae at the largest score.
Expected result: 1.5. Repeat the calculation for Fae:
=RANK.AVG(B7;B2:B9;0)
Expected result: 1.5 again. The explicit 0 gives the same descending direction as leaving order out. Both tied values are treated symmetrically. There is no single first-place winner in this dataset under average-rank accounting.
Example 2: average the two middle tie positions
=RANK.AVG(B3;B2:B9)
Expected result: 3.5 for Ben’s 88. Cara’s score is equal:
=RANK.AVG(B4;B2:B9)
Expected result: 3.5 for Cara as well. There are two values greater than 88, so the pair occupies numeric positions 3 and 4. A simple top-position competition ranking would give both a 3; this average-rank function explicitly represents the mean position of 3 and 4.
Example 3: compare ascending ranking
Suppose a lower number represents a better outcome, such as average processing minutes instead of a performance score. Supply the third argument:
=RANK.AVG(B2;B2:B9;1)
Expected result: 7.5 for Ava’s 95 in ascending order. In increasing sequence, the 95s occupy positions 7 and 8; (7+8)/2 = 7.5.
=RANK.AVG(B3;B2:B9;1)
Expected result: 5.5 for an 88. Ascending values are 61, 70, 79, 82, 88, 88, 95, 95. The two 88s occupy fifth and sixth places, so their average position is (5+6)/2 = 5.5. This change affects the rank direction, not the physical row order.
Advanced application: compare qualification policies
Create a rank column for all eight people. Put Average Rank in C1, and enter the following formula in C2:
=RANK.AVG(B2;$B$2:$B$9)
Copy it through C9. The dollar signs make B2:B9 an absolute comparison range, so only the target B2 moves when the formula is filled down.
| Person | Score | Expected average rank |
|---|---|---|
| Ava | 95 | 1.5 |
| Ben | 88 | 3.5 |
| Cara | 88 | 3.5 |
| Dev | 79 | 6 |
| Eli | 70 | 7 |
| Fae | 95 | 1.5 |
| Gus | 61 | 8 |
| Hana | 82 | 5 |
If a hypothetical program qualifies people with an average rank no greater than 3, use:
=COUNTIF(C2:C9;"<=3")
Expected result: 2. Only Ava and Fae (1.5 each) meet that cutoff; Ben and Cara (3.5 each) do not. If you built column C with RANK.EQ instead, the same criterion would count 4 people, because the tied 88s have competition rank 3. Neither convention is universally fairer: the program must explicitly decide whether the cutoff means an average occupied position or everyone tied at the best occupied position.
For an audit of the average-rank output, enter this formula outside the table:
=AVERAGE(C2:C9)
Expected result: 4.5 because the eight average ranks sum to 36 and 36/8 = 4.5. This is a useful independent checksum for a complete rank list; it does not by itself validate every record or establish native software behaviour. An incomplete range, nonnumeric input or different tie handling may invalidate the check.
Why use RANK.AVG instead of RANK.EQ?
Zoho explicitly documents RANK.EQ as returning the top rank of a tied group. For the 95s and 88s in this dataset, the distinction is:
| Score group | Occupied positions | RANK.EQ |
RANK.AVG |
|---|---|---|---|
| Two scores of 95 | 1 and 2 | 1 | 1.5 |
| Two scores of 88 | 3 and 4 | 3 | 3.5 |
| Single score 82 | 5 | 5 | 5 |
The basic Zoho RANK reference describes ranking direction but does not explicitly spell out its own tie behaviour in the public text retrieved here. Do not silently treat the three functions as identical. Selecting a tie policy is part of designing the ranking system.
Troubleshooting, errors and limitations
The rank contains .5. This is normally expected when two equal numbers occupy adjacent positions. It does not mean the score has changed; it only describes their average rank positions.
Descending versus ascending seems reversed. Zoho documents omitted or zero order as highest-first; a nonzero numeric value as lowest-first. Choose the direction based on what better means for your metric.
Copied results change unexpectedly. Check the range reference: B2:B9 moves when copied, while $B$2:$B$9 remains fixed. Exclude title rows and summary rows from the comparison range.
Apparently equal scores get different ranks. Inspect underlying precision, not just the number of decimal places shown. Two cells displayed as 88 may hold 88 and 88.0001, or an imported value may be text rather than a number. Rounding should reflect the policy’s precision, not serve as an arbitrary fix.
#N/A! appears. Zoho’s help associates this code with a missing requested value or empty list. Verify the input value and range. The vendor’s generic help also describes #NAME! for invalid names, #VALUE! for invalid types and #REF! for bad references. These are documented explanations, not error cases reproduced through native Zoho execution.
Blank values, booleans, text or hidden rows appear in the list. The checked function reference does not exhaustively document all coercion rules, exact errors or filter-sensitive behaviour. Do not import Excel’s or Google Sheets’ handling as presumed Zoho truth. Test a minimal worksheet and confirm which items actually participate before using this for real performance evaluation.
Can rank 1.5 be used to pick one winner? No: both tied top scorers have the same average rank. If you need one winner or exactly N people, define a separate, auditable tie-breaker. An average rank is intentionally not unique.
Excel and Google Sheets comparisons
Microsoft’s Excel RANK.AVG documents RANK.AVG(number,ref,[order]) and shows a comma in English syntax examples. Google’s RANK.AVG documents RANK.AVG(value, data, [is_ascending]), using a boolean ascending setting. Zoho’s own reference gives RANK.AVG(number; number_list; [order]) with zero/nonzero numeric ordering. All three references describe averaging tied rank positions, but that common mathematical objective does not verify identical parsing, type conversion, error codes or supported platforms.
The Zoho Sheet reference was checked on 2026-10-12. It does not establish a first-supported release, account-tier availability or identical results on every browser, mobile integration, desktop/offline build and imported Excel file. Those specifics remain unverified rather than invented.
Related guides: RANK.EQ, RANK, AVERAGE and the software comparison.
Official sources and validation record
- Zoho RANK.AVG function documentation — checked 2026-10-12; direct authority for syntax, argument definitions, default direction and average-rank treatment of duplicates.
- Zoho RANK.EQ and Zoho RANK — checked 2026-10-12 for tie-policy comparisons.
- Microsoft Excel RANK.AVG and Google Sheets RANK.AVG — checked 2026-10-12 for cross-software documentation differences.
- Independent calculation: All eight descending ranks, selected ascending ranks and cutoff-count examples were checked with separate mathematical assertions using the displayed example inputs.
- Not performed: Native Zoho formula execution, account-specific version test, browser/desktop/mobile comparison, screenshot. Independent calculations in another environment do not count as a Zoho Sheet test.