15 Significant Digits Limitation a Mistake for Spatial Information
Matthew
practical mistake for Microsoft to follow. Especially for marking Latitude &
Longitude coordinates for geocodes and establishing and storing boundaries
for maps at zoomed levels. Take for instance, a latitude value of
39.729831646409565 in Denver Colorado. Excel only stores it as
39.7298316464095. The longitude of -105.02543449401855 is stored as
-105.025434494018. You loose two significant digits of accuracy for this
spacial information.
Can the decision be reevaluated for practical purposes?
Matthew
Ron Rosenfeld
wrote:
I suspect you'll have better results obtaining a tool that deals with higher
precision
--ron
MartinW
Just how much precision are you talking about here?
I have worked with many surveyors over the years
who can calculate their position on the earth's surface
down to the millimetre, and they do it with a calculator
that only has a precision of 10 significant digits.
In fact, in the surveying game, you are constantly
amazed at the accuracy of the old guys of 100 years
ago who did it all with log tables and pencil and
paper. You often find that their accuracy was as
good as today's most sophisticated technology
can produce.
Regards
Martin
"Matthew" <Mat...@discussions.microsoft.com> wrote in message
news:98365604-FD37-43AC...@microsoft.com...
Nick
Why does it matter?
The precision seems to be to 1/10000 of a millimetre.
If you need better than that you should use a different model or a
different tool.
Excel is a general purpose tool.
Bernard Liengme
we going to take temperature, tidal force, etc into account?
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email
"Nick" <Nick...@yahoo.co.uk> wrote in message
news:66abd6F...@mid.individual.net...
Shane Devenshire
Actually you can get Excel to track and calculate with more than 15 digits
by declaring the variables as Variants as mentioned below:
"At this time the Decimal data type can only be used within a Variant, that
is, you cannot declare a variable to be of type Decimal. You can, however,
create a Variant whose subtype is Decimal using the CDec function."
You can make calculations in VBA using this data type and then you can enter
those values in the spreadsheet as text. You can also create VBA functions
to work with these.
Here is a simple example:
Sub Macro1()
Dim x As Variant
x = CDec(Range("A1"))
y = 2 * x
Range("A2") = "'" & y
End Sub
Assume cell A1 contains the text entry '1.2345678901234567890123456 when you
run this macro cell A2 will contain the text entry:
'2.4691357802469135780246912
I am using a VBA macro you could also use a VBA Function.
Cheers,
Shane Devenshire
Microsoft Excel MVP
"Matthew" <Mat...@discussions.microsoft.com> wrote in message
news:98365604-FD37-43AC...@microsoft.com...
Paul Hyett
microsoft.public.excel :
>Hi Matthew,
>
>Just how much precision are you talking about here?
>I have worked with many surveyors over the years
>who can calculate their position on the earth's surface
>down to the millimetre, and they do it with a calculator
>that only has a precision of 10 significant digits.
>
>In fact, in the surveying game, you are constantly
>amazed at the accuracy of the old guys of 100 years
>ago who did it all with log tables
Log tables - that takes me back...
> and pencil and
>paper. You often find that their accuracy was as
>good as today's most sophisticated technology
>can produce.
--
Paul Hyett, Cheltenham (change 'invalid83261' to 'blueyonder' to email me)
Rick Rothstein (MVP - VB)
But you must perform your calculations with simple math operators and watch
for overflows. Here is a previous posting of mine that demonstrates what to
watch out for...
You could cast (you can't Dim) a Variant variable as a Decimal type (96-bit
number) and get some 28 or 29 digits of accuracy depending if there is a
decimal in the answer or not. Simply Dim a variable as Variant and CDec a
number (any number will do) into it to make it the Decimal type. Thereafter,
that variable will track 28/29 digits of accuracy. For example the following
function will calculate factorials up to 29 digits of display before
reverting to exponential display.
Function BigFactorial(ByVal N As Integer) As Variant
If N < 28 Then
BigFactorial = CDec(1)
Else
BigFactorial = CDbl(1)
End If
For x = 1 To N
BigFactorial = x * BigFactorial
Next
End Function
However, you have to watch out for overflows with Decimal data types -- once
over 28/29 characters, they will produce an overflow error. So, if you tried
to use the above function like this
Debug.Print 10*BigFactorial(27)
you would get an overflow error but
Debug.Print 10*BigFactorial(28)
would work fine (the difference being in the first case BigFactorial has a
Decimal subtype and in the second case the subtype is a Double).
More generally, if a Variant variable is assigned a value that was cast to
Decimal, any calculation involving that variable will be "performed" as a
Decimal; and then the result cast back to the variable receiving it. If the
result is assigned back to the variable that was originally cast to Decimal,
that variable continues to contain a Decimal type value. For example,
X = CDec(135.6)
X = X - 135
X = X / 7
Print X ==> 0.0857142857142857142857142857
You have to be careful with this though . . . all VB functions return
non-Decimal data.and assigning *that* back to the Variant that was cast as
Decimal "collapses" it back to a less robust data type. For example,
continuing the code above
X = Sqr(X)
Print X ==> 0.29277002188456
Rick
"Shane Devenshire" <shanede...@sbcglobal.net> wrote in message
news:uZCqHGFn...@TK2MSFTNGP03.phx.gbl...
joeu2004
> a latitude value of 39.729831646409565 in Denver Colorado.
> Excel only stores it as 39.7298316464095.
Actually, that it is only how it is __displayed__. Excel __stores__
it as exactly 39.72983164640950093371429829858243465423583984375.
You could enter the latitude "exactly" as follows:
=39.7298316464095 + 6.5e-14
Excel stores that as exactly
39.729831646409564882560516707599163055419921875. That is the closest
IEEE binary representation using 64-bit floating point.
> The longitude of -105.02543449401855 is stored as -105.025434494018.
Likewise, enter:
=-105.025434494018 - 5.5e-13
which is stored as exactly -105.0254344940185546875. Again, that is
the closest IEEE binary representation.
> The IEEE Standard of limiting 15 significant digits of precision is a
> practical mistake for Microsoft to follow.
I doubt that it is an IEEE standard per se. The IEEE usually does not
concern itself with "presentation" issues (e.g. how things are
displayed). It is more likely an ANSI standard, if anything. But I
confess: I have not looked at either.
(If someone could post a quote from either standard or another one,
I'd appreciate it.)
What the IEEE standard might point out is that the 51 bits (binary
digits) to the right of the radix point can be represented with
__approximately__ 15 decimal digits to the right of the decimal point
(because 51*log(2) = 15.35 approximately). But that's a far cry from
saying that input or output "must" be truncated at 15 significant
digits.
FYI, there are Excel plug-ins that allow you to use numbers with many
more significant digits. I have never used any of them.
----- original posting -----
Rick Rothstein (MVP - VB)
might want to read up on this subject...
http://office.microsoft.com/en-us/excel/HP100541491033.aspx
http://www.cpearson.com/excel/rounding.htm
Rick
"joeu2004" <joeu...@hotmail.com> wrote in message
news:aa7de9be-64c4-4df4...@q24g2000prf.googlegroups.com...
Jerry W. Lewis
> Where did you get THAT from!!! Excel has nowhere near that precision. ...
>
> "joeu2004" <joeu...@hotmail.com> wrote in message
> news:aa7de9be-64c4-4df4...@q24g2000prf.googlegroups.com...
> On Apr 11, 1:14 pm, Matthew <Matt...@discussions.microsoft.com> wrote:
> > > a latitude value of 39.729831646409565 in Denver Colorado.
> > > Excel only stores it as 39.7298316464095.
> >
> > Actually, that it is only how it is __displayed__. Excel __stores__
> > it as exactly 39.72983164640950093371429829858243465423583984375.
> >
> > You could enter the latitude "exactly" as follows:
> >
> > =39.7298316464095 + 6.5e-14
> >
> > Excel stores that as exactly
> > 39.729831646409564882560516707599163055419921875. That is the closest
> > IEEE binary representation using 64-bit floating point. ...
joeu2004 got those values from looking at the decimal representation of the
binary approximations that Excel uses. While I am not sure how he actually
did so, one possible way would be to use the VBA D2D function that I posted
at
http://groups.google.com/group/microsoft.public.excel/browse_frm/thread/9f83ca3dea38e501/6efb95785d1eaff5
While IEEE double precision can only reliably represent 15 digits (probably
the basis for the MS decision to only display 15 digits), it takes 17 digits
to uniquely determine an IEEE double precision binary value, and the decimal
equivalent of that binary value may contain many more than 17 digits. In
this case, the decimal equivalent has the 47 digits that joeu2004 quoted.
When you enter more than 15 digits, Excel truncates the entry to 15 digits,
as in the OP's example. VBA however, uses up to 17 digits to produce the
most accurate double precision representation possible (though it will round
the display of that value to 15 digits and subsequent edits of that line will
then reduce the precision to a binary representation of the rounded 15 digit
displayed value. A more reliable way to enter more than 15 digits in VBA is
to use CDbl("39.729831646409565"). It is a happy accident that joeu2004's
equation does in fact produce the IEEE double precision representation of
39.729831646409565; his approach does not work in general. A VBA UDF such as
Function D2F(x As String) As Double
D2F = CDbl(x)
End Function
would allow you to reliably get the full IEEE precision in representing a
number with more than 15 digits.
Jerry
Earl Kiosterud
there in the original stored IEEE number.
--
Regards from Virginia Beach,
Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
"Jerry W. Lewis" <post_a_reply@no_e-mail.com> wrote in message
news:33A59A5B-4F1A-49CA...@microsoft.com...
Rick Rothstein (MVP - VB)
> implies precision that isn't there in the original stored IEEE number.
Which I thought would have been understood in my comment when I used "THAT"
instead of "that" (although I don't think a value
like -105.0254344940185546875 can possibly be stored exactly either).
Rick
Jerry W. Lewis
that when you enter more than 15 digits into Excel, you DON'T get the IEEE
representation of the number that you entered, because Excel first truncates
the number to 15 digits before converting to IEEE double precision.
To use your example number, -105.0254344940185546875 = -55063575*2^-19 which
only needs 26 bits (just over half of IEEE double precision) to represent
exactly. If you enter the formula -55063575*2^-19 into Excel, you get
exactly -105.0254344940185546875, despite the fact that it has 22 decimal
digits, and despite the fact that Excel will only display 15 of those digits.
If instead, you enter -105.0254344940185546875 into the cell, then you get
the IEEE approximation to -105.025434494018
(-105.0254344940180004641661071218550205230712890625, which requires the
full 53 bit accuracy of IEEE double precision) instead of the much simpler
binary representation for -105.0254344940185546875. That is why
=-105.025434494018+55063575*2^-19 displays the first 15 digits of
5.542233338928781449794769287109375E-13 instead of displaying zero.
I don't see anything in the preceding dicussion that implies more accuracy
than is available in Excel. How would you suggest discussing the fact that
direct entry into Excel of >15 digits gives you LESS accuracy than is
available in Excel, without showing the numbers that Excel is actually
storing?
Jerry
joeu2004
> In any event, 39.729831646409564882560516707599163055419921875
> implies precision that isn't there in the original stored IEEE number.
Of course it is there in the __stored__ number. Whadayathink: I just
made up those decimal digits?!
I think you are missing the point, namely: the difference between the
__displayed__ precision and the __stored__ precision.
Perhaps the following will demonstrate the point. Enter the following
formula into A1:
=1 + 2^-52
FYI, that is the smallest IEEE 64-bit floating point number greater
than 1.
But Excel will always display that as "exactly" 1 (followed any number
of zeros, corresponding to the number of decimal places that you
choose).
In fact, Excel even treats that as exactly 1 in some circumstances.
For example, the predicate "A1=1" is TRUE. But as you might infer
from the following, the predicate "(A1-1)=0" is FALSE(!).
Now enter the following formula into A2 and format the cell as
Scientific with 14 decimal places:
=(A1 - 1)
Be sure to include the parentheses to avoid Excel's heuristics. (See
http://support.microsoft.com/kb/78113 .)
You will see that Excel displays the result in A2 as
2.22044604925031E-16.
So the precision __is__ there in A1, and the value in A1 is
approximately 1.000000000000000222044504925031.
But the value in A1 can be written more exactly because 2^-52 (1 /
2^52) -- that is, the power of 1/2 corresponding to the last 1-bit in
the stored value -- can be computed exactly. The exact value in A1 is
a 54-digit string, namely:
1.0000000000000002220446049250313080847263336181640625
Howard Kaikow
news:98365604-FD37-43AC...@microsoft.com...
No.
Due to hardware limitations, computers always have used very specic formats
for numeric data.
In general, there are integer (8-bit, 16-bit, 32-bit, 64-bit, etc. fixed
types), and floating-point types.
For integer types, you have signed and unsigned types, and ones complement
or twos complement format.
Each is subject to a range of values determined by the number of bits used
to represent the data type.
For floating-point, there used to be more diifferent formats han one could
easily count.
IEEE (actually I was a participant in the early meetings 30 years ago) did
us a favor by standardiszing the format.
If you need to do any calculations that exceed the ranges of these standard
data types, then you either have to try
Decimal data type or roll your own extended precision data types.
A place to start might be "The Definitive Guide To How Computers Do Math".
ISBN 0-471-73278-8.
Or a good numerical analysis book.
JP
precision might help you understand why MS does what it does.
HTH,
JP
Howard Kaikow
Post queries here.
JP
--JP
Jerry W. Lewis
the possible interpretations.
Hardware limitations would restrict to 8-byte IEEE double precision (DP,
though PC hardware that does not support 10-byte reals has not been made for
nearly 20 years).
However, DP does not restrict to 15 decimal digits. Most 16 digit numbers
can be represented in DP, so many packages can display more than 15 digits.
It takes 17 digits to uniquely determine a DP value, so most packages
(including Excel's VBA, but not Excel) will use 17 digits of the input value
to obtain the best binary representation.
Input and output precision are more tightly linked in Excel (and in VBA)
than in most packages, since in most packages the source code is not altered
on input. In the Excel model input becomes output, so some alteration on
input of the source is unavoidable if more than 15 figures are displayed
(e.g. 9007199254740993 has no DP representation, and so must change on entry
to either 9007199254740992 or 9007199254740994 if Excel were to support 16
figures on input and output. However 15 digit input means that you cannot
easily get full DP input precision, and 15 digit output means that you cannot
easily maintain DP precision in the face of subsequent editing.
Thus 15 digit I/O is a design decision, not a physical constraint. It is
not clear to me that MS adequately thought it through before making the
decisions (particularly since they decided differently for Excel and VBA),
but since the decisions were made, I doubt that MS will reconsider them.
Jerry