Excel Help

[dev653]

Hi there,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
I am currently trying to implement a program for my work. It is an AFL/NRL Tipping Competition.<o></o>
<o> </o>
The basic idea is that each week, each person is designated 1 of 16 AFL teams and 1 of 16 NRL teams to be drawn at random. To save time and effort of drawing out each person's tips each week with a barrel, my bosses decided to see if I could implement it into a computed form.
<o> </o>
My plan is to randomly generate two numbers for each person, one for their AFL team and one for their NRL team, between 1-16. I have that part working fine, with the formula: =INT(<?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-comffice:smarttags" /><st1lace w:st="on">RAND</st1lace>()*(17-1)+1)
<o> </o>
The problem is, I cannot convert that random number into which team it represents.
For AFL, 1 = Blues, 2 = Bombers, 3 = Bulldogs etc etc.
<o> </o>
I tried to use consecutive IF statements, but it turns out you can only use 8 consecutive IF statements in a single cell. I then tried to use VLOOKUP and HLOOKUP but I couldn’t figure them out and it did not return the value I wanted.
<o> </o>
Can anyone possibly help me with this? The layout sort of sits like this..
<o> </o>
Name 1……AFL Team……NRL Team
Name 2……AFL Team……NRL Team
Name 3……AFL Team……NRL Team
Name 4……AFL Team……NRL Team
Name 5……AFL Team……NRL Team

Any help would be greatly appreciated, thankyou.

 

[xld]

Try this

=INDEX(A2:A17,RANDBETWEEN(1,16))

 

[dev653]

I couldn't figure it out .. Im sorry ..

I have the peoples names starting from A3 and going down ..
I have the AFL Team Column at B3 going down .. where I guess the function should go
I have the NRL Team Column at C3 going down .. where the function I guess should also go

I have put the AFL and NRL teams starting F3 and G3 and consecutively and going down to F18 and G18 .. When we do the draw they probably won't be seen, but they are there for the function i guess ..

Hope this made sense :S

Here is the pic:

I couldn't figure it out .. Im sorry ..

I have the peoples names starting from A3 and going down ..
I have the AFL Team Column at B3 going down .. where I guess the function should go
I have the NRL Team Column at C3 going down .. where the function I guess should also go

I have put the AFL and NRL teams starting F3 and G3 and consecutively and going down to F18 and G18 .. When we do the draw they probably won't be seen, but they are there for the function i guess ..

Hope this made sense :S

Here's the pic:

 

[xld]

Use

=INDEX(F$3:F$18,RANDBETWEEN(1,16))

in B3 and copy down and across

 

[dev653]

Just came up with a #NAME? error .. Does the index and randbetween connect? Sort of defining the index and randomising a number with both but assigning a number to that cell, or a value to the cell?

 

[Kaps_mr2]

=INDEX(F$3:F$18,:INT(<st1lace w:st="on">RAND</st1lace>()*(17-1)+1))

ought to do it. Thks

Kaps

 

[xld]

They are connected, the INDEX retrives the iteme from the range indexed by the RAND.

The #NAME problem might be because you don't have the Analysis Toolpak installed, so you could install it or try

=INDEX(F$3:F$18,INT(RAND()*(17-1)+1))

 

[dev653]

Thankyou guys .. so much .. all working now .. you guys are lifesavers

 

[dev653]

Hey guys, thanks again for the help, but I have another question, if that's okay.

My manager would still like the draw to be random, but like the teams given out randomly .. So .. If I were to draw a team out for each person out of a barrel, the team would stay out til all the teams are drawn, then they all get put back in and it starts again .. This would mean there would be say .. 7-8 of each team and not 2 of one team and 12 of another, make sense?

Is there any way I can incorporate that into my excel spreadsheet? I used the formula as said above in the cells to calculate the random team ..

 

[dev653]

My manager would still like the draw to be random, but like the teams given out evenly

Sorry, couldn't edit for some reason..