Calculation Results

[Steve]

Here is a puzzler, at least for me.

I am calculating distances between points identified by latitude and
longitudes.
Occasionally, if I calculate the distance between the same point, the
formula returns "#NUM!", when it SHOULD return "0".

Here is the formula - anyone have any ideas?

=(DEGEREES(ACOS(SIN(RADIANS(latitude1))*
SIN(RADIANS(latitude2)) +
COS(RADIANS(latitude1)) *
COS(RADIANS(latitude2)) *
COS(RADIANS(longitude 1 - longitude2)))))*69

If latitude 1 and 2 =33.14 and longitude 1 and 2 = 117.16, the formula
returns "#NUM".
If latitude 117.22 and longitude 33.36 are used, the result is zero.

Formulas that use the results will ignore the "#NUM", but I am curious
as to the reason.

Many thanks!

steve

 

[John McGimpsey]

Here is a puzzler, at least for me.

I am calculating distances between points identified by latitude and
longitudes.
Occasionally, if I calculate the distance between the same point, the
formula returns "#NUM!", when it SHOULD return "0".

Here is the formula - anyone have any ideas?

=(DEGEREES(ACOS(SIN(RADIANS(latitude1))*
SIN(RADIANS(latitude2)) +
COS(RADIANS(latitude1)) *
COS(RADIANS(latitude2)) *
COS(RADIANS(longitude 1 - longitude2)))))*69

If latitude 1 and 2 =33.14 and longitude 1 and 2 = 117.16, the formula
returns "#NUM".
If latitude 117.22 and longitude 33.36 are used, the result is zero.

Formulas that use the results will ignore the "#NUM", but I am curious
as to the reason.

You're seeing the results of imprecision in floating point math. Just as
1/3 cannot be exactly represented in decimals with a finite number of
digits, so most fractions can't be exactly represented in binary.

XL (and every other commercial spreadsheet that I know of) uses IEEE
double precision floating point math, which employs various techniques
to limit the error due to imprecise representation. XL can display 15
decimal digits, but keeps about 1 more decimal digit in internal
calculations to try to reduce rounding errors.

What's happening here is that when latitude1 and latitude2 = 33.14, the
result of the calculation inside the parens in

ACOS(...)

is just slightly greater than 1.00000000000000, which returns the #NUM!
error (the magnitude of the discrepancy is is hard to tell, since XL
won't display the 16th and following digits as anything other than 0s).

You can force the value to 1 exactly in several ways. One way is to use
the ROUND() function. Rounding the result of the functions inside the
ACOS() function to 15 (or even 20 - what matters is that the ROUND()
function is applied) digits truncates that extra bit, so you could
modify your formula to

=DEGREES(ACOS(ROUND(SIN(RADIANS(latitude1)) * SIN(RADIANS(latitude2)) +
COS(RADIANS(latitude1)) * COS(RADIANS(latitude2)) *
COS(RADIANS(longitude1 - longitude2)), 20))) * 69

While I didn't play with it, it's conceivable that reording the terms of
the formula may also work, since the intermediate results will be
different.

For a more complete explanation of XL's rounding and precision, see

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

 

[Steve]

You're seeing the results of imprecision in floating point math. Just as
1/3 cannot be exactly represented in decimals with a finite number of
digits, so most fractions can't be exactly represented in binary.

XL (and every other commercial spreadsheet that I know of) uses IEEE
double precision floating point math, which employs various techniques
to limit the error due to imprecise representation. XL can display 15
decimal digits, but keeps about 1 more decimal digit in internal
calculations to try to reduce rounding errors.

What's happening here is that when latitude1 and latitude2 = 33.14, the
result of the calculation inside the parens in

ACOS(...)

is just slightly greater than 1.00000000000000, which returns the #NUM!
error (the magnitude of the discrepancy is is hard to tell, since XL
won't display the 16th and following digits as anything other than 0s).

You can force the value to 1 exactly in several ways. One way is to use
the ROUND() function. Rounding the result of the functions inside the
ACOS() function to 15 (or even 20 - what matters is that the ROUND()
function is applied) digits truncates that extra bit, so you could
modify your formula to

=DEGREES(ACOS(ROUND(SIN(RADIANS(latitude1)) * SIN(RADIANS(latitude2)) +
COS(RADIANS(latitude1)) * COS(RADIANS(latitude2)) *
COS(RADIANS(longitude1 - longitude2)), 20))) * 69

While I didn't play with it, it's conceivable that reording the terms of
the formula may also work, since the intermediate results will be
different.

For a more complete explanation of XL's rounding and precision, see

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

Many thanks!