Tracker
HOME    TRACKER    Excel

# Calculating Distance Between Coordinates

## i use a Trimble Total Station Survey equipment, This is used for suveying and measuring distances between points on jobs. These points can be exported onto the pc as a text file and imported onto excel, the file has a northing and easting to locate the point these are comma separated. I would like to know if excell is capable of working out a distance betwen two of these such points by using the two pairs of northing and easting coordinates. The maximum distance between two points is no more than 50 meters. If for example when imported onto excel point 1's coordinates are locted in cells A1 and B1 and points 2's coordinates are locted in cells A2 and B2.

Related Forum Messages:
Calculate Shortest Distance From Coordinates
I want to use a User Defined name look up its corresponding X/Y Coordinates , reference a list organized by categories, then go through each name in the list finding each's X/Y Coord Calculate distance.

SQRT((X-X)*(X-X)+(Y-Y)*(Y-Y))

where X and Y are Coordinates

& Return the name of the smallest Value.

Minimum Distance Calculation Of Geographic Coordinates Using Array
I am working on two spreadsheets, one we shall call Target Sheet and the other Reference Sheet.

On the target sheet, I have a set of 200 geographic coordinates longitude and latitude (in decimal degrees). Each pair of coordinates coresponds to a geographic site.

On the reference sheet, I have a set of 900 geographic coordinates longitude and latitude (in decimal degrees). Each pair of coordinates coresponds to a geographic site. Note that of the 900, 200 are the same from target sheet, other 700 are different sites.

Using the spherical law of cosines, I can calculate the distance between a set of coordinates on target sheet agianst a set of coordinates on reference sheet. This allows me to see the distance from one geographic site, to the other. This was easy. The formula I use is as follows: ....

Calculating The Distance Round An Arc
I am looking to calculate the distance around an arc, given A-B = 70 and the depth is 70cm.

Calculating Miles, Distance & MPH
My wife wanted a excel sheet to track her walking. See Attached. I think I have the time calculations wrong, for example I am entering 1.07 for an hour and 7 minutes.

Numbering For Coordinates
how to get a single cell (C2) and (D2) to make the numbering format go from (## ## ##) to (######).

The Excel spread sheet is a coordinate converter, designed to take Degree's minuets seconds and convert it to Decimal Degrees, the formula is set up and work Great, but every time I copy and paste the coordinate to the excel spread sheet, i have to manuelly erase the spaces between the numbers so the formula can work properly. How can i get the cell to automatically delete the space between the numbers to save me time.(I.e 29 35 42.34325 -to-> 293542.34325)

XYZ Coordinates Graph 3D
I have the following data:

X Axis - Years (2004, 2005 ,...)
Y Axis - Quantity (imported, exported, produaced)
Z- Axiz - Name of the products

I want to make a graph with teh X, y,z axis in excel. When i use the 3D graphs in excel, it is not taking the z vales connected to teh cell.

Calculate Angle From X, Y Coordinates
I have a userform, on it is a very large command button with a picture of a map loaded on the button. The button is 500 width(X) by 500 height (Y), so the center of the button would be at the location 250 (X) by 250 (Y). The button is coded for mousemove to get the X,Y cooridates and when you _Click the button a textbox gives the distance to the point(mousemove) from the center of the button. All that is good, but now I'd like to find the degree of the point referenced to a compass, ie, if the point is at 250 (X) and (Y) is less than 250 then the point would be due north at 0/360 degrees and if the point (Y) is greater than 250, it would be south of the center of the button. I hope this make sense. It's been a long time since trig. I've been searching but can't seem to find the correct formula to figure this out.

Here's the other problem, I'm doing this in powerpoint so I'm not sure if cos or tan functions are loaded in this program. I am posting this in the xl forum because I figure that's where most of the people who could figure this out are. If this is in the incorrect forum I can try to move it,

Distance Between Zip Codes
I have two columns, zipA and zipB. There are over 7000 rows and I need to calculate the distance between zipA and zipB for each row (so the result should be over 7000 rows of distances (in miles)). I posted this in the programming board because I figured it would require some. I don't have any add-ons.

Minimum Distance Between Points
I want to know the minimum distance between customers that I visited in each day.

If i visit customer 1, 2 and 5 in one day, which distance is smaller? 1-2, 2-5 ou 1-5 and what is the corresponding value?

I have in columns DATE, CUSTOMER 1, CUSTOMER 2, ..., CUSTOMER 5 and in each row I put the Day and 1 or blank for each customer (visited / not visited). I also have a mileage chart for each customer in another sheet which is the source for my distance data.

I tried INDEX and MATCH but all I get is ZERO... I think it is comparing the same CUSTOMER.

Distance Between 2 Zip Codes
At my last job we purchased a zip code addin from spheresoft that could give you a distance between two distance ie: =ZipCodeDistance(zip1, zip2)

This worked well but I am now at a new job and wondering if there is any data out there that could help me build my own sheet. I just need California

I have both zip codes now just need the distance, I could sit down, drink a ton of coffee and enter zip codes all night into Mapquest but am hoping for a better way.

Distance Between 2 Geo-points
Need a direction for this type of calculation.

Convert Lat/longs Coordinates
I'm trying to create a spreadsheet that will convert coordinates from DDMMSS.00 to DDMM.000. I found another thread that discusses this same issue, but I was unable to make it work for me. I'm attaching a sample spreadsheet that basically shows what I'm trying to do.

Finding The Hypotenuse Of From Two Pairs Of X,Y Coordinates
I have a map with several waypoints and would like to find the distance between any of these two waypoints. Sounds simple, but I have having an issue with the later parts of the excel file. Let me explain further.

There are over 100 waypoints on this map we have. Each waypoint has an, seemingly arbitrary, X-coordinate and Y-Coordinate. However, if you divide the coordinates by [X], they turn into miles. It should go without saying that finding the distance between two waypoints is rather simple mathematically, but I having an issue figuring out how to pull two random waypoints and having the distance automatically calculated.

So far, I just have column A with the waypoint name and with column B and C having the X and Y coordinates respectively. Is there anyway I can create some sort of call function that when two waypoint values are entered it would automatically calculate the distances?

I am trying to extract (and separate into 3 columns) the numbers only, positive or negative portion of (XYZ) Coordinates copied from AutoCAD and pasted into column A
X = 96179.9699 Y = 61973.9793 Z = 1368.0025
In column B,C and D, I have tried to use Find Mid and other means

Count Geographic Coordinates Within A Range
I have 2 columns of longitude and latitude coordinates. I want to count coordinates within certain ranges. A bit like using the Frequency function but for 2 sets of values and 2 bins arrays. This would mean I could create a table with latitute bins as the row headers and longitude bins as the column headings. Would anyone know how to do this?

Calculate Relative Vector Coordinates
I am running a fairly complex simulation at work and have quite a few different functions in it. For the simulation to work properly, i need to have the Solver add-in and the Analysis ToolPak installed. Everytime that i try and use the simulation on a different computer (or if anyone else tries to run it) the add-ins must be enabled again. Is there a way to set up a macro to enable them when either i open the simulation (first preference) or when a button is pressed (2nd preference).

Logic Formula To Calculate Distance
I have a excel sheet with city names in Column A, Distances in Column B, and Distances in Column C.

I wanted to write a formula so excel checks the distance between column C and column B and if there is less than a 10% difference between the two then it would copy the name of that city into another column D.

Lookup & Find The Distance
I have a distance grid with place names down column A and along Row 1 with distances in the middle. I then have a place name in cell A1 on sheet2 and a distance in cell B1 on sheet2. Ideally I want cell C1 on sheet2 to lookup the place in cell A1 on Row1 of the grid on sheet1 and then find the distance in B2 in that column before displaying the correspoding value from Column A on sheet2. I've tried various Match and Index functions with no success.

Distance Matrix For Multivariate Data
I have a multivariate dataset with a large number of cases. eg. 50.000 cases (data points) and 40 variables. I need to calculate the distances between each pair of data points in this 40-dimensional space. Obviously i cannot produce a whole data matrix, which would have to be 50.000 x 50.000 big. but i need at least the average distance - if N is the number of cases there are (N*N-N)/2 possible pairs of points. Idealy, the (N*N-N)/2 distances could be placed in a column so i could make a histogram of the values. Still this doesn't seem like a very good solution as the (N*N-N)/2 distances if N=50.000 would fill about 75 worksheets!

Here is a simplified exaple of what i want to do, but with N=4 and only 3 variables:
original data:
IDv1v2v3
1103010
2202510
3202020
4402015
Distance matrix:
1234
10 11,1803398917,3205080832,01562119
211,18011,1803398921,21320344
317,32 11,18033989020,61552813
432,01 21,2132034420,615528130

Macro To Solve For Cartesian Coordinates On An Ellipse
Does anyone have a macro they have created or know of that can solve the X and Y coordinates on an ellipse? Would you be willing to share here or send it to me in a personal email?

Data Type Of Coordinates Of 2-dimensional Array
I have a big array "DataArray" and want to access it:

For i = 1 To 4
variable = DataArray(SourceArray(i))
Next i

"DataArray" has two dimensions, so SourceArray has to consist of data like this:

SourceArray(1) = 1,2
sourceArray(2) = 2,4
etc

What data type does Sourcearray have to be? Integer doesnt seem to work, and DataArray doesnt like a string as coordinates. I have a workaround with two different arrays of integer for x and y coordinates, but this cannot be it.

Modify Free-form Shape Coordinates
Right now I'm modifying a figure based on size data in an excel table.

I'm modifying the X/Y coordinates of the points of the freeform shape with VBA, however, I have to use absolute x,y coordinates.

I'd like to be able to move the shapes throughout the sheet. With fixed coordinates, this makes it difficult. I'm hoping there's an easy way to it

I'm attempting to write a piece of code that builds a formula dependent on the location of the cell it is being created in. The relevant line of code currently reads as follows:

ActiveCell.Offset(0, 5).Value = "=" & ActiveCell.Offset(0, -5).Address & " & " & ActiveCell.Offset(0, -6).Address

Which when activated in cell A3, will enter the formula =\$A\$3 & \$A\$2

However, I would like to know if there is a way of automatically building a formula like this, only without the absolute references. Reason being, this formula is used in a large table, which is sorted in date order automatically when the sheet is activated. Since the code generated will point at the row it was generated on, when the table is sorted, this formula will calculate the incorrect result.

Formula To Find Distance Across Points On A Grid
Havent used a spreadsheet in ages and i'm having a fun time here. Cant seem to get the right formula for what i need. Here is a brief explanation!

I have a grid (not related to excel) of 999 by 999 squares.
The center is basically co-ords 500|500.
Upper left would of course be co-ords 001|001
Upper right would be 999|001
Bottom left would be 001|999
Bottom right would be 999|999

Now, here is what I am trying to do:

I want to have 3 squares chosen out on a spreadsheet I have made . .

1) here i put in the start co-ords (ie: 500|500)
2) here i put in the destination co-ords (ie: 505|510)
3) This is the fun one!!! ... And where I need the help!

I need a formula to put here that will tell me the number of squares I have to cross to get from one to the other!

Select Numbers After Every 3 Distance From The Target Number
Below shows a example table:
*ABCDEFGHIJKLMNOPQRSTUVWXY11458912131415192122232728293234363741444548492314

The table above has numbers from A1:Y1. The blue number is the target number, which is selected in cell H3. I want selected numbers from the target number H1 going along each 3 numbers on the right to get the number 22 which is after the 3rd distance. For example the number would be 22, 29, 37 and 48, which are all in red. After the number 48 it should start counting from the beginning and reach to the target point H1. In this way it will select 5 and 13 also. In total it would be 6 numbers selected. The target point can be selected from A1:Y1 but in this example case is H1.

The table below shows the final 6 numbers, which are obtained after the results along each 3rd distance numbers.

Scheduling Technicians Based On Distance To Customer
I need to dispatch 10 technicians across the US starting each Monday. They go to 10 different customers for the whole week. I know the distance between each technician and each customer. What I would like to do is assign each technician to a customer so that the total distance traveled for all the technicians is minimized. I know that if it was only 3 techs and 3 customers (as an example), I could build a table of all the possible combinations (3 factorial or 6 permutations), determine the distance for each permutation and select the minimum. I'm not sure that doing this method with 10 techs is feasible as the number of permutations is 10 factorial or over 3 million possibilities. Anyone see a workable solution?

I've include a spreadsheet with customers, technicians and the distances between them!

Check Distance Difference Based On Criteria
I have a Point A to Point B with the distance.

1. user can set a adjustable distance in cell J1. e.g 2000

2. User select Point A.

3. User select Point B

4. Then list out all the Point C where distance diff between Point A and Point B all within 2000.

Getting Max Value Of A Point Inside A Square Defined With X,y Coordinates
I have a square which is defined with x,y coordinates (roughly 2000 points), and for each point I have a value. And I have to divide this square in to 9 equal sub grids, and find the max value in each sub grid. I have all the coordinates for the sub grids. But I can’t get the max value of the specified cells.

I am thinking something like this:

if 0<x>10 and 0<y<10 then it is sub grid 1 and I want to find the max value there
and if 0<x>10 and 10<y<20 then it is sub grid 2 and so one…..

Button Position/Coordinates On Worksheet. Discrepancy Between Versions
Add Multiple Buttons That Select Row Of Housing Cell. One of my Macro's places a button on a sheet using Top/Left;

Range("J" & count).Left, Range("J" & count).Top, 50, 12)

When I try to find the row of the clicked button using the following code;

The code behaves differently in Excel 2002 and 2007. In 2002 it returns the row the button sits on, but 2007 returns the cell above the button. This could be because the workbook is an XLS running in Excel 2007 in compatibility mode? Is there a way to "nudge" the button down a little after it has been placed using .top/.left ? Or is the only way around this something like;

count = ActiveCell.Row
If Application.Version >= 12 Then
count = count + 1
End If

Is this a bug, or something that I am doing wrong ? The workbook needs to be a .xls to ensure that it works in older versions of Excel.

Add Shape & Textbox Values For Coordinates
i am trying to enter the EndX coordinates (The third number: 500) by entering a number in a forms textbox

how can i break the code up to enter the coordinates via textbox's

Convert The Distance Jumped By A Person To A Specific Rank
I am making an excel file that should convert the distance jumped by a person to a specific rank. For example if the distance was greater or equal to 34, he would get a 10. and 34> distance >= 32, he would get a 9 and so on...

I need a formula that will should nest more than 7 functions, in this case it wont work right? So what I did was name 2 separate fuctions for example

V Lookup Or Hlook Up: Look Up The Cell Reference For Distance Between Two Customers
I have attached a spreadsheet with a small amount of the data I need to use. This is the distance between customers in kms. I need to be able to have excel look up the cell reference for distance between two customers though they will not always be the same customers every day in the same order.

Formula To Calculate Speed Knowing Distance, Destination Time And Checkpoint Times
On my spreadsheet i have a bunch of variables:

A1 = Arrival time at Checkpoint 1 - 5:53:08
A2 = Time to destination from Checkpoint 1 - 1:10:18

B1 = Arrival time at Checkpoint 2 - 6:00:56
B2 = Time to destination from Checkpoint - 1:02:30

C1 = Total distance from Start - 2.83
C2 = Arrival Time at Destination - =SUM(A1,A2)

D1 = Speed determined from the other variables
D1 is SUPPOSED to equal 25.00

It also is where I am stumped!!

Not only can I not figure out the math but also how to format the function to get the right answer.

ps: Arrival Time at Destination is only included in case it would be useful to find the answer!

Calculating % GP Anyone
im running a new bar in Leeds and im trying to create a spreadsheet which will tell me how much to charge per drink to gain a 80% GP on products, ive got as far as listing the products and prices and calculating how much every measure (25ml) will cost. In the past ive used formulas where I can input the price i charge and it will calculate the GP (=SUM(D10/E10)*100-100) for example but id like to be able to type in the GP I want and for it to tell me the price i need, im not sure if tis possible but it will be a massive time saver.

Calculating Months
I input a month as a start date, and then want the next x number of cells to increment by one month based on the start date. I thought this would be easy using the preset DATE function but I can't get the thing to do what I want.

Calculating Using Time
Firstly, i'd like cell G5 to show the amount of time worked i.e diff between E6 & F6. Secondly I'd like help with the formula for cells AF6 & AG6 which would require AB6-AC6 divided by the frames, every time I try it I get a messed up answer.

Calculating TAT
I have some activities that needs to be done ina specified time

Like for example

TAT HRShrsACT 14ACT 28ACT 324ACT 42ACT 54

And i have a sheet where in i need to calculate turn around time

ActivityStart timeEnd timeTATACT 1ACT 2ACT 3ACT 4ACT 5

Based on time entered in start time and end time the TAT column should calculate the TAT based on the above standards.

I also would liuke to exclude weekends(Saturday, sunday). Also the TAT should only be calculated during working hrs between 7:30 PM to 4:30 AM.

Calculating Cells ..
I have the workbook set to Auto Calc the Cells, which seems to be a bad idea, because with the amount of formula in the workbook, it slows the whole thing down and makes it impossible to do anything within it.

Is there a way that I can speed this process up, using some code when I run certain sections?

There is a copy of what I am doing, so you can see the amount of formula.

Calculating Cost Per Second
I'm trying to make a worksheet where I can calculate the cost of a mobile postpaid subscription. It is charged per minute and the cost differs depending on which of the 2 available networks the customer is calling to. The first 20 minutes are free, not depending on network.

Edit:
Charges to network A is 1,79,- per minute after the first 20 minutes are spent.
Charges to network B is 2,29,- per minute after the first 20 minutes are spent.

To sum up:
1. The customer makes a call.
2. If there there are available free minutes, these should be spent first.
3. The customer is charged per minute, depending on network called.

Calculating Median
I have a list of hotel IDs in one column of my spreadsheet. In the next few columns, I have ratings that customers gave the hotel for cleanliness, location, room, etc.

What I need to do is calculate the median of all the ratings for each specific hotel, in a separate column.

An example of what I have: ...

Calculating Time In
=INDEX({"\$5.00","\$3.00","You Were Late"},MATCH(L17,{7:00am,7:05am,7:06am},1))

i have this formula in excel...this formula sanrv1f posted to help with another question (values changed) but i thought i would work with what i wanted but i get a (Value) error

so what im trying to do is base on the system time is if the person typed in

if they were early 6:45am to 7am they get Extra \$5 on top of the \$5 they get for being on time.

6:55am gets \$5
7:00am they get \$5.00 for being on time
if they type 7:01am to 7:05am they get \$3.00
if they type 7:06 to 7:10am would return You Were Late
after 7:10am would return No Pay

so Ex

7am would get \$5

7:03am am would get \$3

7:07am would get You Were Late

7:11am Would get No Pay

if they were on time 5 times (ie 5days in a row) they would get a bonus of \$20 i tried factoring this in could not do it :/

Calculating The Ballots That Come In
I am on an auto show board and want to come up with a way of calculating the ballots that come in, using excel. Ballot points are accrued as follows: 5 points for 1st place, 3 points for 2nd place, 1 point for 3rd place. With about 25 different categories (classes) and I am estimating participants at around 100.where I can in the automotive field knowing there is nothing besides personal gain.

Calculating The Overtime
We are guaranteed (right now) a 10 hour day. (we are on 4 - 10 hour shifts). So if we work 9 hours say on Monday, we get 9 hours of straight time and an hour of short work week (approx 80% of pay). Now if we work 11 hours on Tuesday (which they can do unfortunately) I get 11 hours of straight time and no overtime. We have to make up for the short work week hour. So a less complicated explanation would be if I dont work more than 40 hours per week, no overtime no matter what I actually worked per day. Seems pretty simple but what I want and need to do is to calculate it per day.

Mon 10 Hours
Tues 11 Hours
Wed 9.5 Hours
Thurs 10 hours

I should get:

Mon 0 overtime
Tues .5 overtime
Wed 0 overtime
Thurs 0 overtime

I got it to the point where If the day where we get short work week is first and overtime after that, it works. But if we work overtime first then get short work week later in the week, it wont calculate it. I know why it wont work now but I dont know how to make it work. LOL Here's a link to the file.

Calculating Days ..
What I would like to do is insert a formula into column E that would calculate the number of days from when the count is at 50 to when the next count is i.e. for part 6689841 the time between when the count was 50 and when it was at 20, so the difference between 21/01/2008 and 08/08/2008 in the above example. It does not matter what the count number is (it could be any number except 50), I'm only concerned with every time the count is at 50 how long it is until the next count and to show the result in days.

Calculating A Formula
i need to creat a 12 month calculation of accounts

month 1 month 2 based on a precentage so i need to calulate the cell 1 pluse a precentage equals cell 2 and then cell 1 plus 2 and the precentage equils that

Copying But Not Calculating
I'm copying a formula and pasting it to a group of cells. It copies correctly, but I DO NOT want it to calculate anything until other input cells are filled in the columns for the rows that I have pasted the formula. I would like it to show "nothing" until other data is inputted.

Cells Not Calculating?
I am having a problem with some of the cells in a spreadsheet not calculating.

For example, in column A, all the cells have formulas that pull data from another sheet. When I enter the formula, only the formula shows (not the result). I can't figure out what is wrong as the formulas in column B work fine (the results are showing, but not using the same data that column A is).

I am thinking it may have something to do with the data being used to calculate the formulas but not sure. How can I resolve this?

Calculating R-squared
I'd like to calculate the r-sqaured of the S&P 500 performance in an election year to see if that has any effect on the market's performance.

The data I have is below. The election years start in 1928 and are every 4 greater than that. How would I set it up to use the rsq forumla?

S&P 500 Index
192612%
192737%
192844%
1929-8%
1930-25%
1931-43%
1932-8%
193354%
1934-1%

Calculating Cost
Problem - billing spreadsheet for prisoner fee.

1 - 8 hrs = \$55
9 - 24 hrs = \$55 + \$65 or \$120
Anything over 24 hrs - \$65 for each additional (24 hrs) (\$185)

So if you were locked up for 6 hrs it is \$55. If you were locked up for 18 hrs it is \$120. If you were locked up for 28 hrs it is \$185. And if you were locked up for 49 hrs it is \$250. Cell F5 contains number of hours locked up - I would like cell I5 to calculate the cost of the stay. I am proud of myself for figuring out the date and time subtraction - but this part has me stumped.

Calculating Between Two Times
I had an excellent response last time I posted here, this time I’m stuck again with a new formula. I’m trying to calculated amounts between different times, but keep tying my self in knots with complicated IF formulas.

Is there an easier way to work out hours worked between 2 times, but too complicated things further I need three separate amounts so I’m guessing I’ll need three separate formulas

Hours between 00:00 – 06:00
Hours between 06:00 – 19:00
& hours between 19:00 – 00:00

An example could be, 05:00 – 20:00 should be 1,13,1