How to Calculate Average of Text in Excel (2 Ways)

Get FREE Advanced Excel Exercises with Solutions!

How to calculate average of text in Excel is not a very commonly encountered scenario. However, we have to deal with various requirements our dataset demands. In this article, we demonstrate how to calculate the average of text in Excel using custom formulas.

Since we calculate the average of text depending on particular scenarios, we are not displaying any dataset as a sample dataset. You can use the below-mentioned formulas using your own dataset. Or you can download the dataset attached to this article.


Download Excel Workbook


Typical Average Functions in Excel Dealings with Texts

Excel offers AVERAGE, AVERAGEA, AVERAGEIF, and AVERAGEIFS to simply calculate the average values of a range or array. Every time you see or hear the term “Average”, these functions come to your mind. The syntaxes of these functions are

AVERAGE (number1, [number2], ...)

AVERAGEA (value1, [value2], ...)

AVERAGEIF (range, criteria, [average_range])

AVERAGEIFS (avg_rng, range1, criteria1, [range2], [criteria2], ...)

Among these, the AVERAGE function ignores empty cells, cells that contain text or logical values.

AVERAGE Function- Calculate Average of Text in ExcelThe outcomes of the AVERAGE function result in a #DIV/0! Error as it encounters no numeric values in the cells.

On the contrary, the AVERAGEA function includes the logical values TRUE and FALSE. Also allows texts in the entries but doesn’t account for texts in its calculations.

AVERAGEA Function- Calculate Average of Text in ExcelThe AVERAGEA function takes texts but doesn’t include text values in the calculation. As a result, it displays 0 unable to find any numeric value to sum then average.

For the AVERAGEIF function, the range is anything with numbers or names, arrays, or references. And the criteria are anything with a number, expression, cell reference, or text. However, it averages the numeric values in the range that satisfy the imposed criteria. Similar arguments are applicable for the AVERAGEIFS function.

Read More: How to Calculate Average of Multiple Ranges in Excel (3 Methods)


2 Easy Ways to Calculate Average of Text in Excel

Method 1: Using SUM and COUNTIF Functions to Calculate Average of Text in Excel

Suppose in a scenario, we have to calculate the average number of Product Names in the following dataset. That means what is the percentage of a particular product sold against the whole bunch of products.

To calculate the average, we have to first count the times of a particular product appearing in the entries. Then calculate its average using the simple average formula (i.e., count/total count).

Sum Countif function-Calculate Average of Text in ExcelStep 1: As you want to count the times of appearance, use the following formula in any adjacent blank cell (i.e., E5).

=COUNTIF($C$5:$C$16,C5)

The COUNTIF function has a syntax of COUNTIF (range, criteria). The formula counts the criteria (i.e., E5) among all the Product Names (i.e., C5:C16) and returns the occurred times. In the formula, we use absolute references to lock the range for each cell.

Formula insertionStep 2: Press ENTER and Drag the Fill Handle to display all the count numbers in the cells.

Fill handleAfter applying the Fill Handle count numbers will appear in all cells. You can use the formula for counting any text appearances in a dataset.

Step 3: Now, since we have an individual occurrence number of each product, we can calculate the average of texts. Type the below formula in any adjacent cells

=E5/SUM($E$5:$E$16)

The formula divides the count numbers by the total count of products that appeared in the dataset.

Formula insertionStep 4: Hit ENTER then Drag the Fill Handle. As a result, the average values of Product Names appear in all cells as shown in the picture below.

average calculation-Calculate Average of Text in Excel

Read More: How to Get Average Time in Excel (3 Examples)


Similar Readings


Method 2: Using a Combined Formula While Assigning Value to the Text

Now, in this case, we have 10 Products along with their reviews. Let’s say we pick those product reviews and want to calculate the average of text by assigning review texts a value. The below screenshot may convey the premise we want to articulate.

Dataset-Calculate Average of Text in ExcelStep 1: Write the following formula in any blank cell (i.e., H5).

=SUM(INDEX($L$5:$L$9,N(IF(1,MATCH(C5:G5,$J$5:$J$9,0)))))

The formula consists of multiple functions such as SUM, MATCH, IF, N, and INDEX.

Firstly, the IF function performs a logical_test (i.e., 1) and applies the MATCH function in case of TRUE outcomes. The MATCH function assigns values depending on the lookup_value matchings. For instance, it matches Excellent to point 5 and Bad to point 1.

Secondly, the MATCH function takes the reviews (C5:G5) as lookup_value and matches to an array ($J$5:$J$9) for an absolute match.

The N function converts a value into a number. In this formula, the resultant number passes to the INDEX function as the row_num. Then the SUM function combines all the array points in cell H5.

Formula insertionStep 2: The custom formula used in Step 1 is an array formula. You have to press CTRL+SHIFT+ENTER altogether to display the sum. Afterward, Drag the Fill Handle to appear all the sum in other cells depicted in the following image.

Applying fill handleStep 3: After getting the total sum points against reviews, apply the typical average formula in the adjacent cell (i.e., I5).

=H5/SUM($H$5:$H$14)

This formula calculates the average of each product by dividing it by total points.

Average formulaStep 4: Use ENTER to apply the formula in cells then Drag the Fill Handle to make all the averages visible similar to the image below.

Applying fill handleYou can use any text then assign values or points in order to calculate their averages. Assigning values to texts gives a relative understanding of how a product is performing compared to others.

Read More: Moving Average Formula in Excel (8 Uses with Examples)


Conclusion

In this article, we calculate the average of text in Excel. We use the SUM and COUNTIF functions or array formulas to count the occurrences of particular texts. After we get the individual count of each product, we can use a typical average operation (i.e., count/total count) to calculate the average of text. Hope our datasets, formulas carry lucid insides of calculating the average of texts and able you to apply those in your instances. Comment, if you have further inquiries or have something to add.


Related Articles

Maruf Islam

Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

2 Comments
  1. Hello! correct me if i’m wrong but doing the first method, even though formula wise it’s correct it will give wrong value has you are suming the range and adding the same value whenever the same name appears.

    So counting the name Brian, you have it in the array only 3 times but if you sum the nnumber of time that name is showed it’s counting 9, so the percentage will be incorrect.

    • Hi TIAGO,
      Thanks for your comment. I am replying to you on behalf of ExcelDemy. You can get the correct value by using the COUNTA function instead of the SUM function. Let’s see the steps.
      Step-01: Write the following formula in the selected cell.
      =E5/COUNTA($C$5:$C$16)
      Text average formula
      Step-02: Press Enter to get the Average.
      Text Average result
      Step-03: Drag the Fill Handle down to copy the formula to the other cells.
      Using Fill Handle
      I hope this will help you to solve your problem. Please let me know if you have other queries.
      Regards
      Mashhura,
      ExcelDemy.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo