Table of Contents
In Excel, to calculate the average of a range of cells that contain numbers, use the AVERAGE function. This function takes the range of cells as its argument, and returns the arithmetic mean of the numbers in the range. This function can be used in combination with other functions to filter out cells that don’t contain numbers, such as the IF and ISNUMBER functions. The syntax of the AVERAGE function is AVERAGE(range) where range is a range of cells that contain numbers.
You can use the following formula to calculate the average in Excel only for the cells that contain a number in a corresponding range:
=SUMPRODUCT(--ISNUMBER(A2:A11), B2:B11) / COUNT(A2:A11)
This particular formula will calculate the average of the values in the range B2:B11 only for the cells that contain a number in the range A2:A11.
The following example shows how to use this formula in practice.
Example: Calculate Average If Cell Contains Number
Suppose we have the following dataset in Excel that shows the number of sales made by employees at a company with certain ID’s:
We can use the following formula to calculate the average sales made by only the employees who have a numeric value for ID:
=SUMPRODUCT(--ISNUMBER(A2:A11), B2:B11) / COUNT(A2:A11)
The following screenshot shows how to use this formula in practice:
The average sales among employees with a numeric ID is 12.
We can manually verify that this is correct by identifying which employees have numeric ID’s:
The average sales made by these employees can be calculated as:
Average Sales = (19 + 4 + 8 +17) / 4 = 12.
This matches the value calculated using our formula.