Table of Contents
Google Sheets can be used to calculate the average of a set of numbers, even if some of the cells contain text. This can be done by using the AVERAGEIF function, which can be used to filter out non-numeric cells and only calculate the average of the cells that contain numeric data. This can be a useful tool when working with data that contains both numbers and text.
You can use the following formula to calculate the average in Google Sheets only for the cells that contain a specific text:
=AVERAGEIF(A2:A13,"*text*",B2:B13)
This particular formula will calculate the average of the values in the range B2:B13 only for the cells that contain “text” in the range A2:A13.
Note: The asterisks are wildcard characters that tell Google Sheets 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 any team with “avs” in the team name:
=AVERAGEIF(A2:A13,"*avs*",B2:B13)
The following screenshot shows how to use this formula in practice:
The average points scored by players on a team with “avs” in the name is 22.75.
In this particular example, there are two team names with “avs” in the name: Mavs and Cavs.
We can manually calculated the average points scored by players on these teams:
Average Points Scored = (31 + 23 + 21 +16) / 4= 22.75.
This matches the value calculated by our formula.