Sum and Average Formulas Don't Work

Question

There doesn't appear to be any consistency with how these errors are being made. Sometimes the bottom bar will display correct sums and averages. Other times they'll display incorrect figures or they wont display at all. This also occurs when I try to input formulas. I'm getting a lot of sum = 0 and average = DIV/0 errors.

All of my cells are formatted as numbers and I'm getting no circular reference errors. I have changed calculating options to manual. I've tried exiting excel and reopening.

(average and sum are incorrect)

(Average and sum not calculated)

(Correct average and sum)

Answer 1

Could you share us a test file to reproduce your issue?

For sharing a sample workbook, upload to OneDrive or any other cloud drives. Then post a link here.

Check that the cells you are referencing in your formulas are actually numbers. Sometimes cells can be formatted as numbers but still contain text or other characters that can cause errors in calculations.

You may use isnumber formula to check if the value in cell is a really number.

.

You may try this formula to convert text to number.

=NUMBERVALUE(CLEAN(A2))

Answer 2

Admittedly deceptive, but if the digits were input as Text applying any type of Number formatting does not convert the text string to a value. However, the type of Number formatting will be indicated when the cell is selected.

When summing a range of cells which contain numerical text strings the result is zero.

The Average function will return a #Div/0 because the denominator [number of cells containing values] is none (0).

The easy way to correct the problem is to select & copy the affected cells, then paste while the cells still are selected. Click the warning triangle ⚠️ & select Convert to Number. This only works if all selected cells need to be converted. IOW, none which contain an actual value can be included in the selection.

Another option is to enter the digit 1 into an unused cell the copy that cell. Select affected cells then use Paste Special - Multiply. With this method the selected range can include cells which contain a value as well as cells which contain a text string.