Use AutoSum to quickly find the average
AutoSum lets you find the average in a column or row of numbers where there are no blank cells.
- Click a cell below the column or to the right of the row of the numbers for which you want to find the average.
- On the Home tab, click the arrow next to AutoSum > Average, and then press Enter.
Find the average of numbers that aren’t in a contiguous row or column
Use the AVERAGE or the AVERAGEIF functions. Both functions return the arithmetic mean by adding a group of numbers, and then dividing by the count of those numbers. AVERAGE ignores blank cells. The AVERAGEIF function does a bit more by letting you include a condition (that’s the “IF” part of the function).
Find a weighted average
Use the SUMPRODUCT and the SUM functions to find a weighted average, which depends on the weight that’s applied to the values.
For example, a shipment of 10 cases of pencils is 20 cents per case. But a second shipment of 40 cases costs 30 cents per case, because pencils are in high demand. If you averaged the cost of each shipment this way (0.20+0.30)/2 = 0.25, the result isn’t accurate. The math doesn’t take into account that there are more cases being sold at 30 cents than at 20 cents. To get the correct average, use this formula to get the result (28 cents per shipment):
The formula works by dividing the total cost of the two orders by the total number of cases ordered: