I don´t view the value right after six decimal place in Excel

[Claudio]

Even making calculations only by the Excel submitted figures are not correct
from the 6 decimal place, and I updated with Office (last SP), which can be?

Other e-mail for contact: (e-mail address removed)

 

[David Biddulph]

It's no good just saying vaguely: "... are not correct"
Tell us what formula you used, what numbers were the inputs to that formula,
what answer you got, and what answer you expected.

 

[Claudio]

Suppose cell A1 = 1,0198424825731 (thirteen decimal places after comma) and
cell B1 = 2136,613241 (six decimal places after comma) the result is
presented in Excel = 2179,008952 (only six decimal places after comma) and
should be presented the following results = 2179,00895199999.

After a six decimal places is presented 000000 (zeros)

 

[Claudio]

This problem also occurs in Excel 2007.

Suppose cell A1 = 1,0198424825731 (thirteen decimal places after comma) and
cell B1 = 2136,613241 (six decimal places after comma) the result is
presented in Excel = 2179,008952 (only six decimal places after comma) and
should be presented the following results = 2179,00895199999.

After a six decimal places is presented 000000 (zeros)

 

[Bob I]

Actually Excel produces

2137,63308348257

as opposed to

2137,6330834825731

which is due to the limit of precision available in Excel.

 

[Claudio]

'Not that this is happening with me after the sixth decimal place has only
zeros. How to reach us below after the sixth decimal place?

 

[David Biddulph]

What calculation is giving you 2137.63308348257 ?
And where do you get 2137.6330834825731 ?
Doesn't 1.0198424825731 * 2136.613241 come to something a lot closer to the
OP's 2179.008952 ?

 

[Jerry W. Lewis]

Check your math; in decimal arithmetic your calculation results in
2179.0089519999972
not
2179.00895199999
Excel's document display limit is 15 decimal digits, so the result should
display as 2179.008952

As for what is going on "under the hood", Bob added where you multiplied,
but his explanation was basically right.

Excel and almost all other numerical software uses IEEE double precision
binary representation for floating point numbers. Most terminating decimal
fractions are non-terminating binary fractions that can only be approximated
(just as 1/3 can only be approximated as a finite length decimal fraction).
IEEE double precision supports 53 bit accuracy for the mantissa, so your
original numbers were unavoidably approximated as
1.0198424825731000620265831457800231873989105224609375
2136.6132410000000163563527166843414306640625
When multiplied together, the result is
2179.008951999997359624822203685659147127872796507233035465112003...
which reduces to the IEEE double precision approximation of
2179.0089519999974072561599314212799072265625
This last value is what Excel correctly displays to its documented display
limit of 15 decimal digits.

Jerry

 

[David Biddulph]

You must have missed my earlier reply where I said that Excel's precision is
15 significant figures.

If the answer to a higher precision is 2179.0089519999972104171, then when
you calculate it to 15 significant figures the correct answer is
2179.00895200000, not 2179.00895199999.

 

[Bob I]

You have reached the limit of precision available in Excel. The decimal
place has no bearing on this, if you add 10000 to the number the last 7
will become a Zero too, and then it would be after the fifth. There is
only 15 significant digits available, after that zeros only.

 

[Claudio]

|I don´t understand. In the Windows Calculator the result is presented
2179,0089519999972104171 and if in the Excel's precision is
15 significant figures, but the number 2179.008952 dont´t have 15 digits.

Why in Excel is the number 2179.00895199999 nearest to the number
2179.00895200000.
Each digit number would not be one? Even that was 15 digits (15 numbers)
would have to show up 2179.0089519999 considering the point

 

[Jerry W. Lewis]

The Windows calculator is documented to give at least 32 decimal digit
accuracy--probably IEEE quadruple precision, which is far more than the IEEE
standard double precision used by Excel and most other numeric software.

The 16th digit in 2179,0089519999972104171 is a 7, which rounds up to give
2179,00895100000 to 15 digits, but Excel does not display trailing 0's
beyond the decimal point, unless explicitly formatted to do so.

Jerry

 

[Claudio]

OK I understand now about the 15 decimal digits.

So there isn´t a way (function, macro, programming) to obtain the number
2179.00895199999 instead of the number 2179.00895200000?

 

[Claudio]

OK I understand now about the 15 decimal digits.

So there isn´t a way (function, macro, programming) to obtain the number
2179.00895199999 instead of the number 2179.00895200000?

 

[Ron Rosenfeld]

The Windows calculator is documented to give at least 32 decimal digit
accuracy--probably IEEE quadruple precision, which is far more than the IEEE
standard double precision used by Excel and most other numeric software.

The 16th digit in 2179,0089519999972104171 is a 7, which rounds up to give
2179,00895100000 to 15 digits, but Excel does not display trailing 0's
beyond the decimal point, unless explicitly formatted to do so.

Jerry

It is interesting, to me, that using the CDec data type in VBA gives the same
result as the Windows Calculator, as does the Xmult function in Xlnumbers

====================================
Function DecMult(n1 As Variant, n2 As Variant) As Variant
DecMult = CStr(CDec(n1) * CDec(n2))
End Function
==============================

2179.0089519999972104171

But different from the result after applying the IEEE binary limitations.

2179.008951999997359624822203685659147127872796507233035465112003...
--ron

 

[David Biddulph]

If you were looking at a number of digits smaller than 15, and you don't
want to round to the closest value at the specified number of digits, which
Excel does by default, but instead you wanted to round down or truncate, you
may wish to look at the ROUNDDOWN, FLOOR, or TRUNCATE functions. Excel
help will tell you about them.

However in this case you don't have the number 2179.0089519999972104171 in
the first place, so I think you're out of luck trying to do it all in one
go. If you want to work to more than 15 digits, don't use Excel.

There might be a complicated workaround in splitting your input numbers into
most significant and least significant parts, manipulating those separately,
deciding where you needed to do your truncation in the least significant
part, and then gluing together the answers.
In this case, as your input numbers are greater than 1 but have non-integer
parts, it is as simple as
=INT(A1)*INT(B1)+TRUNC(INT(A1)*MOD(B1,1)+INT(B1)*MOD(A1,1)+MOD(A1,1)*MOD(B1,1),15-LOG(A1*B1))
but in a more general case it would be more complicated.

 

[Claudio]

the function informed solved the problem.

Thank you very much.

 

[Ron Rosenfeld]

OK I understand now about the 15 decimal digits.

So there isn´t a way (function, macro, programming) to obtain the number
2179.00895199999 instead of the number 2179.00895200000?

You can use the CDec data type in VBA and truncate the resultant string.

====================
Function DecMult(n1 As Variant, n2 As Variant) As Variant
DecMult = CStr(CDec(n1) * CDec(n2))
End Function
======================

--> 2179.0089519999972104171

Or you could use the Xnumbers add-in from
http://digilander.libero.it/foxes/SoftwareDownload.htm

which affords high-precision math routines for Excel.
--ron

 

[Jerry W. Lewis]

If you were looking at a number of digits smaller than 15, and you don't
want to round to the closest value at the specified number of digits, which
Excel does by default, but instead you wanted to round down or truncate, you
may wish to look at the ROUNDDOWN, FLOOR, or TRUNCATE functions. Excel
help will tell you about them.

However in this case you don't have the number 2179.0089519999972104171 in
the first place, so I think you're out of luck trying to do it all in one
go.

That is not strictly true. If you are careful to avoid MS's "helpful"
fuzzing of the calculations, such as in my code posted at
http://groups.google.com/group/microsoft.public.excel/msg/6efb95785d1eaff5
then you can verify that the value of Excel's result is
2179.008951999997...
RoundDown won't reveal this, because all of Excel's rounding functions
appear to double round, first to 15 figures, and then to the specified
accuracy. VBA does not double round, so the VBA code

a = 1.0198424825731
b = 2136.613241
c = a * b
MsgBox Fix(c * 10 ^ 11) / 10 ^ 11

will dsiplay 2179.00895199999 without the decimal data type.

Most 16 digit numbers can be represented in IEEE double precision.
Presumably MS chose not to display more than 15 digits because not ALL 16
digit numbers are representable, and when you enter say 9007199254740993
(=2^53+1) they would rather explain a displayed value of 9007199254740990
rather than explaining a displayed value of 9007199254740992.

Jerry

 

[dsmall2112]

I think Microsoft/Excel 2007 should enable user to configure their
preferences for the "15-DIGIT PRECISION" default. Perhaps, add a
setting/check-box asking user "handle Integer/Numeric data having more than
15 digits as TEXT" would be a nice work-around solution too.

I've read that the limit is well-disclosed in HELP materials, but that still
doesn't give users a viable solution.

I use PC-SAS to export data into Excel (uses Microsoft Jet drivers) and
realized that the driver still only allows 255 columns, though the row
limitation was increased to the 1 million row limit available in Excel 2007.
This would seem another "miss".

I will occasionally copy/paste data into Excel....if any all-number data
(with 16+ digits) then CTRL-V will result in trucated data. I did discover,
however, after pasting the data I could click the clipboard icon and re-paste
fia "Use Text Import Wizard" and specify applicable columns as TEXT. (just
wish this was a setting for importing XLS datasets created from other sources
such as PC-SAS).