Table of Contents
Calculating the average of cells that contain specific text in Excel can be done using the AVERAGEIF function. This function allows you to specify a range of cells and a criteria, and it will only calculate the average of the cells that meet that criteria. For example, if you want to find the average of all cells that contain the text “Apple”, you would use the formula =AVERAGEIF(range of cells, “Apple”). This will only include cells that contain the text “Apple” in the calculation of the average. This feature is useful for analyzing data that is categorized by specific text, such as product names or customer names.
Excel: Calculate Average If Cell Contains Text
You can use the following formula to calculate the average in Excel only for the cells that contain a specific text:
=AVERAGEIF(A1:A13,"*text*",B1:B13)
This particular formula will calculate the average of the values in the range B1:B13 only for the cells that contain “text” in the range A1:A13.
Note: The asterisks are wildcard characters that tell Excel to ignore any text before or after a specific string.
The following example shows how to use this formula in practice.
Example: Calculate Average If Cell Contains Text
Suppose we have the following dataset that shows the points scored by 12 different basketball players:
We can use the following formula to calculate the average points scored by players on the “Mavs” team only:
=AVERAGEIF(A2:A13,"*mavs*",B2:B13)
The following screenshot shows how to use this formula in practice:
The average points scored by players on the “Mavs” team is 25.
We can manually verify that this is correct:
Average Points Scored = (31 + 23 + 21) / 3= 25.
Note that this formula is case-insensitive.
That is, we could use “*MAVS*” or “*mavs*” or “*Mavs*” in the formula and Excel will return the same result.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
Cite this article
stats writer (2024). How can I calculate the average of cells that contain specific text in Excel?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-calculate-the-average-of-cells-that-contain-specific-text-in-excel/
stats writer. "How can I calculate the average of cells that contain specific text in Excel?." PSYCHOLOGICAL SCALES, 1 Jul. 2024, https://scales.arabpsychology.com/stats/how-can-i-calculate-the-average-of-cells-that-contain-specific-text-in-excel/.
stats writer. "How can I calculate the average of cells that contain specific text in Excel?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-calculate-the-average-of-cells-that-contain-specific-text-in-excel/.
stats writer (2024) 'How can I calculate the average of cells that contain specific text in Excel?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-calculate-the-average-of-cells-that-contain-specific-text-in-excel/.
[1] stats writer, "How can I calculate the average of cells that contain specific text in Excel?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, July, 2024.
stats writer. How can I calculate the average of cells that contain specific text in Excel?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.