RANK.AVG Function (Gnumeric)
Learn the RANK.AVG function in Gnumeric: Returns the ranking of a value in a sample; if more than one value has the same rank, the average rank is returned.
Verification status: Official documentation referenced; example outcomes independently reasoned or arithmetically checked where stated. Not executed in Gnumeric. How formula examples are checked
Quick answer
RANK.AVG gives a numeric score an average rank when the score occurs more than once in the comparison range. In a six-score example 92, 88, 88, 75, 60, 60, =RANK.AVG(88,B2:B7) has expected result 2.5: two values occupy positions 2 and 3, whose arithmetic mean is 2.5. Unlike RANK, this reports fractional tie positions.
Exact Gnumeric syntax and argument rules
=RANK.AVG(x,ref[,order])
x — Required. The numeric score to rank. Use a numeric literal or a reference to a numeric cell.
ref — Required. The collection of numeric scores, usually a worksheet range such as $B$2:$B$7.
order — Optional. 0 (default) = descending: largest score receives rank 1. A nonzero value = ascending: smallest score receives rank 1.
The official Gnumeric Statistics manual documents this exact argument order and the default. Both functions are registered with a required numeric x, required range ref, and optional Boolean-like order indicator. This page uses a comma as the formula-argument separator and a period for decimal examples; other locale settings may require different separators. Enter the separators expected by your local Function Wizard, particularly if importing Excel or LibreOffice examples.
Version and availability: The checked Gnumeric reference is the GNOME project documentation with Gnumeric 1.12.62 as the currently listed 1.12 stable release (checked 2026-10-12). The official catalogue confirms the function exists, but catalogue presence does not prove a specific build or older version has been executed in this research. Gnumeric’s function availability can also depend on enabled components.
Build a reproducible worksheet
Enter these six numeric values into cells B2:B7 (and any identifying labels, such as employees, in A2:A7):
On a narrow screen, scroll this table sideways to see every column.
| Row | A — employee | B — score | C — descending rank | D — ascending rank |
|---|---|---|---|---|
| 2 | Maya | 92 | =RANK.AVG(B2,$B$2:$B$7) |
=RANK.AVG(B2,$B$2:$B$7,1) |
| 3 | Noah | 88 | =RANK.AVG(B3,$B$2:$B$7) |
=RANK.AVG(B3,$B$2:$B$7,1) |
| 4 | Priya | 88 | =RANK.AVG(B4,$B$2:$B$7) |
=RANK.AVG(B4,$B$2:$B$7,1) |
| 5 | Sam | 75 | =RANK.AVG(B5,$B$2:$B$7) |
=RANK.AVG(B5,$B$2:$B$7,1) |
| 6 | Taylor | 60 | =RANK.AVG(B6,$B$2:$B$7) |
=RANK.AVG(B6,$B$2:$B$7,1) |
| 7 | Zoe | 60 | =RANK.AVG(B7,$B$2:$B$7) |
=RANK.AVG(B7,$B$2:$B$7,1) |
Lock the comparison range with $ so every filled-down formula ranks against the same six scores. Copy C2:D2 through row 7 to reproduce the ranking calculation.
On a narrow screen, scroll this table sideways to see every column.
| Row | Expected C (descending) | Expected D (ascending) |
|---|---|---|
| 2 | 1 | 6 |
| 3 | 2.5 | 4.5 |
| 4 | 2.5 | 4.5 |
| 5 | 4 | 3 |
| 6 | 5.5 | 1.5 |
| 7 | 5.5 | 1.5 |
These values are independently calculated expected outputs, not observed Gnumeric results. The calculations implement the documented tie handling and are cross-checked against the GNOME-maintained function source. Native Gnumeric execution was unavailable during draft preparation.
How average ranks are calculated
For ties, calculate the competition rank (1 + count of scores strictly above x when descending), then add half of the number of additional tied values. With scores 92, 88, 88, 75, 60, 60, the two scores at 88 share positions 2 and 3: (2+3)/2 = 2.5. The two scores at 60 share positions 5 and 6 in descending order, so (5+6)/2 = 5.5. In ascending order, these 60s average ranks 1 and 2 to get 1.5. The official GNOME source implements competition_rank + (tie_count - 1)/2.
For a five-value reference 11.4, 17.3, 21.3, 25.9, 25.9, the official Gnumeric documentation gives RANK.AVG(25.9,A1:A5) = 1.5; independently calculating the positions 1 and 2 gives the same result.
Advanced example: compare rankings with and without tie averaging
Using B2:B7, place =RANK(B2,$B$2:$B$7) in C2 and =RANK.AVG(B2,$B$2:$B$7) in D2; copy down. Expected comparisons: 92 → 1 vs 1, 88 → 2 vs 2.5 (both 88 rows), 75 → 4 vs 4, 60 → 5 vs 5.5 (both 60 rows). Rank averages are useful when analysing tied survey scores or contest positions, but they do not create unique positions. A chart or report might treat 2.5 as a fractional statistic rather than a second-place designation; name the column appropriately.
Rank an external benchmark (portable-syntax warning)
In current GNOME-maintained source code, =RANK.AVG(80,$B$2:$B$7) evaluates to a source-inferred rank 4 even though 80 is not in the range, because no tie is found and exactly three scores are higher. Not independently observed inside Gnumeric and not guaranteed portable to Excel/LibreOffice. Use a member score when recreating rank comparisons in other products.
Troubleshooting and limitations
| Symptom | How to check it |
|---|---|
| Rank 2.5 or 5.5 looks unexpected | They are fractional average positions. Use RANK if your report requires whole-number competition ranks. |
| Expected descending order but smallest score is rank 1 | order was set to nonzero. Set order to 0 or omit it. |
| Rank numbers change when copied down | Keep $B$2:$B$7 absolute. Make only B2 relative. |
| Numeric-looking imported scores behave differently | Inspect whether the cells contain numeric values or imported text. GNOME’s function code ignores text, blanks, and Boolean values in the comparison set. |
| Locale parse errors | Verify the Gnumeric argument separator and decimal convention; examples use English-style commas and decimal points. |
| Formula returns an error | Check the x input, the ref range and errors within cells. Specific edge-case error codes were not tested natively in the recorded checks and should not be inferred from Excel help. |
Compatibility, links and release-specific notes
Gnumeric documentation describes RANK.AVG as Excel 2010 compatible, not as an assurance of identical behavior on every edge case. Modern Excel provides a RANK.AVG function with the same broad purpose; Gnumeric’s public catalogue lists Complete implementation / No Testsuite for this function. That testing label is the Gnumeric project’s catalogue entry, not a result from this research.
Related pages in the October 10 site source: RANK, PERCENTRANK, MEDIAN. These are proposed internal links based on verified source filenames; validate the actual generated permalink and any redirect before publishing. Gnumeric’s current 1.12 release directory lists 1.12.62, but this document has not been executed in that binary.
Sources and validation status
- Official Gnumeric Statistics manual — RANK.AVG, checked 2026-10-12: syntax,
orderdefault, tie average rule, Excel 2010 compatibility. - Official Gnumeric function catalogue, checked 2026-10-12: RANK.AVG: Complete / No Testsuite.
- GNOME-maintained source implementation,
plugins/fn-stat/functions.c,help_rank_avg/gnumeric_rank_avgchecked onmaster: numeric comparisons, tie count and fractional ranking; this was not a tagged 1.12.62 read. - Official 1.12 release directory, checked 2026-10-12: lists latest release 1.12.62.
How this guide was checked
The syntax and behaviour are based on the Gnumeric references cited above. The sample calculations were independently checked; they are expected results, not outputs observed in Gnumeric. Test locale-sensitive text conversion and imported files in your own installation. View the example-check record.