Need formula help

[Rebecca]

Hi. I have never written an excel formula and am a complete newbie. My end
goal is to select a winner for a drawing. All employees who made loan
referrals are entered to win a prize, and are entered for each referral made.
Some have made more than 150 referrals. I don't want to type in the names
hundreds of times for 3 dozen employees. I also hope that once all the names
are created for me, excel can do a random sort, and choose a winner.

 

[Shane Devenshire]

Hi,

1. Highlight the range where you want to put the name 300 times
2. Type the name but don't press Enter
3. Press Ctrl+Enter

 

[Rebecca]

This works! Now is there a formula that can select one of these entries
randomly!

 

[JoeU2004]

This works! Now is there a formula that can select one of these
entries randomly!

One way:

=INDEX(B1:B1000,1+RAND()*COUNTA(B1:B1000))

But I would not structure the data that way. What if some employees come to
you with adjusted counts of referrerals? You would need to insert or delete
rows; and you might easily know by how many if the employee gives you an
updated total count instead of an incremental count.

One alternative:

1. List all employee names in B1:B36, and list the corresponding number of
referrals in C1:C36.

2. Put zero into A1, and starting with A2 and copy down, put =A1+C1. Note:
You can hide column A, if you wish.

3. Then the random selection is: =VLOOKUP(RAND()*SUM(C1:C36),A1:B36,2)

Caveats: As you will discover, the RAND() is recomputed every time you
change anything in the workbook. You might want to consider the following
UDF/

Function myRAND(Optional rng as Range) as Double
myRAND = Rnd()
End Function

You can use myRAND in the same way as RAND, namely myRAND().

Alternatively, you can use myRAND(C1:C37). Thus, the random selection is
changed whenever the number of referrals is changed and when you add or
delete rows before rows 37.

To add the UDF, press F11, click on Insert > Module, then copy-and-paste the
function above into the VB editor pane that should appear.

Note: It would behoove you to also set the macro security to medium. In
Excel 2003, click on Tools > Macros > Security > Medium, then click OK.


----- original message -----

 

[JoeU2004]

Errata....

=INDEX(B1:B1000,1+RAND()*COUNTA(B1:B1000))

Apparently, INDEX rounds the row index. So try the following instead:

=INDEX(B1:B1000,1+RAND()*(COUNTA(B1:B1000)-1))


----- original message -----