Basic Formula Series: The Excel® Average Formula
Let’s say that 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.
As with other formulas, the Average formula has its own syntax. Begin the formula in the usual way, 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 AVERAGE IF formula, 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 at task at the onset of a project as it would at the beginning, 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. Each item is first multiplied by its weight multiplier, then the items are summed. The final total is divided 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. Learn to master it and the possibilities are limitless.
Quickest Click: AutoSum:
If the values you are averaging are contiguous –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 presented with the formula, press the Enter key to accept it.
