If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 Using =RANDBETWEEN(a,b) in a paers of a whole colume
 Author Name Remember Me? Password
 Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

## Using =RANDBETWEEN(a,b) in a paers of a whole colume

 Thread Tools Display Modes
#1
June 1st 12, 10:48 PM posted to microsoft.public.excel.worksheet.functions
 Harold A Climer external usenet poster Posts: 1
Using =RANDBETWEEN(a,b) in a paers of a whole colume

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?
 Ads
#2
June 6th 12, 11:24 PM posted to microsoft.public.excel.worksheet.functions
 joeu2004[_2_] external usenet poster Posts: 637
Using =RANDBETWEEN(a,b) in a paers of a whole colume

"Harold A Climer" > wrote:
> 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()

 Thread Tools Display Modes Linear Mode

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off
 Forum Jump User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Excel Newsgroups     Excel Discussion (Misc queries)     Setting up and Configuration of Excel     New Users to Excel     Excel Worksheet Functions     Links and Linking in Excel     Charts and Charting in Excel     Excel Programming About ExcelBanter     About this forum

 Similar Threads Thread Thread Starter Forum Replies Last Post how to vlookup colume with different values Bklynhyc Excel Worksheet Functions 2 October 13th 09 07:28 PM Colume Headings Gu y Excel Worksheet Functions 1 November 30th 07 12:40 PM Change from row to colume n colume to row [email protected] Excel Programming 2 October 4th 07 03:35 PM 3 COLUME CHART BANDIT Excel Worksheet Functions 0 March 21st 06 07:17 PM How can I look up max of one colume and display the colume to the. Brian Cornejo Excel Worksheet Functions 2 February 21st 05 05:47 AM

All times are GMT +1. The time now is 09:59 AM.

 - Contact Us - ExcelBanter forum home - FAQ - Links - Privacy Statement - Top

Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2013, Jelsoft Enterprises Ltd.
Copyright ©2004-2013 ExcelBanter.
The comments are property of their posters.