Compensating for Excel rounding errors

[CB]

Hi all,

I’m having some issues with the following, which I stumbled across purely by
coincidence. The worksheet was set up by others with more Excel experience
than I so I’m not sure as to the best workaround.

FYI, numbers in A1:B3 are entered with only one decimal place. Cells C1:C4
and D4 are calculated. Formulas are noted at the bottom of the post.

The formula in D4 ***appears*** to be incorrect. Since C1:C3 are EXACTLY
-0.5, so is C4. According to the formula, D4 should display “O.Kâ€. Instead,
it displays “Too Low.â€

I believe the problem lies with the way Excel is storing the numbers (at the
binary level). When troubleshooting, I set cells A1:C3 (and C4) to display
with 30 decimals. I then found the calculation for C3 is not EXACTLY -0.5 as
it should be. Instead, there is a 2 in the fifteenth decimal place.

I tried using the ROUND function in C1:C4 but D4 displays “FALSE.†D4 still
displays FALSE if I use ROUND in that formula as well.

What am I missing?

Thanks for any and all assistance!

Regards,
Chris



A B C
D
Standard Unit Diff
1 16.3 15.8 -0.5
2 16.2 15.7 -0.5
3 16.1 15.6 -0.5
4 Average Difference -0.5
Too Low

The formula for “Diff†in C1:C3 is: =IF(A1="","",B1-A1).
The formula for “Average Difference†in C4 is:
=IF(C1="","",AVERAGE(C1:C3))
The formula for D4 is:
=IF(C4="","",IF(C4>0.5,"TOO HIGH",IF(C4<-0.5,"TOO LOW",IF(C4>-0.5,"O.K."))))

 

[CB]

Hi again,

I **think** I may have found a work-around - sure, just as I'm leaving for
the day! Regardless, I'd appreciate any feedback. The formula for D4 always
struck me as a little more complicated than need be (the final IF statement).
I changed it to the following and all is well.

=IF(C4="","",IF(ROUND(C4>0.5,1),"TOO HIGH",IF(ROUND(C4<-0.5,1),"TOO
LOW","O.K.")))

I appreciate any and all feedback.

Chris

 

[CB]

And...

It also works if I use ROUND in C1:C3 and change D4 accordingly:

=IF(C4="","",IF(C4>0.5,"TOO HIGH",IF(C4<-0.5,"TOO LOW","O.K.")))

Chris

 

[Shane Devenshire]

Hi,

Round is one solution but here is the problem explained:

Computers work in binary, we work in decimals

Here is everything you need to know about this issue (and more):

http://support.microsoft.com/kb/78113/en-us
http://support.microsoft.com/kb/42980
http://support.microsoft.com/kb/214118
http://www.cpearson.com/excel/rounding.htm
http://docs.sun.com/source/806-3568/ncg_goldberg.html

If this helps, please cliick the Yes button.

Cheers,
Shane Devenshire

 

[joeu2004]

It also works if I use ROUND in C1:C3 and change D4 accordingly:
=IF(C4="","",IF(C4>0.5,"TOO HIGH",IF(C4<-0.5,"TOO LOW","O.K.")))

This is closer to the right approach. I would also round the average,
computed in C4. So the formulas should be (presumably C1 is copied
into C2 and C3):

C1: =IF(A1="","",ROUND(B1-A1,1))

C4: =IF(C1="","",ROUND(AVERAGE(C1:C3),1))

numbers in A1:B3 are entered with only one decimal place

But unfortunately, most of the time, WYSI-not-WYG when it comes to
numbers with decimal fractions. Most such numbers are not stored
exactly internally. This leads to small numerical "errors". This is
a limitation of the technology that Excel relies on -- as do most
applications (but not all).

In your example, the problemmatic numbers are in A3:B3. 16.1 and 15.6
are stored internally as exactly
16.10000000000000142108547152020037174224853515625 and
15.5999999999999996447286321199499070644378662109375. When they are
subtracted, the result is exactly
0.5000000000000017763568394002504646778106689453125.

As you can see (and you learned empirically), the result is not
exactly 0.5. In fact, Excel will display it as 0.500000000000002 when
formatted as Number with 15 dp.

=IF(C4="","",IF(ROUND(C4>0.5,1),"TOO HIGH",
IF(ROUND(C4<-0.5,1),"TOO LOW","O.K.")))

That is nonsensical. For example, you are rounding the boolean result
of C4>0.5, which is 0 or 1. I suspect you want to write:

=if(C4="", "", if(round(C4,1)>0.5, "TOO HIGH", ...etc...)))

Perhaps you did just that in your spreadsheet, and you simply had a
typo when you entered the formula into your posting. Always cut-and-
paste examples to avoid such mistakes in the future.

HTH.

 

[joeu2004]

PS....

In your example, the problemmatic numbers are in A3:B3.  16.1 and 15.6
are stored internally as exactly
16.10000000000000142108547152020037174224853515625 and
15.5999999999999996447286321199499070644378662109375.

I did not intend to imply that the numbers in A1:B2 are stored
exactly. In fact, they are not. But coincidentally, their difference
is exactly 0.5, even when we put parentheses around the expression,
which side-steps Excel's attempt to ameliorate such numerical
"errors", and even when we do the computation in VBA.

I want to reiterate that is purely by coincidental. The result of B3-
A3 is much more common.

PS: I also notice that I computed A3-B3, not B3-A3 as you did. That
does not make any difference other than the sign of the result.

 

[CB]

Hi shane,

Thanks for the links. I was familiar with this type of thing once - a life
time ago.

Regards,
Chris

 

[CB]

This is closer to the right approach. I would also round the average,
computed in C4. So the formulas should be (presumably C1 is copied
into C2 and C3):

C1: =IF(A1="","",ROUND(B1-A1,1))

C4: =IF(C1="","",ROUND(AVERAGE(C1:C3),1))

Good to know I was on the right track.

But unfortunately, most of the time, WYSI-not-WYG when it comes to
numbers with decimal fractions. Most such numbers are not stored
exactly internally. This leads to small numerical "errors". This is
a limitation of the technology that Excel relies on -- as do most
applications (but not all).

In your example, the problemmatic numbers are in A3:B3. 16.1 and 15.6
are stored internally as exactly
16.10000000000000142108547152020037174224853515625 and
15.5999999999999996447286321199499070644378662109375. When they are
subtracted, the result is exactly
0.5000000000000017763568394002504646778106689453125.

Interesting!

As you can see (and you learned empirically), the result is not
exactly 0.5. In fact, Excel will display it as 0.500000000000002 when
formatted as Number with 15 dp.




That is nonsensical. For example, you are rounding the boolean result
of C4>0.5, which is 0 or 1. I suspect you want to write:

=if(C4="", "", if(round(C4,1)>0.5, "TOO HIGH", ...etc...)))

I thought it seemed rather odd when I was playing around with the formula
but it ***seemed*** to work. I didn't spend a lot of time playing with that
formula when I decided to use round in the other forumlas instead.

Perhaps you did just that in your spreadsheet, and you simply had a
typo when you entered the formula into your posting. Always cut-and-
paste examples to avoid such mistakes in the future.

I believe I did have that in my spreadsheet (briefly) but in looking at it
again I can see why you say it is nonsensical.

Thanks for your feedback. I do appreciate it.

Chris

 

[CB]

PS....



I did not intend to imply that the numbers in A1:B2 are stored
exactly. In fact, they are not. But coincidentally, their difference
is exactly 0.5, even when we put parentheses around the expression,
which side-steps Excel's attempt to ameliorate such numerical
"errors", and even when we do the computation in VBA.

I understood your intention. I took programming courses ages and ages ago
and was familiar with how computers stored numbers but that knowledge was
forgotten quite some time ago. This experience has been a refresher.