Basic Excel, Excel
Apr 20, 2026
Basic Formula Series: The Excel Average Formula
Key Takeaways
- The Excel AVERAGE function calculates the arithmetic mean of a range of numbers using the syntax =AVERAGE(number1, [number2], ...).
- AVERAGE automatically ignores blank cells and text values but includes zeros in its calculation.
- Use AVERAGEIF or AVERAGEIFS when you need to calculate an average based on specific conditions, such as excluding zeros.
- For a weighted average, combine the SUMPRODUCT and SUM functions instead of using AVERAGE alone.
What Is the Excel Average Formula?
The AVERAGE function is one of the most commonly used basic Excel formulas. It calculates the arithmetic mean of a set of values by adding them together and dividing by the count of numeric entries. If you need to know how to calculate average in Excel, this is the function to learn first.
For example, let's say you've been given a new set of projects to work on and you're being asked to calculate how long you think your team will need to complete various pieces of each project. The best way to obtain those numbers is probably to look at other projects you've completed and calculate the average amount of time those pieces took. Excel's Average formula makes that calculation easily.
AVERAGE Function Syntax
The basic syntax for the AVERAGE function is:
=AVERAGE(number1, [number2], ...)
The first argument, "number1," is required. Everything after that is optional. The function accepts up to 255 arguments and each argument can be any of the following:
- A numeric value typed directly into the formula
- A cell reference such as B2
- A range of cells such as B2:B10
- A named range you've defined in your workbook
How AVERAGE Handles Your Data
Learn how the AVERAGE function treats different types of data to avoid unexpected results. Here is a quick reference:
| Data Type | Included in Calculation? | Notes |
|---|---|---|
| Numbers | Yes | Standard numeric values are always included |
| Blank cells | No | Ignored entirely, not counted as zero |
| Cells containing zero | Yes | Zeros are valid numbers and affect your result |
| Text values | No | The function skips text and empty strings ("") |
| Logical values in cells | No | The function ignores TRUE/FALSE stored in cells |
| Logical values typed in formula | Yes | =AVERAGE(TRUE, 1, 2) counts TRUE as 1 |
| Error values | Error | Any error in the range makes the formula return an error |
Because the function ignores blank cells rather than treating them as zeros, empty rows won't artificially lower your average. If you need to include blanks as zeros, enter 0 in those cells first.
How to Use the Average Formula in Excel
As we covered above, the AVERAGE formula uses a straightforward syntax. Begin the formula with an equal sign and the formula name, followed by parentheses. Notice that the tooltip prompts you to provide numbers to be averaged. You can provide numbers, or cell addresses or ranges.
In this example, we've averaged the total hours for a series of project tasks in cell C17 using the formula =AVERAGE(C2:C16).
If you want to only average particular tasks, such as just the Research tasks, you can just isolate those cells.
But sometimes when you think you need to use the AVERAGE formula, you may need a different function entirely. For example, if you want to average the number of hours but exclude those with values of zero, you need to use the AVERAGEIF function and make your criteria ">0." And if you want to calculate a weighted average, such as if you want to figure that you will spend more time on tasks at the beginning of a project when people are learning about the new job than they will at the end of it when they are more familiar with it, then you need to use the SUMPRODUCT formula. In the SUMPRODUCT scenario, one column would contain your hours, and another column would contain a "weight" multiplier. If you felt that it would take you three times as long to complete a task at the onset of a project as it would at the end, the weight would be three. In the middle of the project, it might be two and at the end, it might be one, when you were at full speed.
The chart below shows weighted average project hours calculated in just that way. First, multiply each item by its weight multiplier, then sum the items. Divide the final total by the number of items in the list to obtain the weighted average.
The average formula is versatile and helpful in a number of different ways. Like most functions, it can be combined with other formulas to become even more powerful. Master it and you'll have a strong foundation for the tips and shortcuts that follow.
Quick Tip: Use AutoSum for Contiguous Cells
If you want to average contiguous values (next to each other), you can use the AutoSum feature to save time. Just place your cursor where you want the average to appear, then click the AutoSum dropdown arrow in the Editing group on the Home tab and select Average. When Excel presents the formula, press the Enter key to accept it.
Related Excel Functions to Explore
Once you're comfortable with the AVERAGE function, consider exploring these related functions to expand your Excel toolkit:
- AVERAGEIF and AVERAGEIFS for calculating conditional averages based on one or more criteria
- MEDIAN for finding the middle value in a data set, useful when outliers skew the mean
- MIN and MAX for identifying the smallest and largest values in a range
- SUMPRODUCT for building weighted average calculations with custom multipliers
- COUNT and COUNTA for counting cells that contain numbers or any data
These functions pair well with AVERAGE and will help you handle a wider range of analysis tasks. Pryor Learning offers Excel training courses that cover these functions and more.
