Math and statistics
AVERAGE function playground
Returns the arithmetic mean: the total of the numbers divided by how many there are.
The playground runs in your browser. Nothing you type is sent anywhere.
How AVERAGE works
AVERAGE adds the numbers and divides by the count of numbers. In this sheet, D4 says "pending" and D9 is empty, so AVERAGE(D2:D9) uses only the six cells that hold numbers.
Blank cells are skipped, but a cell that contains 0 is a number and is included, which pulls the average down. If there are no numbers at all, AVERAGE returns #DIV/0!.
Syntax
=AVERAGE(number1, [number2], ...)| Argument | What it means |
|---|---|
number1required | The first number, cell or range to average. |
number2, ...optional | More numbers, cells or ranges. |
Examples
| Formula | What it does | Result |
|---|---|---|
=AVERAGE(D2:D9) | Average order amount. "pending" and the blank cell are ignored. | $231.00 |
=AVERAGE(C2:C9) | Average quantity. The text "n/a" is skipped. | 11.5 |
=ROUND(AVERAGE(D2:D9),0) | Average rounded to a whole number. | $231.00 |
=SUM(D2:D9)/COUNT(D2:D9) | The same result, written out the long way. | 231 |
Common mistakes
Zeros vs blanksA 0 counts as a value; an empty cell doesn't. Typing 0 for "no data" lowers the average.
#DIV/0!Happens when the range has no numbers at all. Wrap it in IFERROR if that can happen.
Related functions
Learn more
Download the example workbook (.xlsx)
The workbook has the same sample data and formulas, plus a practice sheet.