Re: Need formula help

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

"Rebecca" <Rebecca@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
"Shane Devenshire" wrote:
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

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 -----

"Rebecca" <Rebecca@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:A2D95A5B-A4E0-4285-B0F1-17023187A967@xxxxxxxxxxxxxxxx
This works! Now is there a formula that can select one of these entries
randomly!

"Shane Devenshire" wrote:

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

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Rebecca" wrote:

> 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.

.

Relevant Pages

  • Need formula help
    ... I have never written an excel formula and am a complete newbie. ... Some have made more than 150 referrals. ... hundreds of times for 3 dozen employees. ... are created for me, excel can do a random sort, and choose a winner. ...
    (microsoft.public.excel.misc)
  • Re: Need formula help
    ... What if some employees come to you with adjusted counts of referrerals? ... the random selection is changed whenever the number of referrals is changed and when you add or delete rows before rows 37. ... I have never written an excel formula and am a complete newbie. ... > are created for me, excel can do a random sort, and choose a winner. ...
    (microsoft.public.excel.misc)
  • Re: calculating with weeks
    ... The definition of a week need finalising, but this works on the number of ... (there's no email, no snail mail, but somewhere should be gmail in my addy) ... Column B will have number of employees in position ... Column C will have the number of referrals ...
    (microsoft.public.excel.misc)
  • Re: Doctors
    ... Not in this case, Chief. ... Could one or both of those two employees offer referrals? ... Perhaps they have an "in" with doctors and would put in a good word for ...
    (alt.support.arthritis)