Distance between zip codes

[Guest]

I need help constructing a formula to determine the distance between 2 zip
codes (actually the demical Lat & Lon). The zip code information was
downloaded from http://data.geocomm.com/editorspicks/data_censuszips.html

I found the formula on this site: http://www.dbazine.com/weeg9.shtml

Column D: Decimal Longitude
Column E: Decmimal Latitude
Column F: DLON
Column G: DLAT
Column H: A
Column I: C
Column J: D


My excel equivlent formulas are:
DLON = =ABS(D3-D2)
DLAT = =ABS(E3-E2)
A = SIN(G2/2)^2+COS(E2)*COS(E3)*SIN(F2/2)^2
C = 2*ASIN(MIN(1,SQRT(H2)))
D = 0.017453293*I2

But result is not correct. Anybody know what I did wrong?

TIA
Mike

 

[Guest]

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

Forgot about the greatest excel help site on the web. Thanks Chip!

 

[Ron Rosenfeld]

I need help constructing a formula to determine the distance between 2 zip
codes (actually the demical Lat & Lon). The zip code information was
downloaded from http://data.geocomm.com/editorspicks/data_censuszips.html

I found the formula on this site: http://www.dbazine.com/weeg9.shtml

Column D: Decimal Longitude
Column E: Decmimal Latitude
Column F: DLON
Column G: DLAT
Column H: A
Column I: C
Column J: D


My excel equivlent formulas are:
DLON = =ABS(D3-D2)
DLAT = =ABS(E3-E2)
A = SIN(G2/2)^2+COS(E2)*COS(E3)*SIN(F2/2)^2
C = 2*ASIN(MIN(1,SQRT(H2)))
D = 0.017453293*I2

But result is not correct. Anybody know what I did wrong?

TIA
Mike

Mike,

Excel's trig functions expect the angles to be in radians; you are inputting
them in degrees and not converting.

So for each reference to a lat/long, encase it in a RADIANS argument.

e.g. DLON = ABS(RADIANS(D3) - RADIANS (D2))



--ron