Formula Errors

Fix Excel AVERAGE #DIV/0! Errors When Numbers Are Stored as Text

Tauseeq MagsiTauseeq Magsi Nov 18, 2026 870 views

Question details

Identify and convert blood pressure readings stored as text so Excel averaging formulas can use them.

Fix Excel AVERAGE #DIV/0! Errors Caused by Numbers Stored as Text
Product
Microsoft Excel
Device & OS
not provided
Scenario
A user is calculating averages of blood pressure readings with AVERAGE, AVERAGEIF, or AVERAGEIFS.
Observed behavior
The formulas return #DIV/0!, and the readings may be stored as text rather than numbers.
Before you start

Check that the averaging range contains individual numeric readings, not combined values such as 120/80. If you use AVERAGEIF or AVERAGEIFS, also check that the criteria match the intended rows.

Solution 1Recommended

Check and convert text readings with Text to Columns

Use this method when readings look like numbers but Excel recognizes them as text.

1
Check a reading

In an empty cell, enter =ISTEXT(A2), replacing A2 with a reading cell. TRUE indicates that the value is text.

2
Select the readings

Select the cells containing the individual readings, excluding the column heading.

3
Convert the values

Select Data > Text to Columns. Choose Delimited, select Next twice, choose General as the column data format, and select Finish.

4
Recheck the average

Review the converted cells and recalculate the AVERAGE, AVERAGEIF, or AVERAGEIFS formula. If it still returns #DIV/0!, check the range and any criteria.

Check and convert text readings with Text to Columns
Number formatting is not conversion: Changing the cell format to Number does not convert existing text values into numbers.
Average readings in WPS Spreadsheets

Convert and average text readings in WPS Spreadsheets

Use a helper column in WPS Spreadsheets to convert numeric text, then average the converted readings.

  1. 1. Open the workbook: Open WPS Spreadsheets and use Open to select your Excel workbook.
  2. 2. Convert a reading: In an empty helper column, enter =VALUE(TRIM(A2)), replacing A2 with the first reading cell.
  3. 3. Fill the formula down: Drag the formula cell's fill handle down alongside the remaining readings.
  4. 4. Calculate the average: In an empty cell, enter =AVERAGE(B2:B10), adjusting the range to the converted readings in your helper column.
Convert numeric text with VALUE before calculating an averageUse familiar spreadsheet formulas and menusOpen and edit Microsoft Excel XLS and XLSX files
microsoft office alternative - wps office

Frequently Asked Questions

Why does AVERAGE return #DIV/0! when cells look like numbers?

AVERAGE ignores text values in a referenced range. If it finds no numeric values to average, it returns #DIV/0!. Use =ISTEXT(A2) to check a reading.

Why did changing the cells to Number format not fix the error?

The Number format changes how numeric values are displayed; it does not convert existing text. Select the readings and use Data > Text to Columns or Paste Special > Multiply to convert numeric text.

What if AVERAGEIF still returns #DIV/0! after conversion?

Check that the criteria match at least one row and that the corresponding average range contains numeric values. AVERAGEIFS also returns #DIV/0! when no rows meet all criteria.

Can Excel average readings written as 120/80?

A combined reading such as 120/80 is not a single numeric blood pressure value suitable for averaging. Separate systolic and diastolic readings into numeric columns, then average each column.

How can I remove extra spaces from imported readings?

In a helper cell, try =VALUE(TRIM(A2)) and fill the formula down. If imported values contain nonbreaking spaces, try =VALUE(TRIM(SUBSTITUTE(A2,CHAR(160)," "))) instead.