Statistical

RANK.AVG Function in Excel

Returns the rank of a number, with average rank for ties.

Syntax

  • =RANK.AVG(number, ref, [order])

Arguments

  • number (required): Number to find rank of
  • ref (required): Range of numbers to rank against
  • order (optional): 0=descending (default), 1=ascending

Examples

  • =RANK.AVG(A2, A2:A100) - Rank with average for ties - Result: Rank position

RANK.AVG for data analysis

  • Confirm whether you need entire columns, filtered subsets, or distinct values.
  • Use [COUNTIFS](/functions/countifs/) and [SUMIFS](/functions/sumifs/) for multi-criteria metrics.
  • Pivot tables complement single-cell statistical formulas for exploration.

Common errors

  • Returns decimal for tied values

Use cases

  • Statistical ranking
  • Fair tie handling
  • Academic grading

Frequently asked questions

  • When should I use RANK.AVG instead of RANK.EQ? Use RANK.AVG for statistical analysis where you need the sum of ranks to equal n(n+1)/2. It's mathematically correct for correlation calculations and non-parametric tests. Use RANK.EQ for leaderboards and competitions.

Editorial review

  • Reviewed by Excel.Directory Editorial Team. Updated May 2026.

When to use RANK.AVG

  • Statistical ranking — common Statistical scenario for RANK.AVG.
  • Fair tie handling — common Statistical scenario for RANK.AVG.
  • Academic grading — common Statistical scenario for RANK.AVG.

RANK.AVG in the Statistical category

  • Browse all Statistical functions at /categories/statistical/ for related formulas.
  • RANK.AVG syntax: =RANK.AVG(number, ref, [order])
  • number (required): Number to find rank of
  • ref (required): Range of numbers to rank against
  • order (optional): 0=descending (default), 1=ascending
  • Confirm RANK.AVG arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.

Formula checklist before you copy down

  • Confirm RANK.AVG arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.
  • If results look wrong, check for text stored as numbers and invisible spaces with TRIM.
  • Spot-check three known input rows manually against expected output.

Related Excel functions

  • RANK.EQ (/functions/rank-eq/): Returns the rank of a number in a list of numbers.
  • LARGE (/functions/large/): Returns the k-th largest value in a data set.
  • SMALL (/functions/small/): Returns the k-th smallest value in a data set.

Errors to watch for

  • Returns decimal for tied values — review causes on linked error pages in the directory.

Copy-paste audit workflow

  • Enter RANK.AVG on three test rows with known expected output documented on a QA tab.
  • Fill down only after absolute references are locked on lookup tables and rate tables.
  • Compare against manual calculation or a calculator for financial and statistical functions.
  • Search this directory for comparison guides when choosing between similar functions in the same category.

RANK.AVG worked examples to copy

  • =RANK.AVG(A2, A2:A100) — Rank with average for ties. Expected result: Rank position.

RANK.AVG reference summary for crawlers and offline review

  • RANK.AVG belongs to the Statistical category in Excel. Returns the rank of a number, with average rank for ties.
  • Full syntax: =RANK.AVG(number, ref, [order]). Open /functions/rank-avg/ for parameters, FAQs, and related pages.
  • Common mistakes: Returns decimal for tied values
  • Pair this function with comparison guides when another Excel formula might fit the same task better.
  • Review fix-excel-formula-errors when unexpected errors appear after upgrading Excel or sharing across locales.