=CHOOSE(RANDBETWEEN(2,23),$J$57:$AF$57)

[febausa]

I used the following formula

=CHOOSE(RANDBETWEEN(2,23),$J$57:$AF$57) but the formula show error #VALUE! . I try fix the error but I unable fix the error.

 

[Chihiro]

You can't use range in 2nd argument of CHOOSE. You have to separate it by comma. Or use other methods.

What is it that you are trying to accomplish with the formula?

 

[NARAYANK991]

Hi ,

Try this :

=INDEX($J$57:$AF$57 , RANDBETWEEN(2,23))

Narayan

 

[febausa]

Hi ,

Try this :

=INDEX($J$57:$AF$57 , RANDBETWEEN(2,23))

Narayan

Thank you Narayan very good formula

 

[febausa]

Hi ,

Try this :

=INDEX($J$57:$AF$57 , RANDBETWEEN(2,23))

Narayan

Hi Narayan:

I like this formula, but I want include no repetition numbers in result.

Thanks,

FEBAUSA

 

[Chihiro]

How many numbers do you need to generate?

And should it include all numbers between 2 to 23?

If 22 number should be generated between 2 to 23...
Then you will need helper column(s).

Ex: In Cell A1 to A22 enter =RAND()
In Cell B1 to B22 enter =RANK(A1,$A$1$A$22)+1

Column B will have list of unique numbers between 2 to 23 in random order.

Note: While it is EXTREMELY unlikely that RAND() will produce identical number. There is still a possibility. Without knowing your full requirement, it's bit difficult to give best fit solution.

 

[NARAYANK991]

Hi Narayan:

I like this formula, but I want include no repetition numbers in result.

Thanks,

FEBAUSA

Hi ,

To ensure there are no repetitions :

Each random number which is generated will have to be stored somewhere , so that the next random number which is generated can be different from the random numbers which are already in the list ; I doubt that a formula can both do this storage and return the original numbers using the INDEX function.

VBA can certainly do the job.

Narayan

 

[febausa]

Hi ,

To ensure there are no repetitions :

Each random number which is generated will have to be stored somewhere , so that the next random number which is generated can be different from the random numbers which are already in the list ; I doubt that a formula can both do this storage and return the original numbers using the INDEX function.

VBA can certainly do the job.

Narayan

Hi Narayan

I attach example (excel file) for make VBA see the TEXTBOX with explanations.

Thank you for your help and time,

Best regards,

FEBAUSA

 

[NARAYANK991]

Hi ,

See the attached file ; run the macro named ProcessData.

Narayan