AVERAGE Function in Excel and Google Sheets
Purpose
Calculate the arithmetic mean of a set of numbers, ignoring blank and text cells
Return value
The arithmetic mean (sum divided by count) of all numeric values in the supplied arguments
AVERAGE is the function I use every day -- it is as fundamental as SUM and COUNT and sits at the centre of any data analysis. Average sales per rep. Average score across a class. Average delivery time over a month. Average spend per customer. Every dataset I work with has at least one question about the typical or expected value, and AVERAGE answers it in two seconds. The behaviour that surprises people initially is that AVERAGE ignores blank cells and text -- it is not averaging in zeros for missing values, it is treating them as non-existent. That is usually exactly the right behaviour (a missing score should not drag down an average) but occasionally you need to be aware of it. For averages conditional on a category or threshold, AVERAGEIF and AVERAGEIFS extend this function with criteria, the same way SUMIF and COUNTIF extend SUM and COUNT. Understanding AVERAGE fully means understanding what it counts and what it skips -- and that knowledge flows directly into understanding the entire IF family of functions.
Syntax
✓ Excel 2003+
✓ Google Sheets
= Same syntax
=AVERAGE(number1, [number2], ...)
Arguments
| Argument | Required | Description |
|---|---|---|
| number1 | Required | The first range, cell, or value to include. Can be a range like A2:A100, an individual cell, or a literal number. Text and blank cells in the range are ignored. |
| number2 ... | Optional | Additional ranges, cells, or numbers up to 255 arguments. All numeric values across all arguments are pooled and averaged together. |
How it works
AVERAGE adds all numeric values in the supplied arguments and divides by the count of those values. Blank cells are excluded from both the numerator (sum) and the denominator (count), so they do not deflate the average. Text cells are also excluded. Cells containing errors like #N/A or #DIV/0! cause AVERAGE to return an error -- wrap error-producing cells in IFERROR(cell,0) to treat them as zero, or use AGGREGATE(1,6,range) which ignores error values automatically. Dates are stored as numbers and are averaged numerically -- the result is a date serial number that displays as a date when formatted correctly. For a conditional average by category, AVERAGEIF adds a single criterion. For multiple criteria simultaneously, AVERAGEIFS handles them with AND logic. To double-check an AVERAGE result, you can manually verify using the COUNT function to confirm how many cells were included in the denominator.
Examples
1
Calculate the average test score for a class, ignoring blank cells for students who were absent.
| A | B | |
|---|---|---|
| 1 | Student | Score |
| 2 | Alice | 82 |
| 3 | Bob | 91 |
| 4 | Carol | 78 |
| 5 | Dan | |
| 6 | Ella | 85 |
| 7 | Finn | 67 |
| 8 | Gia | 94 |
| 9 | Hugo | |
| 10 | Iris | 88 |
| 11 | Jo | 75 |
| 12 | Average | =AVERAGE(B2:B11) |
Row 2: 82.5
— Eight students have numeric scores totalling 660. Dan and Hugo are blank and are excluded from both the sum and the count. 660 divided by 8 equals 82.5.
If Dan and Hugo had been entered as 0 instead of blank, the average would be 660 divided by 10 = 66 -- a significantly lower and misleading result. AVERAGE's blank-ignoring behaviour is correct here: absent students should not drag down the class average.
2
Calculate the average order value for two separate order columns combined in a single AVERAGE formula.
| A | B | C | |
|---|---|---|---|
| 1 | Rep | Jan Orders | Feb Orders |
| 2 | Alice | 4200 | 5100 |
| 3 | Bob | 3100 | 4900 |
| 4 | Carol | 5500 | 3800 |
| 5 | Dan | 3800 | 4700 |
| 6 | Ella | 4100 | 4400 |
| 7 | Average both months | =AVERAGE(B2:B6,D2:D6) |
Row 2: 4360
— Ten values total: 4200+3100+5500+3800+4100+5100+4900+3800+4700+4400 = 43600. 43600 divided by 10 = 4360.
Comma-separated range arguments pool all values and average them together as one set. This is the same multi-range pattern available in SUM and COUNT -- all three functions accept up to 255 arguments.
3
Use AVERAGEIF to compare the overall average with the North region average to see how the region performs against the whole dataset.
| A | B | C | |
|---|---|---|---|
| 1 | Rep | Region | Sales |
| 2 | Alice | North | 4200 |
| 3 | Bob | South | 3100 |
| 4 | Carol | East | 5500 |
| 5 | Dan | North | 3800 |
| 6 | Ella | South | 4900 |
| 7 | Finn | North | 5100 |
| 8 | Gia | East | 2800 |
| 9 | Hugo | North | 4700 |
| 10 | Iris | South | 3600 |
| 11 | Jo | East | 4100 |
| 12 | Overall avg | =AVERAGE(C2:C11) | 4200 |
| 13 | North avg | =AVERAGEIF(B2:B11,"North",C2:C11) | 4450 |
Row 2: 4450
— North region: Alice 4200, Dan 3800, Finn 5100, Hugo 4700 -- total 17800 divided by 4 equals 4450. The overall average across all 10 reps is 4200. North performs above average.
AVERAGEIF extends AVERAGE with a condition -- it accepts the same criteria syntax as COUNTIF and SUMIF. The AVERAGEIF function is the natural next step when an unconditional AVERAGE is not specific enough.
Common use cases
1. Calculate the mean test score for a class while ignoring absent students who have blank entries
=AVERAGE(B2:B50)
2. Average sales across two separate time periods in a single formula
=AVERAGE(B2:B13,D2:D13)
3. Find the average for a specific category using AVERAGEIF
=AVERAGEIF(B2:B100,"North",C2:C100)
4. Calculate a rolling 7-day average from a daily data column
=AVERAGE(B2:B8)
5. Compare the average across multiple groups using AVERAGEIF in a summary table
=AVERAGEIF($B$2:$B$100,F2,$C$2:$C$100)
Common errors
#DIV/0! error
The range contains no numeric values -- all cells are blank or text. AVERAGE has no values to sum and cannot divide by zero.
Fix: Check that the range contains numeric data. Wrap in IFERROR: =IFERROR(AVERAGE(B2:B100),"No data") to show a message when the range is empty.
Average is unexpectedly low
Some numbers in the range are stored as text and are being excluded. The count used in the denominator is smaller than you expect.
Fix: Check with ISNUMBER(B2) on suspicious cells. If FALSE, the value is text. Convert text-numbers to real numbers using Data > Text to Columns or by multiplying by 1.
Average includes values you wanted to exclude
The range is wider than intended and includes rows from another data section.
Fix: Use a precise range with exact row numbers rather than a whole-column reference. Or use AVERAGEIF with a condition to restrict which rows are averaged.
Tips and variations
Use IFERROR to handle empty ranges cleanly
When a data column might be entirely empty, AVERAGE returns #DIV/0!. Wrap in IFERROR: =IFERROR(AVERAGE(B2:B100),"No data") to display a message instead of an error. This is essential in any template where data may not yet be entered.
=IFERROR(AVERAGE(B2:B100),0)
Verify the count with COUNT to understand what AVERAGE included
When an average looks wrong, check =COUNT(B2:B100) to see how many cells AVERAGE actually used. If COUNT returns fewer cells than you expect, some values are text or blank and AVERAGE is excluding them.
=COUNT(B2:B100)
Step up to AVERAGEIF for category-based averages
Once you are comfortable with AVERAGE, AVERAGEIF adds a single condition -- average only the rows matching a criterion. It follows the same three-argument pattern as SUMIF and COUNTIF, making it quick to learn if you already know those functions.
=AVERAGEIF(B2:B100,"North",C2:C100)
Excel vs Google Sheets
Excel vs Google Sheets
AVERAGE works identically in Excel 2003 and newer and every version of Google Sheets. The blank-cell exclusion behaviour, treatment of text, and multi-argument syntax are the same in both applications. Formulas copy between them without changes.
Related reading
Related Functions
Frequently asked questions
AVERAGE calculates the arithmetic mean of a set of numbers -- the sum of all values divided by the count of values. Blank cells and cells containing text are ignored, so they do not affect the result. AVERAGE is the most common way to find the typical value in a dataset and is available in every version of Excel and Google Sheets.
Yes. AVERAGE skips blank cells and text cells entirely -- they are not included in either the sum or the count. This means =AVERAGE(1,2,,4) returns 2.33 (the average of three values: 1, 2, and 4) not 1.75 (which would include the blank as zero). If you want blank cells to count as zero, convert them to zeros before averaging, or use SUM(range)/COUNT(range) where you control what gets included.
AVERAGE calculates the mean of all numeric cells in a range unconditionally. AVERAGEIF averages only the cells that meet a specific criterion -- for example, only rows where the region column equals North. Use AVERAGE for an overall mean and AVERAGEIF when the average should depend on a condition in another column.
Use SUMPRODUCT divided by SUM: =SUMPRODUCT(values,weights)/SUM(weights). For example, if scores are in A2:A10 and weights are in B2:B10, =SUMPRODUCT(A2:A10,B2:B10)/SUM(B2:B10) gives the weighted average. The AVERAGE function always gives equal weight to each value and cannot directly produce a weighted average.
The most common reason is that some cells in the range contain text or are blank. AVERAGE excludes those cells from both the sum and the count, which changes the denominator. A second reason is that numbers formatted as text are skipped -- if imported data shows numbers that look numeric but are stored as text, AVERAGE ignores them. Check with ISNUMBER to verify cells are genuinely numeric.