can i get excel to generate random numbers

mrsm

I have run a raffle and have all the details on an excel spreadsheet. I cant pick the numbers out of the hat as there are 6 different categories for prizes and each ticket can be entered in anyone or all of the categories so when a number is drawn i need to remoce it from the other draws.
So my question is it possible to get excel to pick the numbers by either randomly sorting the worksheet or by me inputting a range (range could be say 1-50 then 75-89 etc)

Woby_Tide

=RANDBETWEEN(1,15) then hitting PF9 will give random numbers between 1 and 15, can't seem to make it work with ranges though guess you could have


Cell A1 =RANDBETWEEN(1,50)
Cell A2 =RANDBETWEEN(75,89)
Cell A3 =RANDBETWEEN(1,2)


In first two cells each generates a random number in the range of tickets you need. The 3rd row then decides which of the first or second row wins (if you need more ranges you just change the final cell to have the top value as the number of ranges you have included

Going one step further in the interests of 'fairness' as chances of your number being selected in the range 1-50 is less than 75-89 mya have to go one step further and maybe have them in batches of 5

Cell A1 =RANDBETWEEN(1,5)
Cell A2 =RANDBETWEEN(6,10)
Cell A3 =RANDBETWEEN(11,15)
....
.....
Cell A8 =RANDBETWEEN(75,79)
Cell A9 =RANDBETWEEN(80,84)
Cell A10 =RANDBETWEEN(85,89)
Cell A11 =RANDBETWEEN(1.10)

magicmaria

or just put in =RAND()*100 pressing F9 will give you a random no between 1 and 100

mrsm

thanks I'll give both a go when i'm not fighting my little ones for the keyboard.

pealy

I'm glad I didn't buy a ticket!

Lets not forget that computers can't produce truly random numbers (I don't want to get philosophical but 'thinking' of a number requires the ability to 'think') often applications like excel will return the same set of 'random' numbers each time you start-up. I'm no excel guru but most languages (like VB) include some kind of Randomise function to try and ensure this doesn't happen.

Even so, you should be cery careful about using computer generated 'random' numbers, especially when there's money/prizes to be won. (I believe 'Ernie' the premium bonds number generator uses a naturally random event, radioactive decay, to generate his numbers)

Is there a simpler solution to this? In your original post you said "when a number is drawn i need to remoce (sic) it from the other draws", surely if you draw from a hat this just happens anyway? Once the ticket is out, it's out...

mrsm

well, everyone who'se bought a ticket is happy with it. basically we have an online sponsorship form, and the girls normally pay straight to that. to make it more fun, people have donated prizes and bought tickets. Not every one is so mis-trusting and we've raised lots of money for a charity that is close to our hearts.

as i said in my original thread, one ticket can be entered into several prize categories. some people want their tickets in one categorie, some in 2 or 3 some in all.
Having sold over 1500 tickets it would take forever to remove a ticket number from other categories once it has been drawn and one a prize . Hence wanting a virtual means of picking numbers.


thanks for your comments (dont think our prize donators would have wanted you staying at their house in the BVI, or would have knitted for your baby anyway )

mrsm

oh and i apologise for the typo obviously i meant remove not remoce just as i take it i need to be very wary not "cery wary" . Not always easy to type when a 27 month and 11 month baby want to type too

NastyMatt

Remember to turn off automatic calculation or it will generate new numbers each time you do anything in the spreadsheet.

Pealy - there is no thing as random within in the known laws of physics. If an event happens i.e shuffling a pack of cards and that events happens exactly the way it did the first time.. i.e. all the cards were in exactly the same order.. shuffled exactly the same way the desk would be the same.

And therefore, pulling a ticket out of a hat is NOT a random event. So I'd say Excel generating a "random" number is as close as pulling a ticket out of a hat

mrsm

thanks matt, not so nasty after all

pealy

Crikey - easily offended or what? Sorry if you thought I was being critical, I'll leave you to it next time.

tomstickland

Pseudo random number sequences are fine AFAIK. rnd() function.