RAND causes duplicate occurances

  • Thread starter Thread starter Dan
  • Start date Start date
D

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?
 
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.
 
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.
 
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
 
Back
Top