• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Lat Lon Excel Formula

lomer

New Member
Hi guys,

I need some help with a formula that calculate the distance from point A to different points B C D E F G using lat and lon and show the closest one.

Please find an example attached.

Thank you very much for your help.
 

Attachments

  • Lat and Lon Formula.xls
    96 KB · Views: 9
Hi guys,

I need some help with a formula that calculate the distance from point A to different points B C D E F G using lat and lon and show the closest one.

Please find an example attached.

Thank you very much for your help.
Hi,

Using a helper column to calculate the distances between A and the other points, you can then use INDEX/MATCH to return the closest one.

Please see attached.

Hope it helps
 

Attachments

  • Lat and Lon Formula.xls
    97.5 KB · Views: 13
I have your solution already... I did this dashboard for a friend. It is for fund raising (All of the data is fake). You can select a name on the left and it will show you in order by distance all other gifters in area, the closest airports, etc...

https://www.dropbox.com/s/5tw01b9yi8ylb8x/Track_Travel3.xlsx?dl=0


Lon_Lat.jpg
 
Thank you very much Paul for your dashboard.

The main problem in my case is the fact that the excel formula doesn't take in consideration the road (mountains, etc.) only a straight line from point A to point B.
To understand better my situation:
I have different products in different sites around Europe. I need to calculate which product is over utilized and which isn't based on some utilization formulas that I've created, after that to swap the product that are in the same country(swap the under utilized product from 1 client(closest) with the product that is over utilized in another site/client).
I came up with this idea to calculate the distance between the postal code using the lat and lon but i'm not sure how accurate can be.

But i will take a look at your dashboard to see how you made the calculation.

God bless you!
 
Last edited:
Lomer,

You are correct... I dashboard uses long/lat based on a zip code lookup and calculated straight distances... I'm assuming you will need to use a google maps API and some programming to use road distance but I'm sure there might be other options... I hope what I shared will help you in some way.... If not I wish you the best in finding a solution...

Respectfully,
Paul F
 
Back
Top