Some Excel help please

[JonInMiddleGA]

I'm wondering if there's a fairly simple way (i.e. like so simple a caveman could do it) to do something in Excel.

What I want to do is randomly order a range of numbers.

For example, if I have the numbers 1-40, I would like to be able to hit F5 and have it sort the range randomly using each number only once, simulating what I would get if I had a bunch of ping-pong balls numbered 1 through 40 & picked them out of a hat one at a time & I would know that the first number drawn was 37, the second was 14, etc.

Doable?

Thanks in advance.

[FrogMan]

I'd need two columns to do it.

First column is however many numbers you have, say from 1 to 40. Then right next to every ball number,
enter the formula =rand().

hit F9 to recalculate and then sort the thing by the second column

FM

[JonInMiddleGA]

Frogman, I tried that but got this
1 0.803176207
2 0.453942078
3 0.980224166
4 0.631304581
5 0.423562114
6 0.21927084
7 0.429044156
8 0.540794244
9 0.004624898
10 0.898444919
11 0.985363796
12 0.593286377
13 0.298161956
14 0.64414277
15 0.86172585
16 0.821225305
17 0.386906177
18 0.271532715
19 0.201856863
20 0.677492432

A little digging in the help file & I changed the formula to
=RANDBETWEEN(1,40)
and got whole numbers in the second column, but they included duplicates (i.e., there's a couple of 1's, a couple of 39's, etc).

That's what I'm trying to eliminate (since there's only one "ping pong ball" for each number) but have no clue how to do.

[FrogMan]

select these cells, two colums by 20 rows, then DATA--SORT and sort by the second column, whatever it is (probably the B column) and your ping pong balls will be in random order in the first column

FM

[FrogMan]

dola, the second column numbers are simply random seeds to sort the pong balls in random order...

FM

[JonInMiddleGA]

dola, the second column numbers are simply random seeds to sort the pong balls in random order..

Doh!

Now I get what you're doing. And by golly, that should indeed accomplish what I'm wanting to do just fine. Thanks.

Only problem is, I'm going to be kicking myself for quite a while for not thinking of this myself.

[FrogMan]

LOL! don't kick too hard, there are tons of things we don't see everyday, in excel and out of it too ;)

Glad to be of help.

FM

[finketr]

excel geek on the loose!

i love "Excel" questions/help thread as I always read them as "Ping: FrogMan"

[KevinNU7]

Interesting way to pick Mega Million numbers ;)

[JonInMiddleGA]

Interesting way to pick Mega Million numbers ;)

Funny you mention that, since the prospect of nearly $400 (before taxes & reduction from cash option) just had me buying 5 chances for the first time in at least a couple of years.

Will & I were just discussing which wrestlers we could/would book for his birthday party in April if we win. They would compete in the 1st Annual WWTL Tournament, to become the "We Won The Lottery" Heavyweight Champion :)

edit to add: Oops. I got caught up thinking about the MegaMillions & forgot why I started to post in the first place. I actually wanted to find a way to do this in Excel to help speed up some things with a couple of tabletop sports games.

[wade moore]

Funny you mention that, since the prospect of nearly $400 (before taxes & reduction from cash option) just had me buying 5 chances for the first time in at least a couple of years.

Will & I were just discussing which wrestlers we could/would book for his birthday party in April if we win. They would compete in the 1st Annual WWTL Tournament, to become the "We Won The Lottery" Heavyweight Champion :)

edit to add: Oops. I got caught up thinking about the MegaMillions & forgot why I started to post in the first place. I actually wanted to find a way to do this in Excel to help speed up some things with a couple of tabletop sports games.

I found myself searching for the powerball thread to look at your "If I win" plan so that I can be prepared for the $5 I plopped down today.

[JonInMiddleGA]

bump - so I can find the blasted thread without searching again later today

[FrogMan]

what, another megamillion ticket buy or another tabletop game on the way? ;)

FM

[JonInMiddleGA]

what, another megamillion ticket buy or another tabletop game on the way? ;) FM

Actually hoping to find some time somewhere to finally play the same game that brought this question up in the first place.