Fix Excel AVERAGE #DIV/0! Errors When Numbers Are Stored as Text
Question details
Identify and convert blood pressure readings stored as text so Excel averaging formulas can use them.

- 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.
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.
Check and convert text readings with Text to Columns
Use this method when readings look like numbers but Excel recognizes them as text.
In an empty cell, enter =ISTEXT(A2), replacing A2 with a reading cell. TRUE indicates that the value is text.
Select the cells containing the individual readings, excluding the column heading.
Select Data > Text to Columns. Choose Delimited, select Next twice, choose General as the column data format, and select Finish.
Review the converted cells and recalculate the AVERAGE, AVERAGEIF, or AVERAGEIFS formula. If it still returns #DIV/0!, check the range and any criteria.

Convert text readings with Paste Special Multiply
Use multiplication by 1 to convert numeric text in place.
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. Open the workbook: Open WPS Spreadsheets and use Open to select your Excel workbook.
- 2. Convert a reading: In an empty helper column, enter =VALUE(TRIM(A2)), replacing A2 with the first reading cell.
- 3. Fill the formula down: Drag the formula cell's fill handle down alongside the remaining readings.
- 4. Calculate the average: In an empty cell, enter =AVERAGE(B2:B10), adjusting the range to the converted readings in your helper column.

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.




