Formula Errors

Fix Excel SUM and AVERAGE Formulas That Are Not Working

Natalie TaylorNatalie Taylor Nov 18, 2026 871 views

Question details

The user needs Excel formulas, including SUM and AVERAGE, to calculate correctly.

Fix Excel SUM and AVERAGE Formulas That Are Not Working
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.
Before you start

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.

Solution 1Recommended

Convert Numbers Stored as Text to Numeric Values

Use this method when SUM or AVERAGE ignores values that look like numbers.

1
Select the affected cells

Select the range referenced by your SUM or AVERAGE formula and look for a warning icon beside the selection.

2
Convert the values

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.

3
Check the result

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.

Convert Numbers Stored as Text to Numeric Values
Separator mismatch: If conversion produces an unexpected value or an error, check the decimal and thousands separators in the source data before trying again.
Free Microsoft Office alternative

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.

Create and review SUM and AVERAGE formulas in WPS SpreadsheetsOpen and save Microsoft Excel XLSX and XLS filesUse a familiar interface for a smoother transition from Microsoft OfficeWork with a free, lightweight office suite
microsoft office alternative - wps office

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.