RANK function in Google Sheets: RANK, RANK.EQ and RANK.AVG
RANK returns the position of a value in a list of numbers: 1 for the largest by default. This guide shows the syntax, how RANK.EQ and RANK.AVG treat ties, ranking in ascending order, a rank without gaps, a unique rank for tied values, ranking within a group, and the absolute-reference mistake that silently gives wrong ranks.
The RANK function returns the position of a number in a list of numbers. The largest value gets rank 1:
=RANK(C3, C$3:C$12)
Fill the formula down and every row shows its place in the list. For the smallest value to be rank 1, add 1 as the third argument: =RANK(C3, C$3:C$12, 1).
Sheets has three ranking functions. They differ only in what they do with ties:
| Function | Two values tied for 4th place get | Next rank |
|---|---|---|
RANK |
4 and 4 | 6 |
RANK.EQ |
4 and 4 | 6 |
RANK.AVG |
4.5 and 4.5 | 6 |
RANK and RANK.EQ return the same results.
RANK syntax
=RANK(value, data, [is_ascending])
- value: the number to rank.
- data: the range that holds all the numbers, the value included.
- is_ascending: optional. 0 or omitted ranks the largest value first; 1 ranks the smallest first.
RANK.EQ and RANK.AVG take the same arguments.
=RANK(C3, C$3:C$12, 0)
The whole column can be ranked by one formula:
=ARRAYFORMULA(RANK(C3:C12, C3:C12, 0))
Lock the range, or the ranks are wrong
Lock the range, or the ranks are wrong
The dollar signs in C$3:C$12 keep the range in place when the formula is filled down. Without them the range moves with every row:
=RANK.EQ(C3, C3:C12, 0)
In row 10 the formula has become =RANK.EQ(C10, C10:C19, 0): Harper is ranked only against the two rows below and shows rank 2, although three reps sold more. There is no error message, and the first rows look right. Compare with the correct ranks in the first screenshot, where Harper is 4th.
OWOX Data Marts
See your first report built in real time. 15 minutes.
- Connect your data warehouse
- Pick your metrics
- Get a live Google Sheets report
In the time it takes to write a ticket. Then imagine never writing that ticket again.
Book a DemoWe'll use your actual use case
Ties: RANK.EQ and RANK.AVG
When two values are equal, RANK and RANK.EQ give both the higher place and skip the next one:
=RANK.EQ(C3, C$3:C$12)
RANK.AVG gives tied values the average of the places they occupy. Two values that share places 4 and 5 both get 4.5:
=RANK.AVG(C3, C$3:C$12, 0)
The average rank is used in statistics, where the ranks have to add up to the same total with or without ties. For a leaderboard, RANK.EQ is what readers expect.
A rank without gaps
RANK skips places after a tie: 1, 1, 3. To number the distinct values 1, 2, 3 with no gaps (a dense rank), count how many distinct values are larger:
=SUMPRODUCT((C$3:C$12 > C3) / COUNTIF(C$3:C$12, C$3:C$12)) + 1
A unique rank for tied values
To give tied values different ranks, in the order they appear in the list, add the number of earlier occurrences:
=RANK(C3, C$3:C$12) + COUNTIF(C$3:C3, C3) - 1
The first of two tied values keeps the rank, the second gets the next one. This is the form to use when the rank feeds a lookup that needs every rank exactly once.
Rank within a group
RANK has no condition argument. To rank each row among the rows of its own group, such as its region, count the rows of the same group with a larger value:
=COUNTIFS(B$3:B$12, B3, C$3:C$12, ">" & C3) + 1
With the group in column B and the value in column C, the formula returns 1 for the largest value of each group. It treats ties as RANK does. See COUNTIF and COUNTIFS.
The top N rows
Often the rank is only a step towards a top list. These return it directly:
=LARGE(C3:C12, 3)returns the third largest value.=SORTN(A3:C12, 3, 0, 3, FALSE)returns the three rows with the largest values in the third column. See SORT and SORTN.=QUERY(A2:C12, "select * order by C desc limit 3", 1)does the same with QUERY.
To color the top three in place, use conditional formatting with the custom formula =RANK($C3, $C$3:$C$12) <= 3.
Errors and wrong results
⚠️ Error: #N/A
✅ Solution: The value is not in the data range. RANK only ranks numbers that are in the list: check that the range covers the row of the formula.
⚠️ Error: #VALUE!
✅ Solution: The value is text. Numbers imported as text have to be converted with VALUE first; text cells inside the data range are ignored.
⚠️ Error: The ranks get smaller towards the bottom of the column and several rows show 1.
✅ Solution: The range is not locked. Write it as C$3:C$12.
⚠️ Error: A rank is missing: 1, 2, 2, 4.
✅ Solution: That is how ties are ranked. Use the dense-rank formula above for 1, 2, 2, 3.
⚠️ Error: The best result has the highest number.
✅ Solution: The third argument decides the direction. For values where smaller is better, such as cost or time, use 1.
Related Google Sheets functions
Related Google Sheets functions
- SORT and SORTN: Order rows and take the top N.
- MAX, MIN and MEDIAN: The largest, smallest and middle values, with LARGE and SMALL.
- PERCENTRANK: The rank as a share of the list.
- PERCENTILE and QUARTILE: Cut points of a distribution.
- COUNTIF and COUNTIFS: The building blocks of conditional ranks.
- How to sort data: Sorting from the menu.
- Array formulas: One formula for a whole column.
A ranking is a snapshot of its rows
A ranking is a snapshot of its rows
A rank says who is first among the rows in the range. Add last week of sales and the order changes; leave out a region by mistake and the leader of the list is not the leader. Rankings built on pasted exports are right for the day of the export, and the sheet gives no sign of how old that day is.
The OWOX Data Marts extension for Google Sheets covers that half. A data analyst publishes a data mart (a table or an SQL query in your data warehouse) and makes it available for reports. In Sheets you open the extension, pick the data mart, tick the columns you want and run it. The rows land in a tab, ready for your formulas.
What changes for the person who maintains the sheet:
- Refresh replaces re-export. Refresh current report in the extension menu re-runs the same pull, and a scheduled refresh keeps the tab current without anyone touching it.
- The source is written on the sheet. The header cell of an imported tab carries a note with the data mart it came from, the time of the import and a link to the report.
- No SQL and no warehouse access needed. The analyst decides which data marts and columns are available. You choose from that list.
The extension does not write or change the analyst’s SQL. The ranking formulas then recalculate on every refresh, over the same complete set of rows for everyone. The extension comes with the OWOX Cloud plans of OWOX Data Marts; spreadsheet reporting describes both ways data reaches a sheet.
Turn your data into decisions.
Governed data marts give you the clean foundation ML needs to actually work.
- No AI hallucinations
- Analyst-governed definitions
- Every number traces to SQL
Frequently Asked Questions
How do I handle ties when using the RANK function?
To handle ties, use RANK.AVG or RANK.EQ. RANK.AVG assigns the average rank to tied values, while RANK.EQ assigns the same rank to tied values but skips subsequent ranks.
Can I combine the RANK function with conditional formatting?
Yes, you can apply conditional formatting to highlight top-ranked values, lowest-ranked items, or specific percentile ranges to improve data visualization and analysis.
How do I rank percentages or decimal values in Google Sheets?
The RANK function works with all numeric values, including percentages and decimals. Ensure your data is formatted correctly to avoid errors in ranking calculations.
How do I rank only specific values within a filtered dataset?
RANK has no condition argument. To rank a row among the rows of its own category, count the rows of that category with a larger value: COUNTIFS(B$3:B$12, B3, C$3:C$12, ">" & C3) + 1, with the category in column B and the value in column C. To rank only the rows that pass a filter, build the filtered list with FILTER first and rank inside it.
Can I rank values in ascending order instead of descending?
Yes, by setting the optional order argument to 1 in the RANK function, you can rank values in ascending order instead of the default descending order.
What is the difference between RANK, RANK.EQ, and RANK.AVG in Google Sheets?
RANK and RANK.EQ return the same result: tied values share the higher place and the next place is skipped, for example 4, 4, 6. RANK.AVG gives tied values the average of the places they occupy, 4.5 and 4.5. All three take the same arguments: the value, the range and an optional 1 for ascending order.
What is the syntax for the RANK function in Google Sheets?
The syntax for the RANK function in Google Sheets is:
=RANK(value, data, [is_ascending])
Where value is what you're ranking, data is the range, and is_ascending is optional for ranking in ascending or descending order.
