Trying to randomly choose a number from a set list of numbers

[RJTheDestroyer]

I'm trying to get Excel to randomly choose a number from a set of 58
different numbers using this formula:
=IF(AU2=1,CHOOSE(RANDBETWEEN(1,58),5,24,25,28,35,43,53,58,66,90,121,139,177,185,213,220,226,230,232,233,256,265,288,290,297,304,
310,314,336,178,179,180,181,182,183,184,214,215,216,217,218,219,
337,354,364,369,379,383,398,404,421,423,442,446,459,463,467,484)))

but it says "More arguments have been specified for this function than are
allowed in the current file format."

I'm wondering why it'd say this since the CHOOSE function can choose between
1 and 254 values. If anyone knows how I can get this to work properly,
please let me know (on a side note, it will let me only choose from up to 29
values and no more than that for some reason).

Thanks,

RJ

 

[Jacob Skaria]

Use the LOOKUP function instead..

=LOOKUP(RANDBETWEEN(1,10),
{1,2,3,4,5,6,7,8,9,10},{5,24,25,28,35,43,53,58,66,90})

If this post helps click Yes

 

[bosco_yip]

=IF(AU2=1,CHOOSE(RANDBETWEEN(1,58),5,24,25,28,35,43,53,58,66,90,121,139,177,185,213,220,226,230,232,233,256,265,288,290,297,304,310,314,336,178,179,180,181,182,183,184,214,215,216,217,218,219,337,354,364,369,379,383,398,404,421,423,442,446,459,463,467,484),"")

Regards
Bosco

 

[T. Valko]

The number of arguments is version dependent. In Excel 2007 you can have up
to 254 value arguments. In all other versions you're limited to 29 value
arguments.

I would list the values in a range of cells, say, A1:A58. Then:

=IF(AU2=1,INDEX(A1:A58,RANDBETWEEN(1,58)),"")

 

[RJTheDestroyer]

Thank you very much, this fixed my problem (I just had to add the value if
false rather than keeping it blank then?).

 

[RJTheDestroyer]

Thanks, this works very well too.

The number of arguments is version dependent. In Excel 2007 you can have up
to 254 value arguments. In all other versions you're limited to 29 value
arguments.

I would list the values in a range of cells, say, A1:A58. Then:

=IF(AU2=1,INDEX(A1:A58,RANDBETWEEN(1,58)),"")

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[Harlan Grove]

The number of arguments is version dependent. In Excel 2007 you can have up
to 254 value arguments. In all other versions you're limited to 29 value
arguments.

....

30 arguments, e.g., Excel 2003 correctly returns 465 for

=SUM
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30)

 

[T. Valko]

My reply was addressing the OP's question about using the CHOOSE function
and the number of arguments it takes.

but it says "More arguments have been specified for
this function than are allowed in the current file format."

I'm wondering why it'd say this since the CHOOSE
function can choose between 1 and 254 values.

The total number of arguments for the *CHOOSE* function is 30 in Excel
versions prior to Excel 2007. The number of *value arguments* is 29.