Results 1 to 8 of 8

20030707, 11:13 #1
 Join Date
 Apr 2001
 Location
 Arriving Somewhere but not Here
 Posts
 698
 Thanks
 0
 Thanked 0 Times in 0 Posts
rounding limitation? (XL97 sr2 on Win2K/NT4)
I know Excel can be a little limited at times but...
Enter =(2.032)/0.2 into a cell and you get 0.15, in the cell underneath enter 0.15.
OK so now you have two cells that hold identical values right? Apparently not!
In the cells next to your two numbers ask Excel to round your values to 1dp using =Round(cell ref),1). You will get answers of 0.1 and 0.2.
If you reformat the cells to 15 dps then you see why, Excel's calculation engine comes up with 1.49999999999999 for the answer to the simple sum.
Deep sigh, is there a fix/workaround for this irritation?
stuck

20030707, 11:47 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: rounding limitation? (XL97 sr2 on Win2K/NT4)
These things happen because not all numbers can be stored exactly.
Some numbers can be represented exactly in the decimal system, for instance, 2/5 = 0.4. But other numbers can only be approximated to a finite accuracy, for example 1/3 = 0.3333333.... We have to stop somewhere, so there is a rounding error.
The same holds for the binary number system used by computers and computer programs. For instance, 1/2 = 0.1 binary (exactly), but 2/5 = 0.01100110011001100... Again, we have to stop somewhere, so there is a rounding error.
Excel stores numbers with a precision of (about) 15 decimal digits. This is enough for most practical purposes, but as you have found out, there are situations where the rounding error is noticeable.
There is no real fix for this, since it is impossible to store numbers to infinite precision. You can set Excel to store numbers with the displayed precision (Tools  Options..., Calculation tab, Precision as displayed check box), but this may have unexpected side effects.

20030707, 12:09 #3
 Join Date
 Apr 2001
 Location
 Arriving Somewhere but not Here
 Posts
 698
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: rounding limitation? (XL97 sr2 on Win2K/NT4)
"we have to stop somewhere". Agreed, so no need to answer this one <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15> ...
So how come my hand held calculator can cope with the sum and give me exactly 0.15 as the answer? <img src=/S/confused.gif border=0 alt=confused width=15 height=20> Surely it too must deal with binary rounding and if the technology existed to handle the problem 20 years ago when I bought my calculator how come <img src=/S/ranton.gif border=0 alt=ranton width=66 height=37> ...fill in your favourite MS <img src=/S/bash.gif border=0 alt=bash width=35 height=39>ing text... <img src=/S/rantoff.gif border=0 alt=rantoff width=66 height=37>
<img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>
stuck

20030707, 12:20 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: rounding limitation? (XL97 sr2 on Win2K/NT4)
This is because calculator manufacturers built in some kind of fudging algorithm. The earliest calculators did show rounding errors much more than Excel does nowadays. This was unacceptable to the general public, so a rounding mechanism was built in. As with the "Precision as displayed" option in Excel, this may have undesired side effects in some circumstances, but that is less likely to cause problems for users of handheld calculators than in a complicated spreadsheet.

20030707, 14:16 #5
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: rounding limitation? (XL97 sr2 on Win2K/NT4)
Well, I thought you said that Excel also displayed your answer as .15 <img src=/S/yep.gif border=0 alt=yep width=15 height=15> .
Change your formula to the following and it should fix your problem:
<pre>=ROUND((2.032)/0.2,2)
</pre>
Legare Coleman

20030708, 09:13 #6
 Join Date
 Apr 2001
 Location
 Arriving Somewhere but not Here
 Posts
 698
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: rounding limitation? (XL97 sr2 on Win2K/NT4)
That is not an option nor is working with 'precision as displayed'. This is just a trivial example. In my actual sheet the Round function is part of a more complex formula that is replicated down several columns and 1 dp is required.
As I discovered and as Hans explained, it just so happens that on rare occasions the rounding fails. I didn't really expect a fix I just wondered how other people had tackled this issue.
I can think of a possible kludge involving something like "if the right 10 characters are 9 then Roundup otherwise Round" but I'm not sure it's worth the effort as in my case the answer of 0.1, rather than 0.2 is erring on the cautious side so I can live with it.
(not really)stuck

20030708, 21:06 #7
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: rounding limitation? (XL97 sr2 on Win2K/NT4)
If the If statement is an option, why isn't the Round an option? If your formula is in A1, try these:
<pre>=ROUND(ROUND(A1,2),1)
=ROUND(A1+0.00000000001,1)
</pre>
Checking the right 10 characters for 9s is not going to work. The rounding can make several of the last digits not equal to 9s.Legare Coleman

20030709, 09:16 #8
 Join Date
 Apr 2001
 Location
 Arriving Somewhere but not Here
 Posts
 698
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: rounding limitation? (XL97 sr2 on Win2K/NT4)
AHH! (I wish there was a light bulb smilie) rounding to two 2dp then rounding again to 1dp.
<img src=/S/drop.gif border=0 alt=drop width=23 height=23> Why didn't I think of that? What is is to be an WMVP.
Thanks!
(definitely not)stuck