Table of Contents
To calculate the average of a cell containing a number in Excel, use the AVERAGE function. This function takes a range of cells as an input and returns the average value. Simply select the cell or range of cells containing the numbers, enter the AVERAGE function in a new cell, and press enter to calculate the average. This method can be used for both single cells and multiple cells containing numbers, making it a quick and efficient way to calculate averages in Excel.
Excel: Calculate Average If Cell Contains Number
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.
The following tutorials explain how to perform other common tasks in Excel:
Cite this article
stats writer (2024). How can I calculate the average if a cell contains a number in Excel?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-calculate-the-average-if-a-cell-contains-a-number-in-excel/
stats writer. "How can I calculate the average if a cell contains a number in Excel?." PSYCHOLOGICAL SCALES, 27 Jun. 2024, https://scales.arabpsychology.com/stats/how-can-i-calculate-the-average-if-a-cell-contains-a-number-in-excel/.
stats writer. "How can I calculate the average if a cell contains a number in Excel?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-calculate-the-average-if-a-cell-contains-a-number-in-excel/.
stats writer (2024) 'How can I calculate the average if a cell contains a number in Excel?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-calculate-the-average-if-a-cell-contains-a-number-in-excel/.
[1] stats writer, "How can I calculate the average if a cell contains a number in Excel?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How can I calculate the average if a cell contains a number in Excel?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.