Excel Formulas for Averaging Customer Ratings with Text Values

Consolidating customer feedback is often frustrated by mixed data types, especially when numerical ratings are co-mingled with text placeholders like "N/A" or "Pending." While organizations typically rely on standard funding sources, such as departmental budgets or operational grants, to finance these survey initiatives, measuring their true ROI requires flawless data integrity.

Fortunately, Excel grants analysts the power to seamlessly bypass text anomalies, under the stipulation that formulas must explicitly filter out non-numeric strings to prevent calculation errors. By leveraging robust functions like AVERAGEIF (for example, =AVERAGEIF(B2:B100, ">=1")), you can preserve mathematical accuracy. Below, we outline the exact formula configurations to clean and average your dataset.

Customer feedback is the lifeblood of any service-oriented business. Whether you are tracking Net Promoter Scores (NPS), Customer Satisfaction (CSAT) scores, or product star ratings, aggregating this data is crucial for performance analysis. However, real-world data is rarely pristine. Often, exports from platforms like Zendesk, Salesforce, or Typeform contain a mix of numbers and text.

You might find columns containing numeric ratings alongside text entries like "N/A", "No Comment", "Excellent", "Poor", or even numbers formatted as text strings (e.g., "5 stars"). If you try to apply a standard =AVERAGE() formula to these cells, Excel will either ignore the values you need or throw a frustrating #DIV/0! or #VALUE! error.

This comprehensive guide will walk you through various Excel formulas and techniques to average customer ratings that contain text values, from simple cleanups to advanced dynamic array solutions.

Understanding the Problem: Why Excel's AVERAGE Fails

Before diving into the formulas, it is important to understand how Excel behaves. The native AVERAGE function is programmed to automatically ignore logical values and text cells if they are directly referenced in a range. For example, if you have the values 5, 4, "N/A", and 3 in cells A1 through A4, the formula:

=AVERAGE(A1:A4)

will successfully return 4 (calculating (5 + 4 + 3) / 3). It completely ignores the text string "N/A".

However, problems arise in two common scenarios:

• Numbers stored as text: If your rating system exported "5", "4", and "3" as text strings rather than numbers, the AVERAGE function will ignore them entirely, resulting in a #DIV/0! error because it finds zero numerical values to calculate.
• Qualitative text scales: If your dataset uses qualitative text like "Excellent", "Good", "Fair", and "Poor", Excel has no native way of knowing that "Excellent" equals 5 and "Poor" equals 1.

Scenario 1: Averaging Numbers Stored as Text

When databases export CSV files, numbers are frequently saved as text. You can identify these by the small green error triangle in the top-left corner of the cell, or by the left-alignment of the data.

The Modern Solution: Dynamic Arrays (Excel 365 & 2021+)

If you are using a modern version of Excel, you can use a single-cell array formula to convert the text numbers on the fly and average them. We do this by using the double unary operator (--) or the VALUE function to force Excel to convert the text to numbers.

=AVERAGE(--A2:A11)

How it works:

• A2:A11 represents your range of ratings stored as text.
• The double negative (--) acts as a mathematical operator that coerces text-based numbers into actual numbers without changing their mathematical value.
• AVERAGE then processes the converted numeric array seamlessly.

Handling Errors in the Range

If your text range contains non-numeric text like "Pending" or "No Response", the double unary operator will return a #VALUE! error. To bypass this, wrap your conversion inside an IFERROR function:

=AVERAGE(IFERROR(--A2:A11, ""))

Note: If you are using Excel 2019 or older, you must press Ctrl + Shift + Enter instead of just Enter to commit this as an array formula.

Scenario 2: Mapping and Averaging Qualitative Text Ratings

Often, customer surveys ask users to rate their experience using descriptive words like Excellent, Very Good, Good, Fair, and Poor. To find the average score, we must assign a numerical weight to each word.

Method A: The Helper Column Approach (Recommended for Beginners)

The cleanest way to handle this without complex formulas is by creating a mapping table and using a helper column.

1. Create a mapping table (like the one above) in cells D2:E6.
2. In your main table, next to your text ratings (Column B), insert a helper column (Column C).
3. In cell C2, enter a VLOOKUP or XLOOKUP formula to find the numeric rating:

=XLOOKUP(B2, $D$2:$D$6, $E$2:$E$6, "")

Drag this formula down. Now, you can easily run a standard average on Column C:

=AVERAGE(C2:C100)

Method B: The Advanced Array Formula (No Helper Column)

If you cannot alter the structure of your sheet and must calculate the average directly from the raw text list in a single cell, you can use a combined AVERAGE, MATCH, or SWITCH array formula.

If your ratings strictly follow a standardized list, you can map them inside a MATCH function:

=AVERAGE(MATCH(B2:B11, {"Poor","Fair","Good","Very Good","Excellent"}, 0))

How it works: The MATCH function searches for each feedback entry in the inline array constant {"Poor","Fair","Good","Very Good","Excellent"}. Because "Poor" is first, it returns 1. Because "Excellent" is fifth, it returns 5. The AVERAGE function then averages these returned positions.

Scenario 3: Extracting Numbers from Mixed Text (e.g., "5 Stars")

Sometimes customer data contains mixed strings like "5 Stars", "4 Stars", or "1 - Strongly Disagree". To average these, we need to extract the numeric portion before averaging.

Assuming your ratings are in column A and always start with a number, you can use the LEFT function to extract the first character, convert it to a number, and average the result:

=AVERAGE(VALUE(LEFT(A2:A11, 1)))

If some cells are empty or contain pure text without numbers (e.g., "No Answer"), safeguard the formula using IFERROR and ISNUMBER logic:

=AVERAGE(IFERROR(VALUE(LEFT(A2:A11, 1)), ""))

Scenario 4: Averaging While Ignoring Specific Text Values (e.g., "N/A" or "Refused")

If your data is already numeric but contains a few cells with explicit text like "N/A" or "Out of Scope", Excel's basic AVERAGE function will ignore them automatically. However, if your formula evaluates cells that contain formula-generated text errors (like #N/A or #VALUE!), the average formula will break.

To safely calculate the average while ignoring both text responses and cell errors, utilize the AGGREGATE function. AGGREGATE is highly versatile because it can ignore hidden rows, error values, and nested functions.

=AGGREGATE(1, 6, A2:A11)

Parameters explained:

• 1: The function number for AVERAGE.
• 6: The option to ignore error values.
• A2:A11: The range of your customer ratings.

Summary: Which Formula Should You Use?

Choosing the right formula depends heavily on your Excel version and data structure. Here is a quick reference table:

By implementing these robust formulas, you can bypass manual cleanup steps, save hours of data scrubbing, and build dynamic dashboards that update automatically when new customer reviews are imported.