Function to select random values from a list.

[Guest]

Hello

I have been fiddling about trying to construct a fairly simple spreadsheet -
but have so far been unable to find an elegant function to carry out a fairly
simple task.

Basically, I am trying to build a "Flash Card" type worksheet for maths
problems (multiplication, addition, subtraction etc.). The idea is that the
spreadsheet will create random maths problems (e.g. 4 x 7, 6 x 8 etc.) This
is meant to be some fun for my daughter as she practices her times tables.

The complication / refinement that I want to add is that I want to be able
to identify which times tables ( 1 - 12) are to be included in the test (for
instance, we have not done the 7 and 8 times tables yet and so I do not want
to include these tables in the list of randomly generated questions yet). So
far I have just listed the digits 1 - 12 is separate rows and put a "Y"
character in the adjacent box next to each digit to indicate whether I want
that number included in the list of values to used to generate questions -
i.e. to create a shortlist.

The problem is - how do I create a function that randomly selects from this
shortlist of values?

I tried using the CHOOSE function, but to no joy?

Any thoughts anyone?

Thanks in advance.

 

[vezerid]

Let us say you have the numbers 1-12 in cells A1:A12. Let us say in
B1:B12 you tick with "y" those numbers that you have already used. The
following formula will produce a random number from those *unticked*
(without a "y" next to them).

=INDEX(SMALL(IF($B$1:$B$12<>"y",ROW($A$1:$A$12)),ROW($A$1:$A$12)),INT(RAND()*(12-COUNTIF($B$1:$B$12,"y")))+1)

This formula needs to be array-entered, use Ctrl+Shift+Enter to enter
it.

If we call the above formula _form_, you can now build the bigger
formula:

=_form_ & " x " & _form_

to produce a problem to be solved by daughter. Every time you press F9
a new combination will come up.

HTH
Kostis Vezerides