Harold A Climer said:
I have a set of X,Y data points X1 to X20 and Y1 to Y20.
I would either like to insert a random number between
certain limits to represent a Z value, using
RANDBETWEEN(a,b) into each point individually or be able
to insert a random number using RANDBETWEEN(a,b) into
each possible Y value that corresponds to the same X
value.
Is this possible in Excel 2007 in any other way other
than doing it manually into each X,Y data point?
Do you still help with this?
The reason why you have not received any responses is probably because your
question is unclear. It would help to have a concrete example; for
examples, the values in X1:X20 and Y1:Y20, and what they represent and how
they are related.
Usually when we speak of "a Z value", we are talking about a normal
distribution. Is that what you mean?
In that case, usually we have the original data ("observations"; "samples");
or we have a frequency table or histogram of grouped data. Is that what
X1:X20 and Y1:Y20 represent: either 40 samples, or 20 "buckets" whose
limits are defined by X1:X20 and whose frequencies are in Y1:Y20?
Or is Y1:Y20 the probabilities (PDF results) of discrete data (X1:X20) from
a normal distribution?
In any case, do you also have the average and standard deviation of either
the sample or the population?
If the average is in A1 and the standard deviation is in A2, we can derive
the X for a random Z between a and b (in A3 and A4) with the following
formula:
Z in A5: =RANDBETWEEN(100*A3,100*A4)/100
X in A6: =NORMINV(NORMSDIST(A5),A3,A4)
We can use NORMDIST to generate the "corresponding Y", using the value in
A6.
But exactly how to do that -- and whether or not we actually need two random
values of X -- depends on the answers to the questions above.
Note: RANDBETWEEN generates random __integers__. That would be unduly
self-limiting for Z-values. By multiplying the range by 100, then dividing
the random integer by 100, we can generate Z-values with 2 decimal places
between A3 and A4.
That is good enough, IMHO. But if you want a continuous range of random
values between A3 and A4, use the following formula instead:
=A3+(A4-A3)*RAND()
