calculate problem in excel
 

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?

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

"Ohmega" wrote:

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?

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

"Bob Umlas, Excel MVP" wrote:

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

"Ohmega" wrote:

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?

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.099999999999999644728632119949907064437866210937 5

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

Ohmega wrote:

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

"Bob Umlas, Excel MVP" wrote:


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

"Ohmega" wrote:


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?

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

"Jerry W. Lewis" wrote:

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.099999999999999644728632119949907064437866210937 5

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

Ohmega wrote:

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

"Bob Umlas, Excel MVP" wrote:


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

"Ohmega" wrote:


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?

You're welcome. Glad it helped.

Jerry

Ohmega wrote:

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