# How to label values above or below average in Excel

Compare each value with the average of the locked range: =IF(B2>AVERAGE($B$2:$B$9),"Above average","At or below").

**Excel:** `=IF(B2>AVERAGE($B$2:$B$9),"Above average","At or below")` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C |
| --- | --- | --- |
| Agent | Calls handled | vs team |
| Ana | 142 |  |
| Ben | 118 |  |
| Cara | 165 |  |
| Dev | 97 |  |
| Eli | 130 |  |
| Fay | 151 |  |
| Gus | 109 |  |
| Hal | 128 |  |

Formula in C2, filled to C9: `=IF(B2>AVERAGE($B$2:$B$9),"Above average","At or below")`

Result: Above average; At or below; Above average; At or below; At or below; Above average; At or below; At or below

## How it works

AVERAGE of the locked range is the team average, 130 here, and every row compares its own value with it. Eli handled exactly 130, so he is "At or below".

## Other ways

- `=LET(avg,AVERAGE($B$2:$B$9),IF(B2>avg,"Above average","At or below"))`: Names the average, which helps once the formula grows.
- `=B2-AVERAGE($B$2:$B$9)`: How far above or below average each agent is. (Shows the difference: 12, -12, 35, -33, 0, 21, -21 and -2.)

## Common mistakes

- Lock the range with $, or each row averages a different slice of the list.
- Zeros count in AVERAGE; blanks don't. One agent with 0 calls pulls the average down for everyone.

## Questions

**How do I compare with the median instead?**

`=IF(B2>MEDIAN($B$2:$B$9),"Above","At or below")`.

**How do I highlight above-average values?**

Use `=$B2>AVERAGE($B$2:$B$9)` as a conditional formatting rule, or the built-in Above Average rule.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/label-values-above-or-below-average