RAND() Volatility

F

fargazmo

I want to use Excel's RAND() function to calculate random points within
a circle. To do this, I want first to just calculate a random point on
the y-axis, less than the radius's value away from the center. That's
no problem.

But then I want to use those values generated in a second column, to
generate the correspondent random values in the x-axis. For instance,
the farther the y-value is from the center of the circle, the smaller I
can let the x-value be. The x-value becomes dependent on the y-value.

But every time I put in the calculation, the y-values change (based on
the volatility of the RAND() function), and the x-values are calculated
using the old y-values, and this creates several invalid points (i.e.
points outside of the circle).

Is there a way to get Excel to fix the random values in the first
column after it computes them, and subsequently use them to calculate
the values in the second column?
 
F

fargazmo

An update:

I've found out how to fix single cells by pressing F9 in the formula
bar. But this is insanely tedious when I've got 1000 values to fix.
Is there any way to fix the column en masse?
 
T

Tom Ogilvy

Say for a unit circle, for my x Value I would use

=RAND()*2-1
assume this is in C1

Next to it (D1), for my y value I would use
=RAND()*(((1-C1^2)^0.5)*2)-((1-C1^2)^0.5)

then I would select C1 and D1 and drag fill down for as far as I needed
values.

If you want some stability, then go into Tools=>Options and in the
calculation tab, set calculation to manual. When you want to recalculate,
hit F9.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top