Fix Excel SUM and AVERAGE Formulas That Are Not Working
Question details
The user needs Excel formulas, including SUM and AVERAGE, to calculate correctly.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Calculating totals or averages from spreadsheet values that may have been imported or formatted using different regional conventions.
- Observed behavior
- Formulas may return unexpected results when numbers are stored as text, decimal separators do not match, or a circular reference exists.
Select the cells used by the formula and check for warning icons or numbers aligned to the left, which can indicate text. Compare the decimal and thousands separators in the source values with those expected by Excel.
Convert Numbers Stored as Text to Numeric Values
Use this method when SUM or AVERAGE ignores values that look like numbers.
Select the range referenced by your SUM or AVERAGE formula and look for a warning icon beside the selection.
If the warning icon appears, click it and choose Convert to Number. If it does not appear, select the range and choose Data > Text to Columns > Finish, provided the values use Excel's expected number separators.
Return to the SUM or AVERAGE formula and confirm that it includes the converted cells. For an individual text value, you can also enter =VALUE(A1) in an empty cell, replacing A1 with the relevant cell.

Match Excel's Number Separators to the Source Data
Use this method when imported numbers follow different regional conventions, such as commas for decimals.
Remove Circular References and Check Calculation Settings
Use this method when a formula refers to itself or the workbook is not recalculating automatically.
Work with Spreadsheet Formulas in WPS Office
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface. Open compatible Excel files in WPS Spreadsheets to work with formulas and move between office formats, though it does not repair Microsoft Excel itself.

Frequently Asked Questions
Why does SUM ignore some cells that look like numbers?
The values may be stored as text. Select the cells and use the warning icon's Convert to Number option, or try Data > Text to Columns.
Why does AVERAGE return a #DIV/0! error?
AVERAGE can return #DIV/0! when its referenced range contains no numeric values. Check the range and convert any numbers stored as text.
How do I find a circular reference in Excel?
Go to Formulas > Error Checking > Circular References and select a listed cell. Edit the formula to remove the unintended reference to itself.
How can I convert imported numbers with different decimal separators?
Select the values and open Data > Text to Columns. On the final wizard screen, click Advanced and enter the decimal and thousands separators used in the source data before clicking Finish.




