Another Bug ?

  • Thread starter Thread starter kerry.lawler
  • Start date Start date
K

kerry.lawler

Hi

Apologies if you people already know this - but I was asked a question
by someone I cannot answer.

The person was setting a conditional format too show values >= 0.03,
and he have the following calculation in to arrive at this value - but
did not format the cell as the condition was not true ???

Enter 12.5 to A1
Enter 12.47 to B1
Enter = ABS(A1-B1) to C1

the answer is 0.03 - but if you expand the decimal places it isn't !!

It also doesnt work with 6.5-6.47, 24.5-24.47........I havent gone any
further because I am horrified :-(

Any suggestions or advice would be gratefully received
 
<Apologies if you people already know this>

This is indeed well-known behavior, not just of Excel, but of all software that complies to the standards of IEEE:

http://support.microsoft.com/kb/78113


--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hi
|
| Apologies if you people already know this - but I was asked a question
| by someone I cannot answer.
|
| The person was setting a conditional format too show values >= 0.03,
| and he have the following calculation in to arrive at this value - but
| did not format the cell as the condition was not true ???
|
| Enter 12.5 to A1
| Enter 12.47 to B1
| Enter = ABS(A1-B1) to C1
|
| the answer is 0.03 - but if you expand the decimal places it isn't !!
|
| It also doesnt work with 6.5-6.47, 24.5-24.47........I havent gone any
| further because I am horrified :-(
|
| Any suggestions or advice would be gratefully received
|
|
|
 
Just to add to Niek's response.

Select C1 and hit F2, then F9, then Enter.

You'll see that the formula in C1 evaluates to: 0.0299999999999994

Maybe you could use some small number in your rule:
=abs(c1-0.03)<0.000001
 
But of course you don't have to apologize!
If it were so obvious, it wouldn't be the most frequently asked question about Excel.
It's about time (and has been over the last 20 years) that Excel gives you at least the option of decimal arithmetic. Performance
is probably not an issue in 99% of the cases.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| <Apologies if you people already know this>
|
| This is indeed well-known behavior, not just of Excel, but of all software that complies to the standards of IEEE:
|
| http://support.microsoft.com/kb/78113
|
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
|| Hi
||
|| Apologies if you people already know this - but I was asked a question
|| by someone I cannot answer.
||
|| The person was setting a conditional format too show values >= 0.03,
|| and he have the following calculation in to arrive at this value - but
|| did not format the cell as the condition was not true ???
||
|| Enter 12.5 to A1
|| Enter 12.47 to B1
|| Enter = ABS(A1-B1) to C1
||
|| the answer is 0.03 - but if you expand the decimal places it isn't !!
||
|| It also doesnt work with 6.5-6.47, 24.5-24.47........I havent gone any
|| further because I am horrified :-(
||
|| Any suggestions or advice would be gratefully received
||
||
||
|
|
 
Enter 12.5 to A1
Enter 12.47 to B1
Enter = ABS(A1-B1) to C1
the answer is 0.03 - but if you expand the decimal places it isn't !!
[....]
Any suggestions or advice would be gratefully received
Click to expand...

Yes, this is a well-known problem with binary computer arithmetic.
Try:

=round(abs(A1-B1),2)
 
To elaborate slightly on the prior responses, Excel (and almost all other
computer software) works in binary. Most terminating decimal fractions
(including .47) have no exact binary representation (just as 1/3 has no exact
decimal representation) and must be approximated. When you do math with
approximate inputs, it should be no surprise when the output is only
approximate.

The binary approximation to 12.47 has a decimal value of
12.4700000000000006394884621840901672840118408203125
When you subtract that from 12.5, you correctly get
0.0299999999999993605115378159098327159881591796875
All of which, Excel faithfully reported to its documented display limit of
15 figures.

The simple way to think about is to remember that the math is right, but
that floating point numbers may not have the values you expect beyond the
15th digit.

Jerry
 
Back
Top