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()