Rand Function Problem

**ssjagger** · 07-12-2011, 07:41 AM

I have a column A1:M1 with numbers 1 - 13 and I wish to select 5 Random Numbers. I use O1:S1 for the Rand function. When I execute the function I sometimes end up with ### in one of the Rand Cells? Anyone know why this happens and how to make it stop? Thanks

**NBVC** · 07-12-2011, 08:03 AM

Assuming you don't want duplicates....have a look here: Random Numbers with No Duplicates

**ssjagger** · 07-12-2011, 08:16 AM

I checked out the page you posted and I think there is something missing or maybe I'm reading it wrong??

In Step 3 it says "In Lottery Range (A14:B19):" but there is nothing after it.

Then it says "In column B, use the VLOOKUP and ROW functions in the following formula:
=VLOOKUP((ROW()-ROW($A$19)+1),$B$2:$C$12,2,FALSE)"

**NBVC** · 07-12-2011, 08:29 AM

Ok, not sure about that, never actually followed it myself...

But essentially, enter the RAND() function in 13 consecutive cells, say O1:AA1. Then in O2 or elsewhere enter: =RANK(O1,$O$1:$AA$1) copied across 5 columns.. to get 5 rand and unique numbers...

If the 13 numbers in A1:M1 are not the numbers 1:13, then you can use INDEX() function to get relative positioned numbers...

**ssjagger** · 07-12-2011, 11:22 AM

Ok, I see how that works.

What I am using is =INDEX($A$1:$M$1,RAND()*COUNTA($A$1:$M$1)), but I still end up with #value (###) error in some of 5 cells that I input this formula? Sorry, I didn't realize it was a #value error until I made the column width larger. If I hit F9 a couple times, I will get 5 numbers, some of them are duplicates, but I'm wondering why I end up with this #value error?? Would removing duplicate's solve this? How would I write it? Thanks again

**NBVC** · 07-12-2011, 11:24 AM

Where is the RANK part... RAND() gives decimal values... so multiplying by COUNTA() will give a decimal value... and there are no decimal rows/columns....

Can you post a sample workbook?