Excel VLOOKUP to Calculate Distances using Latitude & Longitude

[@akp6811]

Thank you so much. This was so helpful

[@LearnAccessByCrystal]

you're welcome and thank you!

[@excelisfun]

Love the memorizing trick for F4 - Can I appropriate that for the classes that I teach? I use a similar memory trick for keyboard for Currency Number Format: Ctrl + Shift + 4. There are so many different tricks in one video - Like Excel Christmas with so many presents!!

[@LearnAccessByCrystal]

"Love the memorizing trick for F4 " -- thank you, Mike! Feel free to use anything you like! ... I just caught the idea :) "so many different tricks in one video - Like Excel Christmas" -- it is fun too! ,,, I am anxious to open presents from other producers (although I have another video on my mind ... :) ~Crystal

[@LearnAccessByCrystal]

"Totally amazing video, Crystal!!!" -- thank you, Mike! ~ Warm Regards, Crystal * have an awesome day!

[@LearnAccessByCrystal]

I am glad you like this video on using Excel to calculate distances, Mohamed, thanks for your comment ~Crystal * have an awesome day :)

[@chrismalingshu]

Great tutorial. First time exposed to VBA code. End up with customize my own get distance code. I should really take an Excel crash course. Too much to learn.

[@LearnAccessByCrystal]

thank you, Teoh

[@leonoramaya1157]

For the GetDistance code, is that the general VBA code to find distance between long. lat. points? or is that connected to only your database? Thats the only part that confused me, I dont know alot about VBA.

[@LearnAccessByCrystal]

hi Leonor, thanks for commenting. The GetDistance function is written in VBA and you can get it here: msaccessgurus.com/VBA/Code/Fx_GetDistance.htm You send it the coordinates ... Latitude and Longitude for each point, and the distance is calculated using trigonometry and knowing the radius of the Earth. You can use this same code in Excel, Access, and other places where you need to calculate the distance between points. as for VBA ... don't be daunted. Even if you've never written a program before, you can learn. Learn VBA www.accessmvp.com/strive4peace/VBA.htm VBA (Visual Basic for Applications) is easy to learn! Learning the object model for the application you're programming in is what takes a lot of time! The object model is what the objects look like … Excel has Workbooks and within in each are sheets, some of which are worksheets with cells. Access has Database files that contain objects such as tables with fields, relationships between tables, forms and reports with controls, and more. What's in your world? desk, papers, filing cabinets, ... Other than the objects for the application, VBA is the same! VBA isn’t hard to learn and is usually easier to understand than complex formulas and expressions.

[@Nempino]

Great video! So I got the getdistance formula working at first, but then I tried adding a couple of rows to the table, and the formula has been returning a #NAME? error ever since. Any idea what the cause might be? I've checked the syntax, the cell references, and the cell formatting multiple times, but can't seem to figure out what's going on.

[@LearnAccessByCrystal]

thank you, Matt! Make sure the function is in your workbook in a standard module so you can use it anywhere. Make sure your workbook is an XLSM or XLS, so code is allowed, and that macros are enabled. Compile and save. If your cells are in a table, make sure you are using the table way to reference them.

[@yzzil106]

I am also having this problem, and have also double checked everything. I double checked I changed the input to number format but still no luck. Did you end up resolving your problem?

[@irinatenis406]

Is this GetDistance formula for miles? Sorry if it's a dumb question. I see 3 earth radii in the formula. So, the number I get, what is it?

[@LearnAccessByCrystal]

hi Irina, the GetDistance function returns American Miles by default, but if you specify the optional pWhich parameter, you can get the value in other units

[@irinatenis406]

@@LearnAccessByCrystal Thank you! It was very helpful!

[@LearnAccessByCrystal]

@@irinatenis406 you're welcome and thank you

[@burndjburn]

Note: that this is the distance between two point on the globe, not the distance as in a map. Roads are not straight, so if that is what you're looking for, this will not work.

[@LearnAccessByCrystal]

you are correct ~ thanks for commenting

[@happykhan299]

Great..can you please guide on Lamert datum transformation...I am facing this problem...will be great help.. Or any formula in excel.. Points or toposheet is on Lambert, indian zone1 wgs 84 N41.. Thanks

[@mohamedanwar2240]

Great

[@entertain_all2250]

How can i calculate google distance between two using latitude and longitude

[@saurabhpathak4162]

I need to find the actual that is road distance between two places..can anyone help

[@gobinathang8816]

how to calculate area with the co-ordinates.

[@LearnAccessByCrystal]

hi Gobinathan ... the area of what? The rectangle between the coordinates? for what purpose?

[@raminahadi4240]

Is this excel file is available to download?

[@LearnAccessByCrystal]

sorry it was not posted at the time. I do private tutoring and can teach you how if you need help