calculate problem in excel

O

Ohmega

When I format the cells in excel as number with more then 15 decimal and i
try to do 5.1 - 5.0 the result is 0.0999999999999996 ...is it normal?
 
B

Bob Umlas, Excel MVP

Yes. Instead of using =5.1-5.0, try =ROUND(5.1-5.0,5) or =ROUND(5.1-5.0,8) or
some number of decimal places (under 15!) which you deem sufficient.
Bob Umlas
Excel MVP
 
O

Ohmega

Thank you for your response but my problem is i have to do that for a tons of
worksheet and a lot of formula, so may be it's a bug in excel or may be it's
a configuration problem but if you can help, i'll appreciate
thanks a million
 
J

Jerry W. Lewis

This is a fact of life that impacts almost all software, not just Excel.
The math is correct, but the input numbers had to be approximated, so
the final result is approximate.

Excel (and almost all other computer software) does binary math. Most
decimal fractions (including .1) have no exact binary reprsentation
(just as 1/3 has no exact decimal representation). Excel follows the
IEEE double precision standard, which defines the approximation to 5.1 to be
5.0999999999999996447286321199499070644378662109375

Excel only displays 15 digits (see Help for specifications), where you
would need 17 digits to detect this approximation directly. However
when you subtract 5, you are then able to see the approximation that was
already present in the number you thought was 5.1

Your options are
- live with it
- round results
- do integer math (integers can be represented exactly, so that 51-50
will return 1 as expected)

Jerry

To understand what
 
O

Ohmega

Thank You so much!!!!!!!!

Jerry W. Lewis said:
This is a fact of life that impacts almost all software, not just Excel.
The math is correct, but the input numbers had to be approximated, so
the final result is approximate.

Excel (and almost all other computer software) does binary math. Most
decimal fractions (including .1) have no exact binary reprsentation
(just as 1/3 has no exact decimal representation). Excel follows the
IEEE double precision standard, which defines the approximation to 5.1 to be
5.0999999999999996447286321199499070644378662109375

Excel only displays 15 digits (see Help for specifications), where you
would need 17 digits to detect this approximation directly. However
when you subtract 5, you are then able to see the approximation that was
already present in the number you thought was 5.1

Your options are
- live with it
- round results
- do integer math (integers can be represented exactly, so that 51-50
will return 1 as expected)

Jerry

To understand what
Click to expand...
 
Top