Need some plotting help - determining which points are farthest apart

[memilanuk]

Hello,

I am working on a spreadsheet to simulate some measurements to see
which ones work the best, how many samples should be taken, etc. Very
early on in this endeavor, so bear with me please

I have two columns, a13:a112 and b13:b112 with labels of 'Xi' and 'Yi'
respectively. The cells in each column contain a formula '=norminv
(rand(),0,1)' to generate random X & Y values with a mean of '0' and a
SD of 1, with the intent of creating a bivariate normally distributed
plot. So far this part seems to be working pretty well. I copied
that formula into both columns, and copied down 100 rows so I should
have 100 (x,y) coordinates that are randomly generated every time the
spreadsheet recalculates.

From that, I'm trying to calculate several different values - mean
radius (MR), radial standard deviation (RSD), and center-to-center
distance (D). Of the three, the c-t-c measurement *seemed* like it'd
be the simplest - it surely is the easiest one to measure in 'real
life'; simply measure the distance between the two furthest apart
points on the plot (easily identified visually), and voila, you have c-
t-c. Doing that in terms of a formula in Excel has me stumped for the
time being. No real problems with the MR & RSD calcs, so this is
doubly frustrating.

What seems to be tripping me up is determining which points are the
ones furthest apart. The actual calculation once they are identified
is fairly simple. The problem is that for one iteration the two
furthest apart points could be xmax and xmin, then the next time they
could be ymax and ymin, and the next time they could be somewhere else
entirely on a diagonal. Any hints as how to reliably determine the
two points that are furthest apart would be very much appreciated.

Thanks,

Monte

 

[Herbert Seidenberg]

Excel 2007
Max distance between x, y points:
http://www.mediafire.com/file/1nxka0k5kyb/05_22_09.xlsx

 

[Herbert Seidenberg]

Excel 2007
Max distance between x, y points:
http://www.mediafire.com/file/1nxka0k5kyb/05_22_09.xlsx

 

[Herbert Seidenberg]

Smartin:
Since we want combinations, not permutations,
half of the array has been set to zero with the term
(c_1 R>c_2 C) (using R1C1 ref designations)
The diagonal is zero by definition.
I am using features of Excel 2007 that do not easily
translate into Excel 2003.

 

[Herbert Seidenberg]

Smartin:
Since we want combinations, not permutations,
half of the array has been set to zero with the term
(c_1 R>c_2 C) (using R1C1 ref designations)
The diagonal is zero by definition.
I am using features of Excel 2007 that do not easily
translate into Excel 2003.

 

[Herbert Seidenberg]

Smartin:
I prefer R1C1 because
1. All the formulas in the array appear the same.
2. The formulas are shorter.
3. That's what MultiPlan used.

Check the A1 Option and
check out the mess.

 

[Herbert Seidenberg]

Smartin:
I prefer R1C1 because
1. All the formulas in the array appear the same.
2. The formulas are shorter.
3. That's what MultiPlan used.

Check the A1 Option and
check out the mess.