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.

**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)`

- 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**

**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)`

- 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**

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

- 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**