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

 

[smartin]

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

I opened this with E2003.

Am I correct that the orange area is supposed to be the distance
calculated between points? If so, it doesn't make sense as there are too
many zeros. Is something lost in the translation?

 

[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.

 

[smartin]

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.

Ah! Makes sense. Thanks for the explanation.

If I could engage you a little further, is the use of complex numbers a
convenience, or could this have been accomplished just as well using the
"usual" way of computing the distance between points

=sqrt((y2-y2)^2+(x2-x1)^2)

....Never mind, I think I answered that one myself.

Ok, what's confusing me is the R1C1 syntax. Any pointers?

 

[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.