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
vBulletin® v4.2.5, Copyright ©2000-2024, Jelsoft Enterprises Ltd.