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

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

Herbert Seidenberg wrote:
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?

Smartin:
Since we want combinations, not permutations,
half of the array has been set to zero with the term
(c_1 Rc_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 wrote:
Smartin:
Since we want combinations, not permutations,
half of the array has been set to zero with the term
(c_1 Rc_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?

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.