Produce Random numbers from a list with exceptions

Hi SG,

A couple of minor niggles:

I was scratching my head over how you came up with 63 pairs, even after I noticed a 9 in the list. With 8 lessons (the number specified in the original [ED] revised description), there are 56 ordered pairs ( 8 * (8-1) ); with nine lessons there are 72 ordered pairs ( 9 * (9-1) ).

Minimum count check doesn't list checks for lessons 7 and 8 (or the extra #9).

Using RANDBETWEEN(1,63) to choose among 63 pairs seems logical, but with only 63 possible values, there's a high probability that one or more pairs will be chosen more than once (see days 3, 17 and 24 in your example). Hiroto and I used RAND() to generate the set of random numbers, then used LARGE or SMALL to step the lookup or match function through. With more than 100,000,000 possible results, the likelihood of duplicate choices is somewhat lower.

Overall, looks good. Did you include a Numbers 2 file in your dropbox post?

Regards,

Barry

Hi Barry,

Thanks for the suggestions/corrections to minimize confusion!

In the Numbers 3 document linked to above and again here (Dropbox link) I've corrected the 'Pairs' table to remove the 9's (at least I didn't have lesson number 0s in there🙂) and changed the RANDBETWEEN parameters to match.

It seems one advantage of this approach may be the ease in changing the set of available pairs. Say you always want lesson 4 first and lesson 3 second on a given day and never want 4 to follow 3 the same day; you just remove the 3, 4 pair, etc. Or you never want lesson 3 and 7 together; remove 3,7 and 7,3. And then revise the RANDBETWEEN parameters to match the new dimension of the 'Pairs' table.

Here's the export to Numbers 2 (Dropbox link).

SG

Barry wrote:

Using RANDBETWEEN(1,63) to choose among 63 pairs seems logical, but with only 63 possible values, there's a high probability that one or more pairs will be chosen more than once (see days 3, 17 and 24 in your example). Hiroto and I used RAND() to generate the set of random numbers, then used LARGE or SMALL to step the lookup or match function through. With more than 100,000,000 possible results, the likelihood of duplicate choices is somewhat lower.

Hi Barry,

I've been pondering the likelihood of duplicate pairs. I think I've convinced myself that, given a schedule of 30 days, essentially what is happening is that 30 selections are being made from 56 possible pairs of lessons (counting 2,3 as different from 3,2). So it seems that a highly randomized process of selecting each member of a pair may not necessarily reduce the chance of duplicate pairs, because ultimately there are only so many possibilities... I see that Hiroto's example also has duplicate pairs.

One could put a "check" for duplicated pairs to make sure there aren't too many and, if too many, roll those dice (I mean click that checkbox) again.

Any chance of posting a link to your document? It's a handful to reconstruct.

SG

"Any chance of posting a link to your document? It's a handful to reconstruct."

Hi SG,

I would, but I've no place to put the file that I could link to. Drop me an email, and I'll send by reply. Under 300k zipped.

Offer open to Manos as well. Hover mouse over name to get the address.

Regards,

Barry

PS: Regarding duplicates (and red cells).

I added a column to your file to count duplicates. Paused to check it only when I had all red cells cleared (except those in this counter). Most 'clear pages had at least one duplicate pair, some had a set of three identical pairs, one had a pair that occurred four times in the thirty sets. These were counting ordered pairs, so 13, 13, 31 would count as 2 and 1, not as 3.

Duplicate pairs, and lessons on exclusion days seem to occur pretty often with the RANDBETWEEN picking of pairs to list. I started counting clicks to get from one clear list to the next. I quit after the fifth trial, with these results: 22, 40, 17, 17, 64.

With my second version, using RAND and a smaller (30 pair) set, I always two sets of two duplicates (built into the set as they're necessary to fill the 30 day period), but could control which pairs were duplicated by editing the last two entries in the pairs list. Duplicates in this case included pairs like 78 and 87, as the order is determined after the pair is retrieved.

Red flags were greatly reduced, though.My 'five' trials expanded to ten, with these click counts from one clear list to the next: 5, 2, 3, 7, 2, 5, 14, 8, 9, 4.

I discarded one test where a single click resulted in a second clear list—I didn't notice the usual flicker of numbers changing places, and suspect the pointer had wandered off the checkbox for that click.

B

I updated the document I posted earlier to handle a single column of input with excludes. I am not following the complete problem but I think my solution for random, unique lists is actually pretty slick and does apply.

The general overview is that if you want a list of n items to be randomized, this method selects a random number n times.

each iteration is a row in the "List n" table

The first row starts with 0 thru n-1 items

each subsequent row removes the item randomly selected from the previous row until there is only one item left.

Here is the link again:

https://www.dropbox.com/s/a296hf6hhzox3ns/RandomLists.numbers?dl=0

Hi Wayne,

Thanks for posting the document. So, if I understand correctly, yours is random selection without replacement whereas others (at least mine) are random selection with replacement before another selection is made.

I'm saving the document and meanwhile am going to add this thread, including the OP's goal-post shifting and subsequent "radio silence," to "that long list of things in life that I don't quite understand."🙂

SG

Germanos wrote:

the correct sheet is the 1st one, right?

If you're referring to the document I posted to Dropbox and linked to upthread then you can use either the 'Lessons' sheet or the 'Lessons - extra table for pasting' sheet. Probably using the sheet with the extra table is better (see last question below).

2. Can the "exclude list" be bigger? I need only to add more rows in "Exclude" table?

Yes, just add row(s) to the Exclude table and on each row put a lesson number in column A and a day you want to exclude that lesson in column B.

3. How can I know that this program will generate a solution for sure?

It won't find a solution if the Min for readings is set too high or you have too many exclude days. The only way to know "for sure" is to click the checkbox a few dozen times. If there's still red in the cells, the chances are there isn't a solution.

Is there a way to stop recalculate the numbers in case of finding the correct combination?

That was the idea behind the second sheet 'Lessons - extra table for pasting', implementing a suggestion by Barry. After you've clicked and clicked until the cells are all green, select the cells within the dotted lines in the 'Selector' table, command-c to copy, click once in B3 of 'Πίνακας 1', and Edit > Paste Formula Results. That way the values in your main table won't change when you make other changes in your document.

As Barry notes, this approach is prone to some repetition of lesson combinations during a 30-day period. Perhaps the most practical way to deal with that is to inspect the solution and click some more if you see too much repetition. (I also have a script solution that minimizes repetition and will post it later).

SG

Germanos wrote:

What about if I want each lesson to have a STANDARD repetition? Not default (eg 5) but each of them to have its (eg 1st:5, 2nd:6) and not minimum. Exactly that number. Is there a solution?

In the 'Minimum readings check' table you can easily set a different minimum for each lesson. Just change the relevant value in column C.

You could could make the table check for an exact value (instead of a minimum) by changing the formula in column C from:

=IF(C2<B2,"not ok","ok")

to:

=IF(C2<>B2,"not ok","ok")

But you almost certainly will have red in some of the cells and will have to make a judgment whether you want to accept a schedule that exceeds the minimum for some lessons.

SG

Germanos wrote:

I did it already.

But I wonder if there is another way e.g. to counter in every step each lesson's counter and prohibit in next random pair to produce a lesson which had already reach its desirable counter value...

I'm sure there is a way, especially with a script that loops through random selections and accepts or rejects each selection. Meanwhile, you've got a number of viable solutions here for your original question (actually series of questions, each different from the previous).

SG

HI Manos,

I see the goalposts have migrated again. Some comments on this week's version of the question.

"What about if I want each lesson to have a STANDARD repetition? Not default (eg 5) but each of them to have its (eg 1st:5, 2nd:6) and not minimum. Exactly that number. Is there a solution?"

It depends on how many of the lesson you set these limits on, and what the limits are.

Evenly spread, each lesson will occur either seven or eight times in the sixty slots available.

If each every lesson is to have a STANDARD repetition, the sum of those STANDARD repetition numbers must be 60.

Finding 30 pairs to fit that requirement from the set of 56 possible permutations listed in SG's solution (and generated by his most recent script when the current parameters are set—8 lessons, (all) ordered pairs listed) will be close to impossible, I suspect.

As each repetition number is met, all unchosen pairs containing that lesson number would be blocked.

For example:, if the first five pairs containing 1 were picked 12, 13, 14, 15, 16 then the remaining pairs containing 1 (17, 18, 21, 31, 41, 51, 61, 71, 81) would be blocked, one pair containing a 2 is included in the pairs already picked, so there are five to add. 23, 24, 25, 26, 27, and several more to block: 28, 32, 42, 52, 62, 72, 82.

With only two numbers reaching their limit, there are 10 of the thirty slots filled, and the pool has been reduced by 26, leaving 30 pairs to fill the remaining 20 slots. Not necessarily "impossible," but still 'close to' impossible.

"I wonder if there is another way e.g. to counter in every step each lesson's counter and prohibit in next random pair to produce a lesson which had already reach its desirable counter value..."

The counters shown in SG's solution and in mine show the results after all calculations are completed. It might be possible to build the eight counters into eight columns, one for each lesson, and to write the formulas inserting the pairs to attend to the counts of each lesson in the current row and control the insertion of a random example from the reduced pool based on those counts, but I suspect the formula would be more complicated than this sentence.

Might be possible in a script, though, provided the issue noted regarding setting STANDARD limits turns out to be a non-issue.

You wrote: "(SGIII's) solution (and the thought to use all the available combinations) is the nearest one!"

Actually, it was my solution that used "all available combinations"; SG's chose from a list of all possible permutations.

There are 28 possible combinations of two items from a set of eight. Because each combination may be placed in either of two orders, there are 56 (twice as many) permutations.

Regards,

Barry

Hi SG,

Nice script. It works well with Numbers 2, as it should, given that it's only external action is to place the result on the clipboard. From there, it's the user who determines where to paste the result and starts the actual paste.

I notice that it's based on an earlier placement of the goal posts, where the repetition counts were read by both of us (and others, I suspect) as minimums. As discussed above, I think that placement may turn out to provide an unsolvable puzzle, depending what values are set for the STANDARD repetition for each of the eight lessons.

Regards,

Barry

Hiroto wrote:

Have you really tried my scripting solution? It will address all of your requirements including the new.

Hi H,

I haven't tried your script for two reasons.

First, without instructions on how to run it, it's tough to run it. With AppleScript, you can just copy-paste into Script Editor and click the 'run' button. With your script, there seems more is needed to get it working.

Second, I see in your results what appear to be gaps, when the result needed is a column with days 1 through 30 with two lessons listed for each day in adjacent columns.

On whether yours or the other solutions meet all the requirements including the new, I tend to agree with Barry: if I understand the problem correctly, with the latest constraint (a exact number of times rather than a minimum number of times) there are many possibilities here for an "unsolvable puzzle."

Just a heads up: here I can't see your second image (f4.png) in your latest post. The forum software may be acting up again.

SG

Barry wrote:

Actually, it was my solution that used "all available combinations"; SG's chose from a list of all possible permutations.

There are 28 possible combinations of two items from a set of eight. Because each combination may be placed in either of two orders, there are 56 (twice as many) permutations.

Hi Barry,

Aha, your post set me on a quest to refresh some hazy math vocabulary, which resulted in my finding this explanation. Thanks for that.

So in my spreadsheet solution, if what one really wants is permutations (where order matters) one would need to delete the relevant rows in the 'Pairs' table.

In the AppleScript, one would need to use the "commented out" line in the conditional, e.g. after "if want to lesson i,j to be treated as repetition of j,i ... "

Happy combinations and permutations in 2016, everybody!

SG