I am trying to model the success of sales reps and would like to create a
cell that has 3 options Fail, Average, and Superstar. I would aslo like to
assign ratios to 2 of the groups (so a user can play with the ratios) with
the third being default. So a simple example:

2 out of 3 Fail
1 out of 6 Average

Rep Number Expected Result
Rep 1 Fail
Rep 2 Fail
Rep 3 Average
Rep 4 Fail
Rep 5 Fail
Rep 6 Superstar

One venture ..

Assuming your "Rep number - Expected result" table is in A1:B7, where the
"Expected results" are to be randomly generated

In B2:
=INDEX({"Fail";"Fail";"Fail";"Fail";"Average";"Sup erstar"},RANK(C2,$C$2:$C$7))

In C2: =RAND()
Copy B2:C2 down to C7. Hide away col C. B2:B7 will return the desired random
scramble of the possible outcomes (Fail, Average, Superstar) in the specified
ratios. Press F9 to regenerate.

success? hit YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
" wrote:
I am trying to model the success of sales reps and would like to create a
cell that has 3 options Fail, Average, and Superstar. I would aslo like to
assign ratios to 2 of the groups (so a user can play with the ratios) with
the third being default. So a simple example:

2 out of 3 Fail
1 out of 6 Average

Rep Number Expected Result
Rep 1 Fail
Rep 2 Fail
Rep 3 Average
Rep 4 Fail
Rep 5 Fail
Rep 6 Superstar

I was hoping not to use the rand() function because then I can not replicate
my results. I was hoping I could use a formula that would be able make the
pattern of ratio's work so I would have consistent results.

Thanks,
Dan

"Max" wrote:

One venture ..

Assuming your "Rep number - Expected result" table is in A1:B7, where the
"Expected results" are to be randomly generated

In B2:
=INDEX({"Fail";"Fail";"Fail";"Fail";"Average";"Sup erstar"},RANK(C2,$C$2:$C$7))

In C2: =RAND()
Copy B2:C2 down to C7. Hide away col C. B2:B7 will return the desired random
scramble of the possible outcomes (Fail, Average, Superstar) in the specified
ratios. Press F9 to regenerate.

success? hit YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
" wrote:
I am trying to model the success of sales reps and would like to create a
cell that has 3 options Fail, Average, and Superstar. I would aslo like to
assign ratios to 2 of the groups (so a user can play with the ratios) with
the third being default. So a simple example:

2 out of 3 Fail
1 out of 6 Average

Rep Number Expected Result
Rep 1 Fail
Rep 2 Fail
Rep 3 Average
Rep 4 Fail
Rep 5 Fail
Rep 6 Superstar

Hello,

If you like to achieve a certain distribution "on average" then have a
look at my UDF redw or at the other ones shown under
http://sulprobil.com/html/distributions.html

If you need EXACTLY a specified count of your given results you can
use my UDF UniqRandInt:
http://sulprobil.com/html/uniqrandint.html

Regards,
Bernd