RANDBETWEEN - exclude previous cases

Question

Level 1

0 points

RANDBETWEEN - exclude previous cases

From cruising the 'net, I don't think there are any simple ways to exclude numbers from a RANDBETWEEN function, so I need some in-depth help on this one.

I am a researcher, and I run the same 20 subjects twice a day, once in the morning and once in the afternoon. I create my datasheet in Numbers and generate a random assignment for each subject out of a pool of 8: so my function in each cell is =RANDBETWEEN(1:8). My problem is that subjects cannot be given the same assignment twice in a day; so if one is assigned 4 in the morning, s/he cannot be 4 again in the afternoon (but can be any of the other 7 numbers, and it doesn't matter if other subjects take the same number). So ideally I would've needed a function like: =RANDBETWEEN(1:8, EXCLUDE C3) which obviously doesn't exist in Numbers or other spreadsheet apps I know of.

Can anyone help with a solution? A single cell function would be most useful, but if a number of cells have to be used then so be it. I have to print a LOT of these sheets, so I need something that self-monitors whether the numbers change for each subject (I can't keep checking and correcting myself).

Thanks so much!

Macbook Pro (2008), Mac OS X (10.5.7), Functions - RANDBETWEEN

Posted on May 23, 2009 7:48 PM

Reply

Answer 1

Brie Fly

Level 5

7,925 points

May 23, 2009 9:11 PM in response to ratking

Do both morning and afternoon have to be completely random? How about for the afternoon session, you simply use 9 – theResultOfTheMorningRandomNumber? In other words, if cell B2=RANDBETWEEN(1:8), then the second session is calculated in C2 as simply =9–B2.

Reply

Answer 2

ratking Author

Level 1

0 points

May 24, 2009 10:47 AM in response to Brie Fly

Thank you for your suggestion. That could potentially work - however, I would prefer for the new number to be random, so that subjects can't just learn a rule as to what number to expect next based on the morning.

Reply

Answer 3

Badunit

Level 7

30,708 points

May 24, 2009 12:10 PM in response to ratking

You might try searching the web for how to randomize a list in Excel. That is the underlying problem you are trying to solve. I would do it and try to provide you a formula but I am surfing via my phone and without access to Numbers at the moment.

Reply

Answer 4

Brie Fly

Level 5

7,925 points

May 24, 2009 7:00 PM in response to ratking

After sleeping on it, my suggestion still doesn't resolve the basic problem of potential duplicates. What if all 20 subjects got the same random number between 1 and 8? Maybe you could just have your subjects draw from a deck of cards when they first arrive in the morning. 😉

This is an interesting challenge. The randomization without duplicates has to occur on the first calculation.

Reply

Answer 5

ratking Author

Level 1

0 points

May 24, 2009 7:00 PM in response to ratking

I finally found an awkward but fully satisfactory solution at the following link, under "Non-sequential Non-repeating Random Numbers"
http://blog.livedoor.jp/andrewe/archives/9627394.html
I set up a separate page from the two pages I print: 20 cells with the sequence "12345678", a second set of cells which selects a random number from the sequence, a third which removes the previous cell's value from the set (just for this row, not for all), and finally a fourth cell which selects a new random number for that subject's second daily session. Then in my printed pages I simply reference the second and fourth cells (respectively).

That said, I don't know if all of it would work in Numbers - I have switched to Excel on my desktop rather than wrack my brain on the laptop. It's frustrating that the RANDBETWEEN function doesn't have an exclusion parameter, it seems like a no-brainer and would have completely eradicated this problem.

I am leaving this topic open as "has not been answered yet" just in case someone has a more elegant solution, but don't anybody strain, I consider my problem solved. Thanks all!

Reply

Answer 6

Brie Fly

Level 5

7,925 points

May 24, 2009 7:21 PM in response to Brie Fly

Ratking,

How about this...

For the morning session, a random number between 1 and 8 is generated, and for the afternoon session, a random number between 1 and 7 is generated, which is then added to the morning's random number. If the afternoon session sum is greater than 8, just subtract 8.

I used three cells for each subject, RANDBETWEEN(1,8); RANDBETWEEN(1,7), and =IF(B11 C11>8,B11+C11-8,B11C11), where B11 is the morning number, and C11 is the random increment. The third cell (D11, in this example) is your afternoon number.

Reply

Answer 7

KOENIG Yvan

Level 8

41,819 points

May 25, 2009 12:03 PM in response to Brie Fly

I already posted a script building random lists of this kind in the AppleWorks forum.
If I remeber wel, an other helper posted a response using only AppleWorks without AppleScript.
I'm too busy to search in existing threads but searching with the keystring "random AND randomized" (two words used in my script) in the AppleWorks area may be a good point of departure.

Yvan KOENIG (from FRANCE lundi 25 mai 2009 21:00:13)

Reply

Answer 8

Badunit

Level 7

30,708 points

May 25, 2009 3:46 PM in response to ratking

Brie posted what seems like a reasonable solution for two random assignments. A solution for the more general case of putting all 8 assignments in random order is as follows:

If the assignments are specified by the numbers 1-8:
B3 through I3 = RAND()
B4 =MID("12345678",RANK(B3,$B$3:$I$3),1) and fill-right to I4
The formulas in B4-I4 each pick a unique character from the string "12345678" based on the rank (largest to smallest) of the random numbers in B3-I3.

If the assignments have names:
B2 through I2 are the names of the assignments (such as "Emotional IQ" , "Teamwork", etc)
B3 though I3 = RAND()
B4 = INDIRECT(ADDRESS(2,RANK(B3,$B$3:$I$3)+1)) and fill right to I4
The formulas in B4-I4 each create a cell reference to the assignment names based on the rank (largest to smallest) of the random numbers in B3-I3.

Reply