How to label values above or below average in Excel
Formula (Excel and Google Sheets)
=IF(B2>AVERAGE($B$2:$B$9),"Above average","At or below")Compare each value with the average of the locked range: =IF(B2>AVERAGE($B$2:$B$9),"Above average","At or below").
- Result on the example
- one result per row in
C2, filled down toC9 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in C2, filled down to C9. Change any input and the result updates, or edit the formula to experiment.
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".
=IF(B2>AVERAGE($B$2:$B$9),"Above average","At or below")Result"Above average"
B2142The value in B2.
$B$2:$B$98 rows × 1 col: 142, 118, 165, 97, …A range: 8 cells in one column.
AVERAGE($B$2:$B$9)130AVERAGE: Arithmetic mean of the numbers.- number1
$B$2:$B$98 rows × 1 col: 142, 118, 165, 97, …
- number1
B2>AVERAGE($B$2:$B$9)TRUEThe > operator tests greater than.
IF(B2>AVERAGE($B$2:$B$9),"Above average","At or below")"Above average"IF: Returns one value if a test is true, another if false.- logical_test
B2>AVERAGE($B$2:$B$9)TRUE - value_if_true
"Above average" - value_if_false
"At or below"not used
- logical_test
In Google Sheets
The same formula works in Google Sheets: =IF(B2>AVERAGE($B$2:$B$9),"Above average","At or below"). We ran it in Google Sheets mode on the same data and it returns the same results.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =LET(avg,AVERAGE($B$2:$B$9),IF(B2>avg,"Above average","At or below")) | Above average, At or below, Above average… | Names the average, which helps once the formula grows. |
| =B2-AVERAGE($B$2:$B$9) | 12, -12, 35…Different result | 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.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Label values above or below average
C2, show "Above average" when the agent handled more calls than the team average, otherwise "At or below". Fill down to C9.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.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.