Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
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? |
#3
|
|||
|
|||
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? |
#4
|
|||
|
|||
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? |
#5
|
|||
|
|||
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? |
#6
|
|||
|
|||
You're welcome. Glad it helped.
Jerry Ohmega wrote: Thank You so much!!!!!!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Strange Excel problem | Setting up and Configuration of Excel | |||
Excel Display Problem | Excel Discussion (Misc queries) | |||
Excel mouse selecting problem | Excel Discussion (Misc queries) | |||
Excel 2000 to Excel 2002 problem | Excel Discussion (Misc queries) | |||
Staring Excel Problem | Excel Discussion (Misc queries) |