Excel "random" formula

[JeffITman]

I'm trying to create a sheet that does the following...

Column 1 Column 2 Column 3 Column 6 Column 7
ID Last name entry Random Random

Say if column 1 had ID's 1-20(a1:a20) and Column 3 said that ID 1 had 5 entries. Is there a way to make column 6 and 7... to generate random numbers between 1-20 depending on the amount of entries in column 3. So if ID 2 only had 1 entry it would only generate ID 2 one time between column six and seven??

 

[GlennUK]

Can explain this more clearly please:

So if ID 2 only had 1 entry it would only generate ID 2 one time between column six and seven??

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[mintjulep]

=RANDBETWEEN(1,A3)

maybe. not really sure what you want.

 

[JeffITman]

Basically... let's call this a bracket worksheet. Say column A has ID #'s from 1-20 (a1:a20). Column B has the name of the player. Column C has the amount of times they've entered the bracket.
ID Name Entries Random Generates
1 John Doe 4 1 2 5
2 Jane Doe 2 5 3 4
3 Jack Doe 5 3 5 1
4 Jeff Doe 3 5 1 2
5 Sam Doe 6 2 6 3

So Random Generates would randomly generate the ID number equal to the Entry times. Say ID 1 had 4 Entries, the random columns would random generate ID 1 4 times.

 

[mintjulep]

Random: Made, or occurring without definite aim, reason, or pattern.

 

[AnotherHiggins]

Jeff,

I still don't really understand what you want. Your example doesn't show what I think I read in your description.

Let's look at line 1:[tt]
ID Name Entries
1 John Doe 4[/tt]
You wrote, "Say ID 1 had 4 Entries, the random columns would random generate ID 1 4 times". Since there are four entries for line 1, do you want the ID to appear in four columns across like this?[tt]
A B C D E F G
ID Name Entries (Random Generates)
1 John Doe 4 1 1 1 1[/TT]

Please remember that we can't read your mind and you need to fully explain what you want to accomplish if anyone here is going to be able to help you.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[JeffITman]

John - That's exactly what i'm looking for. Say we had 7 columns D:J and rows 1:20. I would like my spreadsheet to do exactly what you have shown except would like ID 1 (and all the others) scattered around randomly throughout the 7 columns and 20 rows. That's perfect!

 

[AnotherHiggins]

OK.

Place this formula in cell D2 (I'm assuming you have headers in row 1):
[tab][COLOR=blue white]=IF($C2>COLUMN()-4,$A2,"")[/color]

Drag that formula across and down as far as needed. If you drag it down below where you currently have data in columns A:C then it will return "", which looks like a blank cell.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[JeffITman]

Is there any way to make it refresh and randomly generate again?

 

[CBasicAsslember]

Is there any way to make it refresh and randomly generate again?

Press F9

 

[JeffITman]

A B C D E F G
ID NAME Entry
1 3 1 1
2 5 2 2 2 2
3 4 3 3 3
4 2 4
5 4 5 5 5
6 3 6 6
7 6 7 7 7 7
8 4 8 8 8
9 1
10 2 10
11 5 11 11 11 11
12 6 12 12 12 12
13 1
14 3 14 14
15 4 15 15 15
16 3 16 16

This is what happened when i did that. I need it to randomly place the numbers in columns D:G

 

[AnotherHiggins]

randomly generate again?

I still have to wonder if we are missing something. What I suggest does *NOT* randomly generate anything - it fills in data in a very predictable, non-random way.

But, as far as not getting enough columns filled in, All I can suggest is to copy and paste exactly what I typed in my previous post and paste it into cell D2. Then hover the cursor over the bottom right corner of the cell until the cursor changes to a bold +. Now click and drag right all the way to column J (you said J in an earlier post, G in the last post??), then repeat and drag down for as many rows as you have data.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[DirkStruan]

I think what Jeff is after is a matrix randomly populated with the values that have been created by John's formula.

If so, one approach could be to make a random list of all the numbers and then create a matrix of these values

1. do what John has told you
2. name the range of the values that John's advice created
3. below your 20 lines (or whatever) have a list from 1 to the "sum of all the entries" (probably best have the second value as "=if(cell_above<"sum of all entries",cell_above+1,"")" [fill in the necessary cell references or named ranges and drag it down as far as is necessary]
4. Assuming point 3 started in A25, say, make B25 equal to "=if(A25<>"",Rand(),"")"
5. In C25 have "=if(A25<>"",Large("name of range in 2",A25),"")"
6. In D25 have "=If(B25="","",Index($C$25:$C$x,Match(Large($B$25:$B$x,A25),$B$25:$B$x,0)))" where x is the the last row number (so row x, column A holds a value equal to the sum of the Entries).

What you should have now is a random list of values consisting of the entries listed in the bit John sorted for you. I'll refer to this D column part as array1. (Aside - there was a post a few weeks ago about a random list of numbers that may be helpful, some people had very quick ways of setting them up but I couldn't see it when I searched for it - anyone else know the one I mean?).

The next problem is putting them into a g by h array/grid (I assume g would be 20 in this instance, being the number of rows) as you can't be certain the sum of the entries will be a rectangular number. If it isn't, how would you deal with it? Are you happy for last few entries to be blank and if there is more than one would you put the blanks in the last column or row of the g by h matrix?

Assuming you'd put the blanks in the last column:

Set up a set of headings in the AA column and along (the number of columns is obviously equal to the sum of the entries divided by the number of rows (20 in this case), rounded up. Put a 1 in AA1, a 2 in AB1 etc as far as you need to go across. Then in AA2 (the top left entry for your matrix) type "=index(Array1,(AA$1-1)*20+ROW()-1)". This should equal the number in D25.

The above is set up to work on 20 entrants. You could refer to a named cell equal to a countA function (or something) to make this more variable.

Apologies if some of this is slighly off as I've not set up the spreadsheet (a "quick" answer during my lunch hour). May be able to check back in for problems in a couple of hours, otherwise it will be tomorrow. If anyone else follows what I was aiming to do and can provide answers in my absence, please be my guest.

D

 

[ck1999]

Would this code not work

place in column D: =IF($C4<>"",IF($C4>=1,RANDBETWEEN(1,20),""),"")
place in column e: =IF($C4<>"",IF($C4>=2,RANDBETWEEN(1,20),""),"")
place in column f: =IF($C4<>"",IF($C4>=3,RANDBETWEEN(1,20),""),"")
place in column g: =IF($C4<>"",IF($C4>=4,RANDBETWEEN(1,20),""),"")
place in column H: =IF($C4<>"",IF($C4>=5,RANDBETWEEN(1,20),""),"")

ck1999

 

[dallen43]

If you don't have the Analysis ToolPak add-in, RANDBETWEEN will give a #NAME? error.

In that case replace RANDBETWEEN(1,20) with ROUNDUP((RAND()*20),0). This assumes you would like only whole numbers.

Deb