ExcelBanter - Excel extending result to multiple decimals

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Excel extending result to multiple decimals (https://www.excelbanter.com/excel-worksheet-functions/88126-excel-extending-result-multiple-decimals.html)

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

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

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

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