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.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
How to Calculate Average If Between Two Values in Excel
How to Calculate a Cumulative Average in Excel
How to Find Weighted Moving Averages in Excel