15 Significant Digits Limitation a Mistake for Spatial Information

M

Matthew

The IEEE Standard of limiting 15 significant digits of precision is a
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?
 
R

Ron Rosenfeld

The IEEE Standard of limiting 15 significant digits of precision is a
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?
Click to expand...

I suspect you'll have better results obtaining a tool that deals with higher
precision
--ron
 
M

MartinW

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 and pencil and
paper. You often find that their accuracy was as
good as today's most sophisticated technology
can produce.

Regards
Martin
 
N

Nick

Matthew said:
The IEEE Standard of limiting 15 significant digits of precision is a
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?
Click to expand...

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.
 
B

Bernard Liengme

And it we are down to 1/10,000 of a mm (1E-7 m ? about 100 nanometres) are
we going to take temperature, tidal force, etc into account?
 
S

Shane Devenshire

Hi Matthew,

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
 
P

Paul Hyett

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
Click to expand...

Log tables - that takes me back...
 
R

Rick Rothstein \(MVP - VB\)

Just to follow up...

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
 
J

joeu2004

a latitude value of 39.729831646409565 in Denver Colorado.
Excel only stores it as 39.7298316464095.
Click to expand...

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.
Click to expand...

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.
Click to expand...

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 -----
 
R

Rick Rothstein \(MVP - VB\)

Where did you get THAT from!!! Excel has nowhere near that precision. You
might want to read up on this subject...

http://office.microsoft.com/en-us/excel/HP100541491033.aspx

http://blogs.msdn.com/excel/archive...es-excel-give-me-seemingly-wrong-answers.aspx

http://www.cpearson.com/excel/rounding.htm

Rick


a latitude value of 39.729831646409565 in Denver Colorado.
Excel only stores it as 39.7298316464095.
Click to expand...

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.
Click to expand...

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.
Click to expand...

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 -----
 
J

Jerry W. Lewis

Rick Rothstein (MVP - VB) said:
Where did you get THAT from!!! Excel has nowhere near that precision. ...
Click to expand...

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/micr..._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
 
E

Earl Kiosterud

In any event, 39.729831646409564882560516707599163055419921875 implies precision that isn't
there in the original stored IEEE number.
--
Regards from Virginia Beach,

Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
 
R

Rick Rothstein \(MVP - VB\)

In any event, 39.729831646409564882560516707599163055419921875
implies precision that isn't there in the original stored IEEE number.
Click to expand...

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
 
J

Jerry W. Lewis

You appear to have missed the point that joeu2004 and I tried to make, namely
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
 
J

joeu2004

In any event, 39.729831646409564882560516707599163055419921875
implies precision that isn't there in the original stored IEEE number.
Click to expand...

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
 
H

Howard Kaikow

Matthew said:
The IEEE Standard of limiting 15 significant digits of precision is a
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?
Click to expand...


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.
 
Top