PDA

View Full Version : Question Distance Based Neighbour Audit



white_Noise
2011-11-29, 04:12 PM
Dear Experts,

Does anyone have an excel formula to calculate the distance between two cells based on decimal (Long,Lat) points?

I found the below formula but it's not working on excel :

=RadiusEarth*((2*ASIN(SQRT((SIN((RADIANS(C2)-RADIANS(E2))/2)^2)+COS(RADIANS(C2))*COS(RADIANS(E2))*(SIN((RADIANS(D2)-RADIANS(F2))/2)^2)))))

Can you please help?

ninoy_122184
2011-11-29, 04:43 PM
Dear Experts, Does anyone have an excel formula to calculate the distance between two cells based on decimal (Long,Lat) points? I found the below formula but it's not working on excel : Hello,, try this one.. just =RadiusEarth*((2*ASIN(SQRT((SIN((RADIANS(C2)-RADIANS(E2))/2)^2)+COS(RADIANS(C2))*COS(RADIANS(E2))*(SIN((RADIANS(D2)-RADIANS(F2))/2)^2))))) Can you please help? http://www.mediafire.com/imgbnc.php/053ca9dbcca6dd1139f04be6e32e80cdcae804bb0a6dd2bb34711af842a289e86g.jpg (http://www.mediafire.com/imageview.php?thumb%3D6%26quickkey%3Dzyfn93z5r3tjqpf) Hello,, try this one.. just input the longtitude and laitude bro... DONT FORGET TO ADD REPUTATION.. cheers..!! http://www.mediafire.com/download.php?35rvjo58pqxf9e1

D33T0X
2011-11-29, 09:04 PM
I have your solution but it's not Xcel: let's assume that you have the following columns in Xcel: Source, Long_Source, Lat_Source, Target, Long_Target, Lat_Target
Open the Xcel in Mapinfo
Do the following query: Select Source, Destination,Distance(Long_Source, Lat_Source, Long_Target, Lat_Target, "km") from YOUR_XCEL into Query1

The Query tab has now all the distances - just copy them to Xcel.

Hope it solves your problem man

captain_jejemon
2011-11-29, 09:39 PM
Dear Experts,

Does anyone have an excel formula to calculate the distance between two cells based on decimal (Long,Lat) points?

I found the below formula but it's not working on excel :

=RadiusEarth*((2*ASIN(SQRT((SIN((RADIANS(C2)-RADIANS(E2))/2)^2)+COS(RADIANS(C2))*COS(RADIANS(E2))*(SIN((RADIANS(D2)-RADIANS(F2))/2)^2)))))

Can you please help?

you can use this formula:
=ACOS(COS(RADIANS(90-Lat1)) *COS(RADIANS(90-Lat2)) +SIN(RADIANS(90-Lat1)) *SIN(RADIANS(90-Lat2)) *COS(RADIANS(Long1-Long2))) *6371

and my final version that uses Excel cell references is:


=ACOS(COS(RADIANS(90-A2)) *COS(RADIANS(90-A3)) +SIN(RADIANS(90-A2)) *SIN(RADIANS(90-A3)) *COS(RADIANS(B2-B3))) *6371

PS. To calculate distances in miles, substitute R (6371) with 3958.756 (and for nautical miles, use 3440.065).

white_Noise
2011-11-30, 01:43 AM
http://www.mediafire.com/imgbnc.php/053ca9dbcca6dd1139f04be6e32e80cdcae804bb0a6dd2bb34711af842a289e86g.jpg (http://www.mediafire.com/imageview.php?thumb%3D6%26quickkey%3Dzyfn93z5r3tjqpf) Hello,, try this one.. just input the longtitude and laitude bro... DONT FORGET TO ADD REPUTATION.. cheers..!! http://www.mediafire.com/download.php?35rvjo58pqxf9e1

thanks buddy, works perfectly..

abbas nameer
2011-11-30, 05:39 AM
password please !

ninoy_122184
2011-11-30, 08:19 AM
password please !

password is onepiece

DONT FORGET TO CLICK THE REPUTATION IF THE DOCUMENT IS USEFUL BUDDY..:victory:

shankar_eee07
2012-03-31, 07:59 PM
hi if B and C are source cell lat long and D and E are neighbour cell lat long then use below formula

=1.1519*1.609*60*SQRT((B5-D5)^2+((C5-E5)*COS((B5+D5)*PI()/360))^2)

if it is use full add reputation