Need for a random formula that generates a pre-determined distribution

[jerryong]

Hi everyone,

I was using the RandBetween formula to generate a random Ratings number between 1 and 3 (in Column B) for 10 employee ID numbers (1 to 10 in Column A). It was easy to use.

I have one question:
I wanted the Ratings to have a pre-determined forced distribution where Rating 1 = 10%; Rating 2 = 80% and Rating 3 = 10%. How do you generate a random number where the ratings distribution always equals this forced distribution? In this example using 10 employees, 8 employees will have a Ratings of 2, 1 employee will have a Rating of 1 and 1 employee will have a Rating of 1. Currently, the randomly generated Copy/Paste results of the RandBetween formula generated 2 employees with a Rating of 2, 5 employees with a Ratings of 1, and 3 employees with a Rating of 3.

I have one comment:
As you can see the RandBetween formula generates a random number every time I saved the file and re-open it. The count of of the Ratings in the Summary changes each time I re-open the file. I know this an Excel rule, and I can easily copy/paste the results at the bottom (below the black line) to save the distribution that I want.

I would greatly appreciate your help on this formula.

Thank you,
Jerry

[6StringJazzer]

To guarantee a predetermined distribution, you can't generate random numbers. It's a mathematical contradiction. You have to predetermine the numbers that will give you the desired distribution, and then randomly assign them to the employees.

I have set this up in your sample file. Also, I would leave formulas in C37:D40, since those formulas will still be valid.

Your method of freezing the data by copy/paste values works. For a more robust solution we could use VBA to put the values directly in.

[Gregb11]

** - I did not see Jeff's post before posting mine. **


Well, here's an option - using a "helper" column:
In C2, enter:
=RAND()
and copy down the 10 rows
If you're doing it for exactly 10 rows, then in B2, enter:

=IF(C2=MIN($C$2:$C$11),1,IF(C2=MAX($C$2:$C$11),3,2))

If you want it to be a little more flexible, you can use this, and change the ranges as you add/remove rows:

=IF(C2<=SMALL($C$2:$C$11,0.1*COUNT($A$2:$A$11)),1,IF(C2>=LARGE($C$2:$C$11,0.1*COUNT($A$2:$A$11)),3,2))

and copy down.

If you don't want the formulas to generate a number all the time, and only when YOU want them to, you can always go to FORMULAS>"Calculation Options">"Manual"

[jerryong]

Thank you so much 6StringJazzer and Gregb11 for responding to my query. It works!