How do I turn OFF rounding when Excel is adding a column? Example 3+7+6+7=23. Excel shows the total as 22. How do I fix this? Thanks!

Are you sure that you are really summing 3, 7, 6, 7? These numbers SUM to 21.7

2.8 6.6 5.7 6.6 =SUM(A1:A4) shows 21.7However, if I format everything as integers, it will look like this:

3 7 6 7 =SUM(A1:A4) will display 22.Since the numbers that are stored in the cell don't really add up to 23, Excel won't display 23 with a simple SUM function.

What is it that you are trying to do? Perhaps with a little more detail, we can provide a better answer.

Hi, I suspect your problem lies in the difference between what Excel saves as cell values and what it displays.

The cell formatting sets what is seen in a cell, so put the following values in cells A1 to E2:

A B C D E 1 0.5 0.5 0.5 0.5 0.5 2 1.4 1.4 1.4 1.4 1.4

Format the cells with no decimal places and the cells look like this:A B C D E 1 1 1 1 1 1 2 1 1 1 1 1

which is a total of ten.

Enter these formulas:

F1 =SUM(A1:E1)

F2 =SUM(A2:E2)

F3 =SUM(A1:E2)

and format these cells as no decimal places and you get:A B C D E F 1 1 1 1 1 1 3 2 1 1 1 1 1 7 3 10

It looks as though both rows should total 5, but the totals are 3 and 7, which appear to be wrong, but are actually much closer to the actual totals (2.5 & 7.0) than 5

The overall total in F3 looks correct, showing 10, but the actual total for both rows is 9.5.Try formatting your input cells (3+7+6+7) with different numbers of decimal points, to see what is really in the cells.

As an answer to your question -

How do I turn OFF rounding, the answer is you can't turn off rounding in Excel.You can however manage rounding, either by showing the appropriate number of decimal places or actually changing the stored numbers.

Carrying on my example

in cell A4 put =ROUND(A1,0) and

in cell A5 put =ROUND(A2,0)Drag to extend these two formulas across to column E

Add the SUM() formulas in column F

=SUM(A4:E4)

=SUM(A5:E5)

and you get thisA B C D E F 4 1 1 1 1 1 5 5 1 1 1 1 1 5

The row totals match the values displayed in the cells.

The cells A4 to E5 now hold the rounded values, which in this case are all 1. Change the formatting and the cells will show 1.0 or 1.00000Hope this helps explain the way Excel stores and displays numbers.

Regards

Thank you. You are correct. It is a question of significant digits. When I displayed the numbers to 2 decimal places, I found they were not stored as integers, but rounded numbers. Hence the discrepancy. Displaying the result to 2 decimal places revealed the source of the problem. Thank you!

Ask Your Question

Weekly Poll

How long do you think until flying cars or taxis are common?

Discuss in The Lounge

Poll History