Average Formula in Excel: AVERAGE, MIN, MAX, MEDIAN
· 6 min read
To average a set of numbers, use AVERAGE and give it the range. It adds the numbers and divides by how many there are. MIN and MAX work the same way and return the smallest and largest value:
=AVERAGE(C2:C9)
=MIN(C2:C9)
=MAX(C2:C9)The average formula Excel uses skips empty cells and text, but it does count zeros. That one detail explains most averages that come out "wrong". This guide also covers AVERAGEIF for averages with a condition, and when the median is a better answer than the average.
The example table
Eight orders from a supplies company. One West customer placed an unusually large order. Column F has every formula from this guide, shown with two decimals.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Order | Region | Order value | Formula | Result | |
| 2 | 3001 | North | 145.00 | AVERAGE | 269.38 | |
| 3 | 3002 | West | 98.50 | MIN | 76.25 | |
| 4 | 3003 | North | 210.00 | MAX | 1,240.00 | |
| 5 | 3004 | South | 76.25 | MEDIAN | 138.88 | |
| 6 | 3005 | West | 1,240.00 | AVERAGEIF: West | 501.00 | |
| 7 | 3006 | East | 132.75 | AVERAGEIF: under 1,000 | 130.71 | |
| 8 | 3007 | South | 88.00 | AVERAGEIFS: West, under 1,000 | 131.50 | |
| 9 | 3008 | West | 164.50 |
AVERAGE: the basic formula
=AVERAGE(C2:C9)Result: 269.375, which displays as 269.38 with two decimals. The eight values add up to 2,155, and 2,155 / 8 = 269.375.
You can also average separate cells or ranges: =AVERAGE(C2, C4, C6) averages just those three. To show a clean number, round it with =ROUND(AVERAGE(C2:C9), 2), which our round to 2 decimal places guide explains.
MIN and MAX in Excel
=MIN(C2:C9)
=MAX(C2:C9)Results: 76.25 (order 3004) and 1,240.00 (order 3005). Like AVERAGE, they ignore empty cells and text.
Looking at MIN and MAX next to the average is a good habit. Here the maximum is more than four times the average, which tells you one order is pulling the average up. That leads to the median.
Average vs median
The median is the middle value once the numbers are sorted. With an even number of values, it is the average of the two in the middle.
=MEDIAN(C2:C9)Result: 138.875. Sorted, the values are 76.25, 88.00, 98.50, 132.75, 145.00, 164.50, 210.00 and 1,240.00. The two in the middle are 132.75 and 145.00, and halfway between them is 138.875.
| Measure | Result | What it tells you |
|---|---|---|
| Average | 269.38 | Total value spread evenly over every order |
| Median | 138.88 | What a typical order looks like |
Neither is wrong. They answer different questions:
- Use the average when the total matters. Average order value times the number of orders gives back total revenue, so forecasts and budgets use the average.
- Use the median when you want a typical case and the data has a few extreme values, like one huge order or a handful of very large customers.
If the average and the median are far apart, say so in your report. It usually means a few rows drive the result.
AVERAGEIF: average with a condition
AVERAGEIF works like SUMIF: a range to test, a condition, and the range to average.
=AVERAGEIF(B2:B9, "West", C2:C9)Result: 501.00. The West orders are 98.50, 1,240.00 and 164.50, and their total of 1,503 divided by 3 is 501.
The condition can also be a comparison on the values themselves. Leave out the third argument and AVERAGEIF averages the tested range:
=AVERAGEIF(C2:C9, "<1000")Result: about 130.71, the average of the seven orders under 1,000.
AVERAGEIFS for several conditions
AVERAGEIFS takes the range to average first, then pairs of range and condition, just like SUMIFS. West orders under 1,000:
=AVERAGEIFS(C2:C9, B2:B9, "West", C2:C9, "<1000")Result: 131.50, the average of 98.50 and 164.50. If no rows match, AVERAGEIF and AVERAGEIFS return #DIV/0!, because there is nothing to divide by.
Blank cells vs zeros
Suppose order 3008's value has not come in yet. Try both in the sheet above by editing C9.
- If C9 is empty, AVERAGE uses the other seven values: 1,990.50 / 7 = 284.36.
- If someone typed 0 in C9, AVERAGE divides by eight: 1,990.50 / 8 = 248.81.
A zero is a value. An empty cell is not. If zeros in your data really mean "no data yet", exclude them with:
=AVERAGEIF(C2:C9, "<>0")Common mistakes
Averaging averages. This is a classic mistake, and AI assistants make it when asked for an overall figure from a summary table. Here are four regions with revenue, order counts and average order value (AOV):
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Region | Revenue | Orders | AOV |
| 2 | North | 12,400 | 40 | 310.00 |
| 3 | South | 3,150 | 9 | 350.00 |
| 4 | East | 8,820 | 28 | 315.00 |
| 5 | West | 15,600 | 60 | 260.00 |
| 6 | ||||
| 7 | AI's formula | 308.75 | ||
| 8 | Fixed | 291.75 |
Ask for "the company's average order value" and you may get =AVERAGE(D2:D5), which returns 308.75. But South has only 9 orders and counts as much as West's 60. The real AOV is total revenue divided by total orders:
=SUM(B2:B5)/SUM(C2:C5)Result: 291.75 (39,970 / 137). Whenever an assistant averages a column that is already a ratio or a percentage, check whether the groups are the same size. If they are not, go back to the totals.
Counting zeros that mean "missing". As shown above, a zero placeholder drags the average down. Check what an empty value looks like in your data.
Average of a range that includes the total row. =AVERAGE(C2:C10) with a total in C10 counts the total as one more order. Keep total rows outside the range, or check where the range ends. See how to sum a column for the same trap with SUM.
Reporting the average when the median tells the truth. One large order can make a region look much stronger than it is. Show both when they differ a lot.
Wrong argument order in AVERAGEIFS. It puts the average range first, while AVERAGEIF puts it last. The same trap exists with SUMIF and SUMIFS: see SUMIF with multiple criteria.
FAQ
What is the formula for average in Excel?
=AVERAGE(range), for example =AVERAGE(C2:C9). It adds the numbers in the range and divides by how many numbers there are, skipping empty cells and text.
How do I find the min and max in Excel?
Use =MIN(range) for the smallest value and =MAX(range) for the largest. AutoSum's drop-down menu on the Home tab can insert both.
How do I use AVERAGEIF?
Give it the range to test, the condition and the range to average: =AVERAGEIF(B2:B9, "West", C2:C9). Use AVERAGEIFS for more than one condition.
Should I use the average or the median?
Use the average when you care about totals, and the median when you want a typical value and a few extreme numbers would distort the average.
Does AVERAGE ignore blank cells?
Yes. Empty cells and text are skipped. Cells that contain 0 are included, which lowers the average.
Practise this on a realistic dataset
Solve it yourself on one company's data, with instant feedback that points at what's wrong without giving away the answer.
Try “Average, biggest, smallest” →