How do I make a random selection in a spreadsheet using more than one criteria

Question

I have a spreadsheet created in OpenOffice (V3.3.0 in Windows 7 32 bit) that I use as a database of my DVD collection. After asking a question in SuperUser, I have it so it can pop up a random title from my collection for the times I can’t make up my mind what to watch. It uses the following formula:

=INDIRECT("A"&RANDBETWEEN(2;COUNTA(A1:A1048576)))

This selects a random value between the start and end of my DVD’s and displays the name. This works fine but I now want to expand on it to make it a bit more sophisticated.

Every time I watch a DVD, I mark down the date in a column in the spreadsheet. I now want the random selection to pick one that I haven’t seen in, say, the last six months.

I may expand on this in the future to, for example, select a random one that I haven’t seen in the last 6 months AND is a comedy (I also have a column of the film’s genre).

Any pointers on how to do this will be gratefully received.

Answer 1

You need to now add another column containing the date you last watched said movie, as well as the genre. I don't know how to automate this off the top of my head, but you can keep re-calculating the formula until a random selection is found.

What you need to do is add a few IF clauses to see if the selected movie matches the genre you want, and if the last play date exceeds a certain date from now.

To do this, what I would do is enter my criteria in a few cells, and store a random cell's location in another one. Then, you can do something like:

=IF((Last Watched - Today) > 6 Months, IF(Genre = Selected Genre, TITLE))

To get these values, you can use the OFFSET function to offset your INDIRECT by a certain amount of rows/columns. Basically, you would replace the values I put in that formula with a new OFFSET value. The only caveats to this method is that you're not guaranteed to get a title each time - you may have to keep re-calculating the random cell until it finds one that matches your criteria (I don't know how to automate this off the top of my head without macros).

---

Just for more details, let's say you put the random cell containing a DVD's title in cell A1. You could replace the TITLE in the previous formula with INDIRECT(A1). If the genre is in the column beside it, replace it with OFFSET(INDIRECT(A1), 0, 1). You can then update just the random value in cell A1, and it will match against your criteria automatically. Furthermore, you can set your criteria in a number of static cells, so you could update that on-the-fly as well.

Answer 2

1. First, filter the list of DVDs into a smaller list of "Haven't watched in a while", using similar conditionals as Breaththrough suggests: something like =FILTER(A1:A1048576, (Last Watched - Today) > 6 Months, (Genre = Selected Genre, TITLE))
2. Then apply your existing random selector function — =INDIRECT("B"&RANDBETWEEN(2;COUNTA(B1:B1048576))) , changed to column B (the new filtered list) — to find just one movie to watch.