Forum Discussion

archifaust's avatar
archifaust
Copper Contributor
Mar 02, 2024

RANDARRAY with equal spread of numbers

I need to generate a RANDARRAY that is 17 rows and 14 columns using only numbers 1 thru 5... I can do this using =RANDARRAY(17, 14, 1, 5, TRUE) but I need it to generate with a near equal number of each of the numbers 1-5. Any thoughts?

excel
Excel on Mac
Formulas and Functions

1 Reply

  • djclements's avatar
    djclements
    Silver Contributor
    Mar 02, 2024

    archifaust Perhaps something like this:

     

    =WRAPROWS(TAKE(SORTBY(TOCOL(IF(SEQUENCE(48), SEQUENCE(,5))), RANDARRAY(240)), 238), 14)

     

    The TOCOL / IF / SEQUENCE combo is used to repeat numbers 1 thru 5, 48 times, in a single column. SORTBY is used to randomize the results with the RANDARRAY function, then TAKE ensures the correct number of items are passed to the WRAPROWS function to generate the 17x14 array.

Resources