You need to find the central value of a set of numbers in Excel. The AVERAGE function is the primary tool for this calculation. It sums all the numbers you specify and divides by the count of those numbers. This article explains how to use the AVERAGE function correctly and covers related functions for different scenarios.
Key Takeaways: Using the AVERAGE Function
- AVERAGE function: Calculates the mean by adding numbers and dividing by the count, ignoring text and empty cells.
- AVERAGEA function: Includes text and logical values in its calculation, where text is counted as zero.
- AVERAGEIF function: Calculates an average only for cells that meet a single condition you specify.
What the AVERAGE Function Does
The AVERAGE function computes the arithmetic mean. It is designed to ignore cells that contain text, logical values TRUE and FALSE, or are completely empty. The function only includes cells with numeric values in its calculation. You can provide inputs as individual numbers, cell references like A1:A10, or named ranges.
The basic syntax is =AVERAGE(number1, [number2], …). The first argument is required, but you can add up to 254 additional numbers or ranges. A common point of confusion is how the function handles zeros and blank cells. A cell with a value of zero is included in the calculation and lowers the average. A truly empty cell or a cell with text is ignored entirely.
Steps to Use the AVERAGE Function
Follow these steps to insert and use the AVERAGE function in your worksheet.
- Select the result cell
Click on the cell where you want the calculated average to appear. - Start the formula
Type an equals sign (=) followed by the word AVERAGE and an opening parenthesis: =AVERAGE( - Select your data range
Click and drag your mouse to highlight the cells containing the numbers you want to average. You will see the range reference, like B2:B8, appear in the formula. - Complete the formula
Type a closing parenthesis ) and press the Enter key. Excel will calculate and display the average in the selected cell.
Using the Function Arguments Dialog
You can also use the formula wizard for more control.
- Open the Insert Function dialog
Go to the Formulas tab on the ribbon. Click the Insert Function button, which looks like fx. - Find the AVERAGE function
In the dialog box, type “average” in the search field or select it from the Statistical category list. Click OK. - Enter the arguments
In the Function Arguments dialog, click into the Number1 field. Then, select your data range on the sheet. You can add another range in the Number2 field if needed. - Finish and apply
Click OK. Excel inserts the complete formula into your cell.
Common Mistakes and Limitations
Incorrect Range Selection
A frequent error is selecting a range that includes a total row or a text header. The AVERAGE function ignores text headers, but if your total is a number, it will be incorrectly included in the average. Always double-check that your selected range contains only the individual data points you intend to average.
Confusion Between Blank Cells and Zeros
If a cell appears empty but contains a formula that returns an empty string (“”), the AVERAGE function treats it as text and ignores it. A cell with a formula that returns 0 is counted as a numeric zero. This difference can cause unexpected results. Use the Go To Special feature to find cells with formulas to audit your data.
When to Use AVERAGE vs. AVERAGEA
Do not use AVERAGEA for standard numeric data. The AVERAGEA function evaluates text as zero and TRUE as 1. This behavior will artificially lower your average if your data range contains any text labels. Reserve AVERAGEA for specific cases where you must force a calculation across mixed data types.
AVERAGE vs. AVERAGEA vs. AVERAGEIF: Key Differences
| Item | AVERAGE | AVERAGEA | AVERAGEIF |
|---|---|---|---|
| Primary Use | Standard mean of numbers | Mean including text/logical values | Conditional average |
| Handles Text | Ignores it | Counts as zero | Ignores it in range |
| Handles Logical Values | Ignores TRUE/FALSE | TRUE=1, FALSE=0 | Ignores them |
| Syntax Complexity | =AVERAGE(range) | =AVERAGEA(range) | =AVERAGEIF(range, criteria) |
| Best For | Clean numeric datasets | Data validation across mixed types | Averaging data that meets a rule |
You can now calculate averages for any set of numbers in your worksheets. Use the AVERAGE function for standard data and remember it ignores empty cells. For more complex analysis, explore the AVERAGEIFS function to average based on multiple conditions. A pro tip is to use the keyboard shortcut Alt + M + U + A after selecting a range to quickly insert the AVERAGE function below your data.