Table of Contents
If a cell contains text, you cannot calculate the average. Instead, you will need to replace the text with an appropriate numerical value before calculating the average. For example, if the cell contains “low”, you could replace it with the value 1, and if it contains “high” you could replace it with the value 5. Once all of the cells contain numerical values, you can use the AVERAGE function to calculate the average of the values.
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.