Sakibul Hasan Nahid
Normally the functions we use to calculate the average of something always take input of numeric values and calculate the average of numeric values. However, we can have datasets for different purposes and we may need to calculate the average of text in Excel. In this article, we are going to learn easy ways to calculate the average of text in Excel.
Here, we can see an overview of the methods and outcomes.
📁 Download Excel File
Download the Excel file here.
Learn to Calculate Average of Text in Excel with These 3 Approaches
Here, we will learn three approaches to calculate the average of text in Excel. Here we have a dataset that describes diamond quality. The first column tells us the weight of the diamond. Carat is the unit of measurement for the physical weight of precious stones like diamonds. And the second column represents the style or shape of the diamond. A diamond’s cut relates to the proportionality of its size and the positioning of its surfaces, or faces, to generate sparkle and brilliance.
1. Determine Average of Text Using SUM and COUNTIF Functions
In the first method, we will calculate the average of text in Excel using the SUM and COUNTIF functions. Here we will calculate the average percentage of the type of cuts. In the dataset, there are 4 types of cuts: Premium, Good, Very Good, and Ideal. Now, we will calculate their average percentage of cut types. For example, among these 10 examples, what is the average percentage of a good cut. For these, at first, we will use the COUNTIF function to calculate the number of similar cuts. Like how many good cuts or very good cuts are present in the dataset. Then we will the number and then calculate the average percentage of a specific cut.
⬇️⬇️ STEPS ⬇️⬇️
- We added three new columns to the dataset.
- The E column illustrates the type of cut.
- In the F column, we will calculate the number of repeated cuts and the G column is for calculating the average.
- Then, go to cell F6 and enter the following formula. The following formula will calculate the number of repeated cuts present in this dataset.
=COUNTIF($C$6:$C$15,E6)
- After that, press the Enter key and drag the Fill Handle icon from cell F6 to F10.
- Select cell G6 and enter the following formula. This formula will calculate the average percentage of a cut.
=F6/SUM($F$6:$F$10)
- Lastly, press the Enter key and drag the Fill Handle icon from cell G6 to G10.
📕 Read More: How to Calculate Average Percentage Change in Excel
2. Excel SUMIF Function to Get the Average
In the second method, we will compute the average text using a SUMIF formula. This dataset will be a little different from the previous one. Here, based on the type of cuts, we will calculate the average Carat. At first, we will calculate how many repeats are present in the dataset. Then we will calculate the average carat for that type of cut.
⬇️⬇️ STEPS ⬇️⬇️
- In the worksheet, we can see that we have three additional columns. In the E column, we have the type of cuts. In the F column, we will calculate the number of repeated cuts and in the G column, we will calculate the average Carat.
- Select cell F6 and enter the following formula.
=COUNTIF($C$6:$C$15,E6)
- Press the Enter key and drag Fill Handle from F6 to F10.
- Then, select cell G6 and insert the following formula.
=SUMIF($C$6:$C$15,$E6,$B$6:$B$15)/F6
- Click the Enter key and drag the Fill Handle icon from G6 to G10. And this will give us our desired average.
📕 Read More: 2 Easy Ways to Compute Average Excluding 0 in Excel
3. Calculating Average of Text in Excel Using Combination of Functions
In the third method, we will calculate the average of text in Excel using combined formulas. This dataset is totally different from the other two datasets. Here, we will calculate the average price of two different cuts based on Carat. For example, in a shop, we have 0.21 Carat diamonds of Premium and Fair Cut. In the top right table, we can see that we have per-carat prices for different types of cuts. Now we will calculate the price for the given carat for 2 different cuts and then we will calculate the average of them. Like as we mentioned earlier we have 0.21 Carat of diamonds for Premium and Fair Cut. Then we will calculate the total price for the Premium and Fair Cut together then we will divide the price by 2 and get the average price for 0.21 Carat.
⬇️⬇️ STEPS ⬇️⬇️
- First, we will calculate the total Price. So to do that first we need to assign the prices against the cuts present in the table. For that, we will use the combination of INDEX and MATCH functions.
- Select cell E6 and enter the following formula.
=SUM(INDEX($I$6:$I$10,N(IF(1,MATCH(C6:D6,$H$6:$H$10,0)))))*B6🔨 Formula Breakdown
SUM(INDEX($I$6:$I$10,N(IF(1,MATCH(C6:D6,$H$6:$H$10,0)))))*B6
👉 Here, C6:D6 is the lookup value. Lookup values are the values that we want to match in the lookup array.
👉 $H$6:$H$10 is the look-up array and 0 is the match type. Here the MATCH function will give us the positions of the matched values.
👉 IF(1,MATCH(C6:D6,$H$6:$H$10,0) here, IF function does a logical test. If the result is TRUE, the MATCH function is used.
👉 N replaces the values from MATCH into numbers.
👉 The number then passes to INDEX as a row number and with the SUM function, it calculates the sum.
- Then, select cell E6 and drag the Fill Handle icon from E6 to E15.
- As we calculated the total price for the two cuts now if we divide them by 2 we will get the average price of 21 Carat. Select cell F6 and enter the following formula.
=E6/2
- Lastly, press the Enter key. Select cell F6 and drag the Fill Handle icon from F6 to F15.
📕 Read More: 6 Ways to Solve When Average Formula is Not Working in Excel
📝 Takeaways from This Article
📌 Firstly, COUNTIF and SUM functions were used to calculate the average percentage of the Cut types of the diamond.
📌 COUNTIF and SUMIF function calculates the average Carat against different types of Cut.
📌 In the third method, we calculated the average Price for each Carat of diamond using the combination of INDEX and MATCH functions.
Conclusion
Finally, we have come to the end of this article. In this article, we tried to provide every possible way to calculate the average of text in Excel. As many methods have been demonstrated here, you may quickly choose which one is suitable for you. If you have any questions, please leave them in the comments. Finally, we propose that you visit ExcelDen to discover more about Excel.
Related Articles
- 7 Easy Ways to Calculate Average Rating in Excel
- 3 Approaches to Calculate 5 Star Rating Average in Excel
- 7 Ways to Calculate Average Attendance with Formula in Excel
- 6 Quick Ways to Calculate Average Percentage of Marks in Excel
- 5 Ways to Calculate 7-Day Moving Average in Excel
