Formula Errors

Fix the Excel #DIV/0! Error When Averaging Scaled Scores in WPS Spreadsheets

Adam DavisAdam Davis Nov 13, 2026 869 views

Question details

The user needs to average scaled scores in a row but gets #DIV/0! because the scaled-score formula returns numbers as text, and AVERAGE ignores text and blanks.

Fix the Excel #DIV/0! Error When Averaging Scaled Scores in WPS Spreadsheets
Product
WPS Office Spreadsheets
Device & OS
not provided
Scenario
The user has scaled-score formulas across C9:L9 and wants a numeric average in a spreadsheet.
Observed behavior
WPS Spreadsheets displays #DIV/0! in the AVERAGE formula because the scaled-score results are text or blank and no numeric values are supplied.
Before you start

Open the worksheet and select a scaled-score result cell in the target range. Check whether the formula result is text by looking for quotation marks around the returned number in the formula bar or by using ISNUMBER.

Solution 1Recommended

Return Numeric Scaled Scores and Average Them

Use this when the scaled-score formula wraps the score in quotes, which makes AVERAGE ignore every result.

AVERAGE only includes numeric values. If the scaled-score formula returns text such as "85", the function treats all results as non-numeric and may produce #DIV/0!.

1
Edit the scaled-score formula

Select the first scaled-score cell in the row, click the formula bar, and remove the double quotation marks around the numeric result so the formula returns 85 instead of "85".

2
Fill the formula across the row

Press Enter and use the fill handle to copy the corrected formula across the remaining scaled-score cells in C9:L9.

3
Average the numeric results

Select the cell for the average, type =AVERAGE(C9:L9), and press Enter to display the numeric average.

Return Numeric Scaled Scores and Average Them
Error cleared: After the scaled-score results become real numbers, AVERAGE calculates normally and #DIV/0! no longer appears.

Fix #DIV/0! Averages in WPS Spreadsheets

Open your scaled-score workbook in WPS Office Spreadsheets, correct the formula to return numeric values, and use AVERAGE or IFERROR to get a clean result. WPS Spreadsheets is fully compatible with Excel files and supports the same workbook functions.

  1. 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, click Menu > Open, and select the Excel workbook containing the scaled-score formulas.
  2. 2. Correct the scaled-score formula: Select the first scaled-score cell, click the formula bar, remove quotes around the returned number, and press Enter.
  3. 3. Fill the formula across the range: Drag the fill handle across C9:L9 so all scaled-score results become numeric.
  4. 4. Enter the average formula: In the result cell type =AVERAGE(C9:L9) or =IFERROR(AVERAGE(C9:L9),"No numeric scores available") and press Enter.
  5. 5. Check the result: Confirm the cell displays the average or your chosen fallback instead of #DIV/0!.
Formula-compatible AVERAGE, IFERROR, and fill-handle behaviorOpens and saves .xlsx and .xls files without conversionFree spreadsheet app with a familiar Excel-like interfaceLightweight performance for quick formula troubleshooting
microsoft office alternative - wps office

Frequently Asked Questions

Why does AVERAGE show #DIV/0! in my scaled-score row?

AVERAGE ignores text values and empty cells. If your scaled-score formula returns numbers in quotation marks, it supplies no numeric values to average, so WPS Spreadsheets displays #DIV/0!.

How do I change my scaled-score formula so AVERAGE can use the results?

Edit the formula and remove the double quotation marks around the returned number. For example, return 85 instead of "85", then fill the formula across the row.

Can I still show a message when a row has no scores to average?

Yes. Use =IFERROR(AVERAGE(C9:L9),"No numeric scores available") in the average cell to replace the error with your chosen fallback.

Does WPS Spreadsheets handle Excel formulas the same way?

Yes. WPS Spreadsheets supports AVERAGE, IFERROR, and other Excel-compatible functions, so you can open and fix .xlsx workbooks directly.