3 Approaches to Calculate Average of Text in Excel

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.

Overview of the methods


📁 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.

Dataset for calculating average of text in Excel

APPROACH

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)

Opening the worksheet and insert the formula to count repeated values

  • After that, press the Enter key and drag the Fill Handle icon from cell F6 to F10.

Count of repeated values

  • Select cell G6 and enter the following formula. This formula will calculate the average percentage of a cut.
=F6/SUM($F$6:$F$10)

Entering formula to calculate average

  • Lastly, press the Enter key and drag the Fill Handle icon from cell G6 to G10.

Obtained average for cut types

📕 Read More: How to Calculate Average Percentage Change in Excel

APPROACH

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)

Opening the formulas worksheet and insert the formula to calculate repeated times

  • Press the Enter key and drag Fill Handle from F6 to F10.

Obtained points assigned against cut types

  • Then, select cell G6 and insert the following formula.
=SUMIF($C$6:$C$15,$E6,$B$6:$B$15)/F6

Entering formula to calculate average

  • Click the Enter key and drag the Fill Handle icon from G6 to G10. And this will give us our desired average.

resulted average

📕 Read More: 2 Easy Ways to Compute Average Excluding 0 in Excel

APPROACH

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.

Inserting formula to calculate the total price

  • Then, select cell E6 and drag the Fill Handle icon from E6 to E15.

Obtained total price for both cuts

  • 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

Entering formula to calculate the average price of two cuts

  • Lastly, press the Enter key. Select cell F6 and drag the Fill Handle icon from F6 to F15.

Obtained average price for each carat of diamond

📕 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

(Visited 43 times, 1 visits today)
Sakibul Hasan Nahid

Sakibul Hasan Nahid

Hello I am Sakibul Hasan Nahid. I have completed my BSc. in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology. I take a great interest in learning about new technologies and sharing my ideas and thoughts on them with others. Please do reach out to me if you have any queries or recommendations.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo