Forum Discussion

Lou_Ann1953's avatar
Lou_Ann1953
Copper Contributor
Jan 09, 2022

Excel Rand

I am trying to select random numbers between 1 & 39 using this formula =RANDARRAY(6,3,1,39,TRUE)

I keep getting duplicate numbers.  How do I fix this?

excel

1 Reply

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Jan 09, 2022
    Lou_Ann1953 RANDARRY does no more than generate an array of random numbers. When you choose [Integer=TRUE] as the output type, the chance of duplication is eminent. Use FALSE in stead. That will produce random numbers with a precision of 14 decimals. Chance for duplication becomes minimal.
     
    First generate a list of 39 random numbers. Then fix these by copy/paste values. Now rank them and pick the first 18 ranking numbers (i.e. 6 x 3) to generate your output array of 6 rows, 3 columns with unique random numbers between 1 and 39.
     
    Example file attached.

Resources