Excel VLOOKUP to Calculate Distances using Latitude & Longitude

  Рет қаралды 69,275

LearnAccessByCrystal

LearnAccessByCrystal

Күн бұрын

Пікірлер: 28
@akp6811
@akp6811 3 ай бұрын
Thank you so much. This was so helpful
@LearnAccessByCrystal
@LearnAccessByCrystal 3 ай бұрын
you're welcome and thank you!
@excelisfun
@excelisfun 12 жыл бұрын
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
@LearnAccessByCrystal 12 жыл бұрын
"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
@LearnAccessByCrystal 12 жыл бұрын
"Totally amazing video, Crystal!!!" -- thank you, Mike! ~ Warm Regards, Crystal * have an awesome day!
@LearnAccessByCrystal
@LearnAccessByCrystal 12 жыл бұрын
I am glad you like this video on using Excel to calculate distances, Mohamed, thanks for your comment ~Crystal * have an awesome day :)
@chrismalingshu
@chrismalingshu 2 жыл бұрын
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
@LearnAccessByCrystal 2 жыл бұрын
thank you, Teoh
@leonoramaya1157
@leonoramaya1157 4 жыл бұрын
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
@LearnAccessByCrystal 3 жыл бұрын
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
@Nempino 4 жыл бұрын
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
@LearnAccessByCrystal 4 жыл бұрын
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
@yzzil106 3 жыл бұрын
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
@irinatenis406 2 жыл бұрын
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
@LearnAccessByCrystal 2 жыл бұрын
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
@irinatenis406 2 жыл бұрын
@@LearnAccessByCrystal Thank you! It was very helpful!
@LearnAccessByCrystal
@LearnAccessByCrystal Жыл бұрын
@@irinatenis406 you're welcome and thank you
@burndjburn
@burndjburn 8 жыл бұрын
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
@LearnAccessByCrystal 8 жыл бұрын
you are correct ~ thanks for commenting
@happykhan299
@happykhan299 4 жыл бұрын
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
@mohamedanwar2240 12 жыл бұрын
Great
@entertain_all2250
@entertain_all2250 5 жыл бұрын
How can i calculate google distance between two using latitude and longitude
@saurabhpathak4162
@saurabhpathak4162 4 жыл бұрын
I need to find the actual that is road distance between two places..can anyone help
@gobinathang8816
@gobinathang8816 7 жыл бұрын
how to calculate area with the co-ordinates.
@LearnAccessByCrystal
@LearnAccessByCrystal 7 жыл бұрын
hi Gobinathan ... the area of what? The rectangle between the coordinates? for what purpose?
@raminahadi4240
@raminahadi4240 5 жыл бұрын
Is this excel file is available to download?
@LearnAccessByCrystal
@LearnAccessByCrystal 5 жыл бұрын
sorry it was not posted at the time. I do private tutoring and can teach you how if you need help
How to Use VLOOKUP to Compare Two Lists
15:20
Simon Sez IT
Рет қаралды 849 М.
How to calculate Driving Distance Matrix on Excel using Bing Maps API
11:46
Smart Sigma Kid #funny #sigma
00:14
CRAZY GREAPA
Рет қаралды 87 МЛН
Osman Kalyoncu Sonu Üzücü Saddest Videos Dream Engine 275 #shorts
00:29
Я сделала самое маленькое в мире мороженое!
00:43
Auto Populate Latitude and Longitude Function in Excel (NEW VERSION)
6:44
Adventures in CRE
Рет қаралды 134 М.
How to Extract Data from a Spreadsheet using VLOOKUP, MATCH and INDEX
15:54
Tuts+ Computer Skills
Рет қаралды 5 МЛН
Use VLOOKUP to Lookup a Value to the Left
8:44
Doug H
Рет қаралды 62 М.
How to get Latitude and Longitude of Multiple Addresses
5:14
Botsheets
Рет қаралды 127 М.
Tableau Calculations : Distance Between 2 Points
4:59
Andy Kriebel
Рет қаралды 14 М.
Discover What XLOOKUP Can Do For YOU (R.I.P. Excel VLOOKUP)
6:59
Leila Gharani
Рет қаралды 1,4 МЛН
Vlookup in MS Excel to Calculate & Distance using Latitude & longitude
15:16
Online Training Tutorials
Рет қаралды 624
Smart Sigma Kid #funny #sigma
00:14
CRAZY GREAPA
Рет қаралды 87 МЛН