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.
