RAND causes duplicate occurances

[Dan]

I have a series of functions that begin with 24 occurances of the RAND()
function. each has a 1/24 chance of occuring. However, I don't want any
occurnace to occur more than once (like a deck of cards). Is there another
function I'm missing to modify this?

 

[Dan]

That was a little confusing. Here that bad boy sits:

http://us.f1f.yahoofs.com/users/d39.../Probability+Spreadsheet.xls?BCPUOf_AVW_IW6H0

I want the outcomes to be random and non-repetative in each recalculation.

TIA

 

[J.E. McGimpsey]

I'm not clear on how you're using the 24 RAND()s.

RAND() produces a pseudorandom number between 0 and 1. It will not
have any duplicates within any 24 calculations, to 15 decimal
places. If you round to fewer places, you won't be guaranteed a non
duplicate.

One way to get the integers 1 to 24 in random order is to put 1
through 24 in A1:A24, say, and =RAND() in B1:B24. Sort on column B
and take the order produced. You can sort again for another random
order.

 

[Dan]

http://f1.pg.briefcase.yahoo.com/bc/dslaugh/lst?.dir=/My+Shared+Folder&.view=l

It's in my yahoo briefcase / probability spreadsheet. If this link doesn't
work, I'll have to bow out of this one gracefully (and that hardly ever
happens).

 

[Max]

Try playing around with this set-up

Put in A4: =RAND(), copy down A4:A27
Name the range A4:A27 as say: TBL

Select B4:B27
Put in the formula bar: =RANK(TBL,TBL)
Hold down Ctrl + Shift keys, press Enter
(It's an array formula)

Done correctly, Excel will wrap curly braces around
the formula, viz: {=RANK(TBL,TBL)}

In B4:B27 will be a random splash of the numbers 1 - 24
without repeats. Press F9 for a new splash.

 

[Tushar Mehta]

See the Excel | Tutorials | Random Selection page of my web site for a
variety of ways to do what you want.

--
Regards,

Tushar Mehta
MS MVP Excel 2000-2003
www.tushar-mehta.com
Excel, PowerPoint, and VBA tutorials and add-ins
Custom Productivity Solutions leveraging MS Office