# Excel Help

#### dev653

##### New Member
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.

### Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Try this

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

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 ..

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 ..

Here's the pic:

Last edited:
Use

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

in B3 and copy down and across

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?

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

ought to do it. Thks

Kaps

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))

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

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 ..

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

Sorry, couldn't edit for some reason..

Replies
1
Views
130
Replies
14
Views
631
Replies
0
Views
394
Replies
0
Views
333
Replies
0
Views
271

1,202,977
Messages
6,052,890
Members
444,608
Latest member
Krunal_Shah

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back