3

I have a set of cells with values:

A1,B3,C5,D7,E11,F13,G17,H19,I23

I have a formula to select, at random, the value stored in one of these cells:

=CHOOSE(RANDBETWEEN(1,9),A1,B3,C5,D7,E11,F13,G17,H19,I23)

The formula works fine. Now I have assigned a Defined Name to this set of cells - MyPicks

How can I change the formula to use the Defined Name rather than the hard-coded list ??

EDIT#1:

I tried to make the group of cell contiguous by using A1,A2,A3 and sure enough:

=CHOOSE(RANDBETWEEN(1,3),A1,A2,A3)

but =CHOOSE(RANDBETWEEN(1,3),contig)

fails the same way with contig defined as the Name of cells A1,A2,A3:

enter image description here

Improve this question
9
  • What happens when you use the Defined Name in the formula; =CHOOSE(RANDBETWEEN(1,9), MyPicks)?
    – CharlieRB
    Commented Mar 10, 2015 at 15:50
  • 1
    Interesting, you can't even =index(name,number) unless the named range is contiguous.
    – Raystafarian
    Commented Mar 10, 2015 at 16:30
  • 1
    If you have contiguous just use something like =INDEX(named2,,RANDBETWEEN(1,5)) adjusting for a vertical range rather than horizontal
    – Raystafarian
    Commented Mar 10, 2015 at 17:12
  • 1
    @Raystafarian ........THANK You ...........THANK YOU .......THANK YOU.
    – Gary's Student
    Commented Mar 10, 2015 at 17:13
  • 1
    The reason CHOOSE isn't working is because it's seeing the named range as a single value instead of a collection of cells each having their own value. If you use =CHOOSE(1,MyPicks) then it works fine - although it only returns the value from the first cell - but =CHOOSE(2,MyPicks) fails because there isn't a second value for CHOOSE to return.
    – Engineer Toast
    Commented Mar 10, 2015 at 21:27

1 Answer 1

Reset to default
2

Some functions will accept a discontiguous range as an argument, e.g. FREQUENCY, RANK, SMALL, LARGE.

In this case you could use one of those latter two, e.g.

=SMALL(MyPicks,RANDBETWEEN(1,9))

If the size of MyPicks might be variable, or some cells may not be populated you could make the RANDBETWEEN part depend on the number of values in MyPicks, i.e.

=SMALL(MyPicks,RANDBETWEEN(1,COUNT(MyPicks)))

Note: the functions listed will accept a named discontiguous range as above.....or you can use the specific cells directly as a union - cell references separated by commas and bracketed together, e.g.

=SMALL((A1,B3,C5,D7,E11,F13,G17,H19,I23),RANDBETWEEN(1,9))

Improve this answer
1
  • Thanks!.........this exactly achieves my goal without having to fight with CHOOSE()
    – Gary's Student
    Commented Mar 10, 2015 at 23:52

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .