Formula To Find Distance Across Points On A Grid
Nov 22, 2008
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!
View 9 Replies
ADVERTISEMENT
Jul 5, 2011
Is it possible to find the minimum distance between two points using two separate lists of different lengths without creating separate columns of equal row sizes
? I am trying to find the shortest distance form one point to another which is a fixed value. I am using the standard distance formula: =SQRT(((X2-X1)^2)+((Y2-Y1)^2)+((Z2-Z1)^2)).
My X1,Y1, Z1, has 2,000 rows in three columns that need to be compared to find the minimum distance from my one row/column of X2,Y2,Z2.
Is there a command that could go through and use each row of the X1, Y1, Z1, while looking for the MIN of the above formula without having to create an equal amount of rows for the X2, Y2, Z2?
I know I could do it by running this out the long way and then finding the min after I do all the math but I am wondering if there is an automated way for Excel to go through and do the forumla and then only return the MIN value?
View 8 Replies
View Related
Mar 14, 2009
Need a direction for this type of calculation.
View 2 Replies
View Related
Jul 24, 2009
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.
View 14 Replies
View Related
Nov 18, 2008
I have a worksheet ('Matrix') that has a grid that is 40 rows x 40 columns. On another sheet ('Drivers') I have a two columned table where a user is enters two numbers between (you guessed it!) 1 and 40. What I'm trying to do is within the grid, have a "Y" appear on the crossing point as denoted by the table. I've tried a couple of experiments with MATCH, but I'm not getting very far.
I've attached an example document to make this a bit clearer and I've coloured the references on the 'Drivers' sheet to correspond to where they should appear on the 'Matrix' sheet. Sorry if that's a bit garbled. Let me know if you need any clarification.
View 4 Replies
View Related
Jul 21, 2014
I have a list of 3D landmarks, and I'm trying to calculate angles and distances between specific combinations of these points.I hit a complete wall when trying to use excel to calculate out those equations. way input these equations?
Distance equation --> distance between points A and B = sqr root of [(Ax-Bx)^2 + (Ay-By)^2 + (Az-Bz)^2]
This person (see the link) posted the excel equation, and I spent a long time trying to follow it, but can't make sense of it! [URL]
I have attached a summary of the excel I have, and exactly what I'm trying to calculate.
Summary:
1. What is the excel equation to calculate the distance between two 3D coordinates (x,y,z)?
2. What is the excel equation to calculate the angle formed by 3 3D points (again, with x,y,z coordinates)?
View 5 Replies
View Related
Apr 2, 2009
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.
View 5 Replies
View Related
Oct 24, 2013
I created a table where based in several competences anyone can evaluate performance and potential. My issue comes when based on that result (colum Y in "Perf&Pot" Sheet) I want to export that to a nine box matrix (Sheet 2 "grid" in the attached excel). As could be several people in the same box I wasn't able to com up with a formula that solves this.
Pasta1.xlsx
View 1 Replies
View Related
Feb 6, 2014
Is it possible to find the centre point of these 6 figure National Grid References (sample)
345723842214
346094842080
346087842611
346359842379
346502841967
These are point on a map - these points will vary and the number of point will vary too,
View 8 Replies
View Related
Jul 7, 2009
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.
View 4 Replies
View Related
Oct 8, 2013
I would like to search the last end points from my 2 columns and also have all the points it was taken until the end points.
It should start from column B row 1 then search the cell data to column C then back and forth until find the last unavailable points in column A.
Attached here is my sample excel data which have the expected output in column D and E.
View 3 Replies
View Related
Dec 2, 2008
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!
View 7 Replies
View Related
Nov 8, 2011
I'm looking to find a accurate average based on some data points. The issue is that some service times are 0 and so are considerably larger than the norm. So...
17
18
12
13
14
12
10
5
5
0
0
Assuming the following data 12,13,14,12,10 are probably the most accurate and all other are not.
View 7 Replies
View Related
Mar 5, 2014
I'm wondering if it's possible to write a formala that if a certain condition is met, performs a formula referenced in another cell. For example: Column A contains a list of formulas. In column B is the condition, let's use Work Type as an example. The result (or the formula I need) would be in column C. So if the work type is "Type1", then the formula in the corresponding column C cell would perform the formula in cell A5. If the work type is "Type8", then the formula in the corresponding column C cell would perform the formula in cell A2.
Is this possible to do without VBA?
View 6 Replies
View Related
May 2, 2007
Is there a way of amending the following line of code so that it selects multiple data points in a data series in a chart (e.g points 14,15,16)?
ActiveChart.SeriesCollection(2).Points(14).Select
View 2 Replies
View Related
Apr 27, 2013
I am trying to set up a football league table. Iam ok with most of the formulae to arrive at a points total. However when the points total of two or more teams is the same then the final position is governed by goal difference.
View 11 Replies
View Related
Jan 19, 2009
Column P is the percentage of body weight lost...
P
-1.98%
-2.92%
-3.87%
-3.48%
0.00%
-0.10%
0.00%
-3.56%
-3.57%
What I need is a formula to look at all 9 data points and give the place number. Remember that the lowest number is actually in first place.
View 2 Replies
View Related
Aug 2, 2009
I'm creating an (English) football predictions competition for me and my family.
One problem that has stumped me is how to get the scores based on the 'home' & 'away' score predictions.
The rules are: If I predict the correct exact result I get 3 points. I want to add another 'rule' whereby if I predict the correct winner, I get 1 point. Incorrect predictions get 0 points. I don't know how to do this using a formula.
View 11 Replies
View Related
Feb 18, 2014
This is my formula with concatenation: =K3/K1*100 & "c" & " Each"
This is the result of the values in K3 divided by K1: 6.41666666666667c Each
I would like to limit the decimal places to two: 6.41c Each.
I have tried to format the cell and none of the formatting options have any effect.
If possible I would like to use the rounding function but I do not know how to combine the formula + text + rounding.
View 9 Replies
View Related
Dec 8, 2013
I have created a sheet that shows distance between coordinates. Also working on something to put them in order of the closest point. I used a fixed point to find the nearest coordinate, but since i'm using a fixed point it doesnt go the order I want it to. I have attached the excel file I am working on.
View 11 Replies
View Related
Feb 26, 2009
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.
View 14 Replies
View Related
Feb 24, 2007
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.
View 9 Replies
View Related
May 5, 2008
I have x coordinates in column 1 with coresponding y coordinates in column 2. From my data of x,y coordinates I want to find points with similar y values. In my data I have defined groups of numbers, i.e. I have a set of numbers with y values around 30 (+-10), then a new group with y values around 60 (+-10), and so on... Sometimes the groups are not totaly separeted, there could be a few points with y values between 30 and 60. These points can be grouped with the closest group of coordinates (30 or 60). Each group of x,y coordinates could be copypasted in the columns to the right (columns 3 and up).
So what I want to do is find a group of coordinates. This group will have at least 40 individual points +-10 from a group mean. The coordinates need to be sorted out from the data and put in seperate columns.
View 9 Replies
View Related
Mar 27, 2014
I need a formula, from which I need to drag (if needed) and to fill with numbers, by following principles:
- when the left cell contain the zero, it needs to find on the same column if there exists another zero, or another one and count them, let's say if find three zeros untill find number one, return 3.
- when find a number 1, after another 1, has to return a zero.
- the formula need to initialise from the top of the column, only finds the first number 1.
I have a relevant example down bellow. I used to count them visually and sometimes errors sneak into my spreadsheets.
View 5 Replies
View Related
Jan 27, 2009
I am looking to calculate the distance around an arc, given A-B = 70 and the depth is 70cm.
View 10 Replies
View Related
Jul 7, 2009
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.
View 2 Replies
View Related
Jan 20, 2013
I am wondering if there has been something made that is able to measure the distance between 2 cells if there is a barrier between them. For example, say you want to measure the distance between A1 and C1. If there were no barriers, the answer would be 2 cells, but say there was a barrier (a wall) that ran from B1 to B3. The distance would now be 8 assuming you cannot move diagonally, or 6 if you can move diagonally.
View 6 Replies
View Related
Mar 14, 2014
I have two columns of from and to addresses in excel.These are complete addresses with apartment name and pincodes. how can I obtain the distance in km between the two addresses (Both are in the same city) I have used the code in the following [URL].However it seems to give me 0.
View 10 Replies
View Related
Sep 21, 2013
I wrote a code to make all my charts and it made but all charts stocked together
P.S : "The red part in this code is my try to make a distance(space) between charts but it doesn't work"
Code:
For u = 1 To o
ActiveSheet.Shapes.AddChart2(303, xlColumnStacked).Select
ActiveChart.SetSourceData Source:=Sheets("sheet1").Range(crgf(u), crgl(u)), PlotBy:=xlRows
ActiveChart.SetElement (msoElementDataTableWithLegendKeys)[code].....
View 3 Replies
View Related
Jan 16, 2008
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.
View 9 Replies
View Related
