Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,856
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,180
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Can excel calculate Mahalanobis distance? kt_b1 Excel Discussion (Misc queries) 3 March 24th 07 12:38 PM
Minimum Distance Calculation using Array and Geographical Coordinates geobatman Excel Discussion (Misc queries) 1 June 9th 06 10:49 PM
How to calculate speed given distance and mm:ss time format? rrl Excel Discussion (Misc queries) 1 October 21st 05 05:39 PM
Calculate Minimum numbers Pete Excel Worksheet Functions 2 May 12th 05 04:31 PM
CALCULATE DISTANCE BETWEEN TWO GPS POSITIONS Jon re Excel Excel Worksheet Functions 2 March 9th 05 03:14 AM


All times are GMT +1. The time now is 10:39 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"