Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to calculate minimum distance between three coordinate with ex
Hi
I have 1000 data point with x,y coordinate. I am trying to find a point so that this point will give a total minimum distance between all these points. All suggest would be good! Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to calculate minimum distance between three coordinate withex
Presumably you know that given two points (x1,y1) and (x2,y2) you can
use Pythagoras' Theorem to work out the distance between them? So, if you put x1 in A1 and y1 in B1, and x2 in A2 and y2 in B2, then you could have this formula in C2 to calculate the distance: =SQRT((A2-$A$1)^2+(B2-$B$1)^2) Put other values in columns A and B from A3 downwards, and copy the formula down to cover them - each value in column C is the distance between the point co-ordinates on that row and the co-ordinates in the first row. So, you can add these up with a SUM formula (I would suggest that you put it in C1) to give you the total distance between all the points and the point in row 1. Now you could do a bit of trial and error - change the values in A1 and B1 and note the changes in C1. Make a record of them all in columns E, F and G as you vary A1, and then as you vary B1. Perhaps you can spot some trends over the variations? Perhaps you can draw a graph and pull some conclusions from this? Of course, a macro could generate different values for A1 and B1 automatically, and record the results in columns E:G for you. You might also be able to use Solver (with ATP installed) to find the minimum automatically. Depending on the spread of your 1000 data points, there may be several minima, or near-minima. Hope this gives you something to start with. Pete On Nov 28, 11:42 pm, Jonathan wrote: Hi I have 1000 data point with x,y coordinate. I am trying to find a point so that this point will give a total minimum distance between all these points. All suggest would be good! Thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to calculate minimum distance between three coordinate withex
x = list of x coordinates
y = list of y coordinates avgx=AVERAGE(x) avgy=AVERAGE(y) cov=COVAR(x,y) varx=VARP(x) vary=VARP(y) d=2*(varx*vary-cov^2) avgu=AVERAGE(((x-avgx)^2+(y-avgy)^2)*(x-avgx)) (an array formula-CSE) avgv=AVERAGE(((x-avgx)^2+(y-avgy)^2)*(y-avgy)) (an array formula-CSE) cx= avgx+(avgu*vary-avgv*cov)/d (x coordinate of center) cy= avgy+(avgv*varx-avgu*cov)/d (y coordinate of center) Kudos to Greg Egan at group comp.graphics.algorithms |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can excel calculate Mahalanobis distance? | Excel Discussion (Misc queries) | |||
Minimum Distance Calculation using Array and Geographical Coordinates | Excel Discussion (Misc queries) | |||
How to calculate speed given distance and mm:ss time format? | Excel Discussion (Misc queries) | |||
Calculate Minimum numbers | Excel Worksheet Functions | |||
CALCULATE DISTANCE BETWEEN TWO GPS POSITIONS | Excel Worksheet Functions |