-
Member
Reputation: 68
Distance Based Neighbour Audit
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?
-
2011-11-29 04:12 PM
# ADS
Circuit advertisement
-
Senior Member
Reputation: 302
1 out of 1 members found this post helpful.
Re: Distance Based Neighbour Audit
Originally Posted by
white_Noise
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?
Hello,, try this one.. just input the longtitude and laitude bro... DONT FORGET TO ADD REPUTATION.. cheers..!! http://www.mediafire.com/download.php?35rvjo58pqxf9e1
Last edited by ninoy_122184; 2011-11-29 at 09:36 PM
password is onepiece
hit THANKS and REP is highly appreciated..cheers!!
-
Post Thanks - 2 Thanks
-
Senior Member
Reputation: 307
Re: Distance Based Neighbour Audit
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
-
Post Thanks - 2 Thanks
-
Member
Reputation: 96
Re: Distance Based Neighbour Audit
Originally Posted by
white_Noise
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).
-
Member
Reputation: 68
Re: Distance Based Neighbour Audit
Originally Posted by
ninoy_122184
thanks buddy, works perfectly..
-
Junior Member
Reputation: 10
Re: Distance Based Neighbour Audit
-
Senior Member
Reputation: 302
Re: Distance Based Neighbour Audit
Originally Posted by
abbas nameer
password please !
password is onepiece
DONT FORGET TO CLICK THE REPUTATION IF THE DOCUMENT IS USEFUL BUDDY..
password is onepiece
hit THANKS and REP is highly appreciated..cheers!!
-
Member
Reputation: 19
Re: Distance Based Neighbour Audit
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
Bookmarks