Excel and rounding problem

  Neill 19:08 09 May 03

Question from a work colleague.
Calculation with Excel has a different answer to calculation done on a calculator.
A1 to L1 has the 12 months. A3 to L3 has various percentage figures that add up to 100%. For example, A3 has 8.5%, B3 has 10.5%, C3 has 11.5%; the rest across to L3 are all around the 10% mark but all twelve add up to 100%. In cell N5 (the target) is a figure of 3254. In A5 is =$N$5*A3, in B5 is =$N$5*B3, in C5 is =$N$5*C3 and so on across to L5.
In A5, the result is 276.59, 227.78 in B5 and so on. The addition of the A5:L5 is 3254.
These cells (A5:L5) are referred to in A7:L7. A7 has =A5, B7 has =B5 etc across to L7 and are formatted to Number with 2 decimal places. The total of this row is 3254 because, I believe, Excel calculates a number based on its stored value not the value you see.

In row 9, A9 to L9 also references A5 to L5 so A9 has =A5, B9 has =B5
Now if cells in A9:L9 are formatted to Number with no decimal place (A9 now has 277 instead of 276.9, B9 has 228 instead of 227.78). Now if that row is totalled =Sum(A9:L9) the answer is 3254.
Now if we just type the numbers from row 9 into row 10; i.e A10 has 227 and B10 has 228 etc and add them up with a calculator we get 3256, a difference of 2.

Now he wants the figures in row 9 to add up to the target figure (N5) with no variance when checked with a calculator.

Any help would be gratefully received. I could email you the simple spreadsheet if that helps. Neill

  VoG™ 19:17 09 May 03

Thanks for all that detail - makes a pleasant change!

I still think that I need to see the spreadsheet. I'll send you a message......

  VoG™ 09:04 10 May 03

Having looked at the spreadsheet I can see what is going on. As you say, Excel uses its stored value, not that displayed.

To get agreement between the Excel displayed value and a calculator value, you have to use Excel's ROUND function instead of changing the display settings.

Thus, as in the example that I've mailed back to you, use


etc along to =ROUND(L8,0)

Then =SUM(A26:L26) gives 3256 (which is correct).

There are issues of accuracy with Excel but only with numbers with many digits (>17 from memory) but for most applications, Excel gets it right, even if the result is not quite what you expect!

This thread is now locked and can not be replied to.

Samsung Galaxy S8 review

1995-2015: How technology has changed the world in 20 years

This 2D animation about monsters is illustrated in a Van Gogh painterly style

How to get WWDC 2017 tickets