Statistical
AVERAGE in Excel:syntax, examples and errors
AVERAGE adds the numbers in a range and divides by how many there are. In Spanish Excel it is called PROMEDIO. Syntax: =AVERAGE(number1, [number2], ...).
Syntax
=AVERAGE(number1, [number2], ...)In English Excel, arguments are separated with a comma (,). In Spain and much of Europe and Latin America, Excel uses a semicolon (;).
Arguments
| Argument | What it is |
|---|---|
number1 | Required. The range, cell or number you want the mean of. Example: B2:B6. |
number2, ... | Optional. More ranges, cells or numbers, up to 255. Example: 10. |
How do you use AVERAGE? A step-by-step example
AVERAGE adds the numbers and divides by how many there are. In this example you have the scores of 5 students. Valerie did not take the exam: in column B her cell is blank and in column C it holds a 0.
| A | B | C | |
|---|---|---|---|
| 1 | Student | Score (blank) | Score (with 0) |
| 2 | Lucy | 8 | 8 |
| 3 | Mark | 6 | 6 |
| 4 | Valerie | 0 | |
| 5 | Daniel | 9 | 9 |
| 6 | Cameron | 5 | 5 |
=AVERAGE(B2:B6) returns 7 and =AVERAGE(C2:C6) returns 5.6.
- Type
=AVERAGE(in the average cell. - Select the range of scores, B2 to B6.
- Close the parenthesis and press Enter.
The difference is Valerie. In column B, AVERAGE does not count the blank cell and divides 28 by 4. In column C, the 0 counts and it divides 28 by 5.
Does AVERAGE count blanks, zeros and text?
| The cell holds | Does it count in the average? |
|---|---|
| A number, including 0 | Yes |
| Nothing | No |
| Text ("Not taken") | No |
| A number stored as text | No |
Before you calculate, decide what a blank cell means in your data. If it means "did not take it and counts as 0", type the 0.
AVERAGE or SUM divided by the number of rows?
=SUM(B2:B6)/5 always divides by 5, even with blank cells: with column B it returns 5.6. AVERAGE divides by the numbers it finds: it returns 7. Use AVERAGE unless you want blanks counted as 0. In that case, type the zeros: it is clearer for whoever reads the sheet.
For the average of part of the data (one region, one category) use AVERAGEIF, which works like SUMIF.
How does AVERAGE show up in an Excel test?
In a job interview it often comes with another function: "Compare each store's sales with the average." The answer is =B2-AVERAGE($B$2:$B$6), with the range locked with $ so you can drag it.
In a skills test:
A1:A4 holds 10, 20, an empty cell and 30. What does
=AVERAGE(A1:A4)return? a) 15 · b) 20 · c) #DIV/0! · d) 60
Answer: b. The empty cell does not count: (10 + 20 + 30) / 3 = 20. For the total instead of the mean, see SUM; to tidy a long decimal, see ROUND.
Practice AVERAGE
The English exercises are on the way. Until then, copy the scores into a blank sheet, average both columns, and then try the weighted version with scores 8, 6 and 9 and weights 30%, 30% and 40%.
Available in: all versions of Excel. Source: Microsoft: AVERAGE function. Results calculated with Formulo's engine.
Common mistakes
- The average comes out higher than expected
- There are blank cells where there should be a 0. AVERAGE does not count them. With the scores on this page, the blank cell gives an average of 7 and the 0 gives 5.6. Type the 0 if that person counts in the average.
- #DIV/0!
- The range has no numbers: it is empty or all text. AVERAGE divides by 0. Check the range or show a message with IFERROR.
- The average has too many decimals
- =AVERAGE(B2:C6) returns 6.2222222222. To show fewer decimals, change the cell format. If you need the rounded number for other calculations, use =ROUND(AVERAGE(B2:C6),1), which returns 6.2.
- A text such as "Not taken" does not count
- AVERAGE ignores text in the range, the same as empty cells. If "Not taken" should count as 0, type a 0 and put the note in another column.
Frequently asked questions
What is AVERAGE called in Spanish?
PROMEDIO. In a Spanish Excel it is written =PROMEDIO(B2:B6).
Does AVERAGE count blank cells?
No. Blank cells and text do not count. Zeros do.
How do I do a weighted average?
Multiply each score by its weight, add, and divide by the sum of the weights: =SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4). With scores 8, 6 and 9 and weights 30%, 30% and 40%, it returns 7.8.
How do I average only what meets a condition?
With AVERAGEIF, which works like SUMIF: =AVERAGEIF(range, criteria, [average_range]).
What is the difference between AVERAGE and MEDIAN?
AVERAGE adds and divides. MEDIAN returns the middle value of the sorted data, so an extreme value, such as a 0 among scores of 8, moves it less.
Related functions
Practice in a real spreadsheet
Exercises with real data and instant checking. The first three are free.