| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel extending result to multiple decimals
I have an issue with excel:
cell c7: 123200 (entered direct into cell-not calculated) cell d7: -123151.6 (entered direct into cell-not calculated) cell e7 forumula: =sum(c7:d7) result displayed: 48.4 (appears correct) cell j7 value = 48.4 (entered direct into cell-not calculated) cell j7 formula: =j7=e7 result: FALSE When I copy paste special values from cell e7 to another cell the result is 48.3999999999942. How can that be? What is the problem? This is simple math. It has been tested on multiple computers with similar configuration. Please help. MS office 2003 (11.6355.6408) SP1 MS Windows XP V2002 SP1 dls2193 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel extending result to multiple decimals
You must round any calculation result before you compare it to a manually
entered quantity. e7: =ROUND(SUM(C7:D7),1) HTH -- AP "dls2193" a écrit dans le message de news: ... I have an issue with excel: cell c7: 123200 (entered direct into cell-not calculated) cell d7: -123151.6 (entered direct into cell-not calculated) cell e7 forumula: =sum(c7:d7) result displayed: 48.4 (appears correct) cell j7 value = 48.4 (entered direct into cell-not calculated) cell j7 formula: =j7=e7 result: FALSE When I copy paste special values from cell e7 to another cell the result is 48.3999999999942. How can that be? What is the problem? This is simple math. It has been tested on multiple computers with similar configuration. Please help. MS office 2003 (11.6355.6408) SP1 MS Windows XP V2002 SP1 dls2193 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel extending result to multiple decimals
Ardus has told you how to solve the problem but has not explained why.
Computers store numbers in binary notation (base 2) and some decimal (base 10) numbers have no exact representation in binary when limited to 15 digits. For more read: http://support.microsoft.com/kb/78113 best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "dls2193" wrote in message ... I have an issue with excel: cell c7: 123200 (entered direct into cell-not calculated) cell d7: -123151.6 (entered direct into cell-not calculated) cell e7 forumula: =sum(c7:d7) result displayed: 48.4 (appears correct) cell j7 value = 48.4 (entered direct into cell-not calculated) cell j7 formula: =j7=e7 result: FALSE When I copy paste special values from cell e7 to another cell the result is 48.3999999999942. How can that be? What is the problem? This is simple math. It has been tested on multiple computers with similar configuration. Please help. MS office 2003 (11.6355.6408) SP1 MS Windows XP V2002 SP1 dls2193 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel extending result to multiple decimals
Summing is not the issue. As Bernard pointed out, the issue is that .6 has
not exact representation in binary and must be approximated. What you are seeing is perfectly correct arithmetic given the unavoidable approximation to your intput. You will get a similar result from almost all computer software, since the binary number format that Excel uses is that specified by an IEEE standard. You can use the D2D function at http://groups.google.com/group/micro...06871cf92f8465 to see that when you type in -123151.6 you really get -1.2315160000000000582076609134674072265625 so that your sum should be 48.39999999999417923390865325927734375 which Excel correctly reports to its documented limit of 15 digits. Jerry "dls2193" wrote: I am familiar with the round function. It seems odd that there can be such discrepency when summing only two numbers. Thanks to all who replied! dls2193 "Ardus Petus" wrote: You must round any calculation result before you compare it to a manually entered quantity. e7: =ROUND(SUM(C7:D7),1) HTH -- AP "dls2193" a écrit dans le message de news: ... I have an issue with excel: cell c7: 123200 (entered direct into cell-not calculated) cell d7: -123151.6 http://groups.google.com/group/micro...06871cf92f8465 (entered direct into cell-not calculated) cell e7 forumula: =sum(c7:d7) result displayed: 48.4 (appears correct) cell j7 value = 48.4 (entered direct into cell-not calculated) cell j7 formula: =j7=e7 result: FALSE When I copy paste special values from cell e7 to another cell the result is 48.3999999999942. How can that be? What is the problem? This is simple math. It has been tested on multiple computers with similar configuration. Please help. MS office 2003 (11.6355.6408) SP1 MS Windows XP V2002 SP1 dls2193 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel extending result to multiple decimals
Sorry, in switching from the scientific notation output of D2D() to fixed
decimal, I filed to move the decimal point. The decimal representation of the binary approximation to -123151.6 is of course -123151.60000000000582076609134674072265625 Jerry "Jerry W. Lewis" wrote: Summing is not the issue. As Bernard pointed out, the issue is that .6 has not exact representation in binary and must be approximated. What you are seeing is perfectly correct arithmetic given the unavoidable approximation to your intput. You will get a similar result from almost all computer software, since the binary number format that Excel uses is that specified by an IEEE standard. You can use the D2D function at http://groups.google.com/group/micro...06871cf92f8465 to see that when you type in -123151.6 you really get -1.2315160000000000582076609134674072265625 so that your sum should be 48.39999999999417923390865325927734375 which Excel correctly reports to its documented limit of 15 digits. Jerry "dls2193" wrote: I am familiar with the round function. It seems odd that there can be such discrepency when summing only two numbers. Thanks to all who replied! dls2193 "Ardus Petus" wrote: You must round any calculation result before you compare it to a manually entered quantity. e7: =ROUND(SUM(C7:D7),1) HTH -- AP "dls2193" a écrit dans le message de news: ... I have an issue with excel: cell c7: 123200 (entered direct into cell-not calculated) cell d7: -123151.6 http://groups.google.com/group/micro...06871cf92f8465 (entered direct into cell-not calculated) cell e7 forumula: =sum(c7:d7) result displayed: 48.4 (appears correct) cell j7 value = 48.4 (entered direct into cell-not calculated) cell j7 formula: =j7=e7 result: FALSE When I copy paste special values from cell e7 to another cell the result is 48.3999999999942. How can that be? What is the problem? This is simple math. It has been tested on multiple computers with similar configuration. Please help. MS office 2003 (11.6355.6408) SP1 MS Windows XP V2002 SP1 dls2193 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I get cell data in multiple Excel files into one summary fi | Excel Worksheet Functions | |||
| Multiple workbooks on Taskbar in Excel 97 | Excel Discussion (Misc queries) | |||
| EXCEL:NUMBER TO GREEK WORDS | Excel Worksheet Functions | |||
| Conversion | Excel Worksheet Functions | |||
| Convert Numeric into Text | Excel Worksheet Functions | |||
Linear Mode
