RANDBETWEEN() excluding values

[Jon von der Heyden]

Hi,

Does anybody know how to do a RANDBETWEEN(1,100) but have it exclude numbers that already exist within a defined range?

Thanks,
Jon

 

[sheetspread]

Actually you can, I should have tested it before asking (and read lhousesoccer's #3 post more carefully):

=SMALL(IF(RANDBETWEEN(1,5)={1,2,3,4,5},"",{1,2,3,4,5}),RANDBETWEEN(1,3))

works fine. He wanted to show the extra column.

I was looking for a single randbetween type formula that excluded numbers, but this one seems to do it:

e.g.

=CHOOSE(INT(RAND()*7)+1,1,3,4,6,8,9,10)

for 1 to 10 excluding 2,5,7

 

[Ajit_M]

Hi Barry,

I was trying to extend the above exercise with the third random number excluding the first two random generated numbers between 1 to 6.

Any suggestion would be a great help. Thanks, Ajit

Whichever number you have in A1 the IF function generates an array which contains the other 4, so if A1 = 2 this part

IF(A1={1,2,3,4,5},"",{1,2,3,4,5})

generates this array {1,"",3,4,5}

Then SMALL gives you the nth smallest out of that array [where n is generated by RANDBETWEEN(1,4) ]

SMALL could be replaced by LARGE, it makes no difference which of those you use....

 

[barry houdini]

=IF A1 is

=RANDBETWEEN(1,6) and then B1 is

=SMALL(IF(A1={1,2,3,4,5,6},"",{1,2,3,4,5,6}),RANDBETWEEN(1,5))

then C1 can be

=SMALL(IF(A1={1,2,3,4,5,6},"",IF(B1={1,2,3,4,5,6},"",{1,2,3,4,5,6})),RANDBETWEEN(1,4))

 

[Ajit_M]

=IF A1 is

=RANDBETWEEN(1,6) and then B1 is

=SMALL(IF(A1={1,2,3,4,5,6},"",{1,2,3,4,5,6}),RANDBETWEEN(1,5))

then C1 can be

=SMALL(IF(A1={1,2,3,4,5,6},"",IF(B1={1,2,3,4,5,6},"",{1,2,3,4,5,6})),RANDBETWEEN(1,4))

HI Barry,

Seems to be working great. Still not able to understand the logic of the if statements within.

But thanks a lot for the prompt response. Highly appreciated!

Ajit

 

[ben-miller1]

=IF A1 is

=RANDBETWEEN(1,6) and then B1 is

=SMALL(IF(A1={1,2,3,4,5,6},"",{1,2,3,4,5,6}),RANDBETWEEN(1,5))

then C1 can be

=SMALL(IF(A1={1,2,3,4,5,6},"",IF(B1={1,2,3,4,5,6},"",{1,2,3,4,5,6})),RANDBETWEEN(1,4))

Hi Barry,

Thanks for sharing a really useful tip.

I was wondering if there is a way to extend the logic you expressed in your last post (#13), over a much larger number of cells to exclude, in a short-hand form? My problem is that I am trying to assign the numbers 1-25 randomly down a column of 25 cells, but making sure that there are no duplicates. I'm sure there are other tricks out there if I wanted to do a one-time assignment; but unfortunately the problem dictates that I have to keep assigning numbers randomly over and over, making sure that each time those 25 cells are randomly assigned but with no duplicates.

Now, I know that there is an answer to this in post #13 - I can increase the number of nested IF functions in successive rows and reduce the RANDBETWEEN selection by one. However, this will become quite laborious by the time I get to 10 and beyond! (to say nothing of the fact that the logic will be incomprehensible to the people I will be presenting to). Can you help me find a more elegant and less work-intensive way of achieving this?

One idea I had was to write =SMALL(IF($A$1:A2={1,2,3,4,5....,24,25}... in the cell B3, and keep extending the logic in this way. I noticed from the Evaluate Formula that your formula places a blank in place of any number in the array for which the Logical Test is true. However, turns out this is not a valid way of expressing a command ... though I think I'm on to something.

Any ideas welcome!

Many thanks,
Ben

 

[edcull]

Hi

I have a spreadsheet with approx 30 rows in three sections, lets say rows 1-10, 11-20, 21-30.

It has three data columns lets say D, E, F in which I want to place random numbers.

Some of column F will be NULL.

For rows 1-20, columns D-F, I need to generate numbers 11-30.

This is the first step of the problem.

The second step is to do the same for rows 11-20 but randomly entering 1-10, 21-30 into columns D, E, F. And then similar for rows 21-30.

There is an added complication which is probably beyond the scope of this forum for which I expect I will need to hire a programmer!

But here it is anyway.

Let's say row 1 has the numbers 12 and 17 allocated. Row 12 cannot have 1 or 17 allocated to it, nor can 17 have 1 or 12 allocated to it.
Same goes for all rows.

I appreciate that I am asking a lot, but it would be great to learn how to do these things!

Best wishes

Eddie

 

[ozandagco]

Hi Barry,
I am trying to do something really similar to this but I cannot convert your formula to my situation. I am supposed to make a row that has all the numbers {0,1,2,3,4,5,6,7,8,9,10,11} in it. But after you have for example the number 6, you can't have it again until your row uses all the other numbers {0,1,2,3,4,5,7,8,9,10,11}. Can you help me?
Thank you

 

[pransford]

I want to do the same thing but with names...

 

[Eric W]

FYI, you'd have better luck opening a new thread than adding to a very old thread. More likely that someone will see it. Nevertheless, I happened to see it, so you can see if this works for you:

Book1
	A	B
1	Names	Scrambled names
2	Ann	Ivy
3	Bob	Gayle
4	Carol	Erica
5	Dan	Hank
6	Erica	Jack
7	Fred	Dan
8	Gayle	Fred
9	Hank	Ann
10	Ivy	Bob
11	Jack	Carol
Sheet1

Cell Formulas
Range	Formula
B2:B11	B2	=INDEX(A:A,SMALL(IF(COUNTIF(B$1:B1,A$2:A$11)=0,ROW(A$2:A$11)),RANDBETWEEN(1,COUNTA(A$2:A$11)-ROWS(B$2:B2)+1)))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

Just press F9 to rearrange the list.

 

[Phuoc]

Or try

=INDEX(A:A,AGGREGATE(15,6,ROW(A$2:A$11)/(COUNTIF(B$1:B1,A$2:A$11)=0),RANDBETWEEN(1,ROWS(A2:A$11))))

Just press Enter.