I need to create a formula or function that when selected, automatically
selects a random set of questions from a master list. Is this possible
in Excel? Any suggestions?:


--
hank bulger
------------------------------------------------------------------------
hank bulger's Profile: http://www.excelforum.com/member.php...o&userid=29379
View this thread: http://www.excelforum.com/showthread...hreadid=490923

Hank, This might help.

For this example I'll assume that you have a list 50 questions and want
to select 10 of them. These numbers, 50 and 10, aren't critical.

Further assume column A has a heading in cell A1, and numbers 1 thru 50
in cells A2 through A51. These correspond to the 50 questions.

In column B, cells B2 through B51, enter the formula =RAND() to get 50
random numbers.

In cell C2 enter the formula =B2=LARGE(B:B,10), and copy it down to
cell C51.

You should get 10 TRUE values. Select those questions.


--
rsenn
------------------------------------------------------------------------
rsenn's Profile: http://www.excelforum.com/member.php...o&userid=29050
View this thread: http://www.excelforum.com/showthread...hreadid=490923

Hi Hank,

One way (and there's probably a more elegant solution):

Suppose you have five questions in your master list and want to select a
random three. In A1:A5, enter:

=RAND()

In B1:B5, enter:

=SMALL($A$1:$A$5,ROW())

In C1:C5, enter the numbers 1 through 5, or however you prefer to uniquely
identify each question in your master list.

In D1, enter:

=VLOOKUP(A1,$B$1:$C$5,2,0)

and copy/paste it down through D2:D3. Now, whenever you press F9 (or make
some data change to your worksheet), RAND() recalcs, as do SMALL and
VLOOKUP, and you'll get a different three question numbers.

Obviously, you can extend this to as many rows in columns A, B and C as you
have questions in your master list, and as many rows in column D as you want
to sample from that list.

If you want to put these formulas in rows other than those beginning with
Row 1, you'll need to make adjustments to the ROW argument to SMALL.

--
C^2
Conrad Carlberg

Excel Sales Forecasting for Dummies, Wiley, 2005


"hank bulger"
wrote in message
...

I need to create a formula or function that when selected, automatically
selects a random set of questions from a master list. Is this possible
in Excel? Any suggestions?:


--
hank bulger
------------------------------------------------------------------------
hank bulger's Profile:
http://www.excelforum.com/member.php...o&userid=29379
View this thread: http://www.excelforum.com/showthread...hreadid=490923