Random Numbers with certain numbers more common than others

[Steve Kasher]

I need to get a random distribution of numbers to simulate a series of events
that will occur between 44%-72% of the time on a weekly basis over the course
of a year. The high and low ends of this range will occur much less often
than the middle of the range, and I want the random numbers to refelct this.

To explain by example, say a person is flipping a coin, but the coin is
rigged to be heads slightly more often than tails, say on average 55% of the
time. You toss this coin 100 times every week, some weeks it will be heads
56% of the time, others 70% of the time, others 45%. However, most of the
weeks the percentage of heads will be between 50% and 60% of the time, and
less frequently it will be heads in the 44-49% or 61-72% ranges.

Is there a way to set up RANDBETWEEN so that it gives random numbers but
they are weighted to return some numbers more frequently than others, i.e.,
it returns 52-60 70% of the time and 44-51 or 59-72 the remaining 30% of the
time?

Thanks for your help. Hope this wasn't too long.

 

[Jim Cone]

The free "Special Randoms" workbook may come close to what you want.
It can create four different sets of random numbers, among them
random numbers that average a specified amount. You enter...
the range (min and max) the Average and the set size.
Numbers are generated in a new workbook.
Download from the Products page at my website - no registration required...
http://www.realezsites.com/bus/primitivesoftware
Your comments and suggestions about the program welcomed.
--
Jim Cone
Portland, Oregon USA
(Excel Add-ins / Excel Programming)




"Steve Kasher"
<[email protected]>
wrote in message
I need to get a random distribution of numbers to simulate a series of events
that will occur between 44%-72% of the time on a weekly basis over the course
of a year. The high and low ends of this range will occur much less often
than the middle of the range, and I want the random numbers to refelct this.

To explain by example, say a person is flipping a coin, but the coin is
rigged to be heads slightly more often than tails, say on average 55% of the
time. You toss this coin 100 times every week, some weeks it will be heads
56% of the time, others 70% of the time, others 45%. However, most of the
weeks the percentage of heads will be between 50% and 60% of the time, and
less frequently it will be heads in the 44-49% or 61-72% ranges.

Is there a way to set up RANDBETWEEN so that it gives random numbers but
they are weighted to return some numbers more frequently than others, i.e.,
it returns 52-60 70% of the time and 44-51 or 59-72 the remaining 30% of the
time?

Thanks for your help. Hope this wasn't too long.

 

[Steve Kasher]

Thanks, I'll give it a try and let you know how it goes.

 

[Rick Rothstein \(MVP - VB\)]

You could load up a column with the items repeating as often as you want to
weight them, then just pick a random number between one and the last row
containing data and INDEX into that row. For example, if you wanted your
weighted coin to produce 55% heads and 45% tails, then in some column (say
Column C for this example), put H in the first 55 rows and T in the next 45
rows. Then use this formula to "flip" the coin...

=INDEX(C1:C100,RANDBETWEEN(1,100))

Just load up Column C with whatever you are selecting between, repeat the
numbers you want to occur move frequently whatever number of times that will
give you the percentage weighting you want and adjust the range and top end
of the (max row number) of the RANDBETWEEN function to match.

Rick

 

[Steve Kasher]

Rick, thanks, looks like your solution does exactly what I was looking for!

 

[Bernd P]

Hello Steve,

I suggest to use my UDF
http://www.sulprobil.com/html/redw.html
or
http://www.sulprobil.com/html/histogrm.html
which have been programmed exactly for this purpose.

Regards,
Bernd