Calculation Issues

Fix Excel MAX and AVERAGE Results for Numbers Stored as Text

Jason MillerJason Miller Nov 18, 2026 869 views

Question details

The user needs to get accurate MAX and AVERAGE results from values in Excel cells AK3:AK26.

Fix Excel MAX and AVERAGE Results for Numbers Stored as Text
Product
Microsoft Excel 2019
Device & OS
not provided
Scenario
The user is calculating the maximum and average of displayed values that may be text or formula results.
Observed behavior
MAX and AVERAGE return zero or unexpected results even though the values appear numeric.
Before you start

Select a few cells in AK3:AK26 and check the formula bar to see whether they contain formulas or entered values. Note whether formulas use TEXT, which returns text even when its result looks like a number.

Solution 1Recommended

Check for text values and revise source formulas

Use COUNT and ISNUMBER to identify nonnumeric results, then change formulas so they return numbers.

1
Count numeric cells

In an empty cell, enter =COUNT(AK3:AK26). Compare the result with the number of cells you expect to contain numbers.

2
Test an individual value

In another empty cell, enter =ISNUMBER(AK3). A FALSE result means AK3 is not a numeric value. Repeat the test for other suspect cells.

3
Inspect and revise formulas

Select a suspect cell and inspect the formula bar. If its formula uses TEXT to display a sum, remove the TEXT wrapper so the formula returns the numeric sum instead.

4
Apply number formatting

Select AK3:AK26 and use Home > Number to apply the desired number format. Changing the format alone does not convert existing text to numbers.

5
Recalculate the results

Check =MAX(AK3:AK26) and =AVERAGE(AK3:AK26) again. If formulas do not update, select Formulas > Calculation Options > Automatic.

Check for text values and revise source formulas
How text affects results: MAX and AVERAGE ignore text in referenced cells. If the range contains no numeric values, MAX returns 0 and AVERAGE returns a #DIV/0! error.
Calculate with WPS Spreadsheets

Check and Convert Text Numbers in WPS Spreadsheets

Open the workbook in WPS Spreadsheets and use COUNT and VALUE to identify and convert numeric text before calculating MAX and AVERAGE.

  1. 1. Open the workbook: Open WPS Spreadsheets and use Open to select your Excel workbook.
  2. 2. Count numeric values: In an empty cell, enter =COUNT(AK3:AK26) to see how many cells contain numbers.
  3. 3. Convert in a helper column: In AL3, enter =IFERROR(VALUE(AK3),"") and fill the formula down through AL26. Check blank results for values that could not be converted.
  4. 4. Calculate the results: In empty cells, enter =MAX(AL3:AL26) and =AVERAGE(AL3:AL26).
Check whether cells contain actual numbers with COUNTConvert recognizable numeric text with VALUEOpen and edit Microsoft Excel XLSX and XLS filesUse familiar spreadsheet formulas and menus
microsoft office alternative - wps office

Frequently Asked Questions

Why does MAX return zero when cells look like numbers?

The displayed values may be text, which MAX ignores when it references a range. Enter =COUNT(AK3:AK26) to check how many cells contain actual numbers.

Why does AVERAGE show #DIV/0! instead of zero?

AVERAGE returns #DIV/0! when its referenced range has no numeric values to average. Use COUNT to check the range, then convert numeric text or revise formulas that return text.

Does changing a cell's format to Number convert text to a number?

No. Home > Number changes how numeric values are displayed but does not convert existing text. Use Text to Columns for entered text values, or revise formulas that return text.

Can a formula return text even if it calculates a sum?

Yes. For example, TEXT(SUM(...), ...) returns a text representation of the sum. Remove the TEXT wrapper and apply a number format if the result needs to remain numeric.

What is Text to Columns used for in this fix?

Data > Text to Columns can convert recognizable numeric text in selected cells into numbers. Use it on entered values, not formula cells whose formulas you need to preserve.