Distribute/Divide A Number Between 10 Rows Randomly

**mlyth** · 05-10-2018, 06:52 AM

Basically I have 10 rows and I want to distribute numbers/grades between these rows randomly but each cell has to be smaller or equal to 10, how can I do that wtihout macros?

**CK76** · 05-10-2018, 07:10 AM

Does the numbers need to be unique?

If not.
=RANDBETWEEN(1,10)
Copy down.

If yes.
Use two columns.
First column: =RAND()
Second column: = RANK(A1,$A$1:$A$10)

There's very small chance of duplicate with above... to avoid it just add row# divided by large number.
Ex: =Rand()+ROW()/10^9

**mlyth** · 05-10-2018, 07:14 AM

Thanks for the answer but I think I have to restrict these cells to get randomised again.

Because;

RANDBETWEEN works but everytime I edit a cell, all rows and cells randoms itself again.

**CK76** · 05-10-2018, 07:28 AM

If you need it to be static... easiest method is to use VBA to fill cells with random number.

Ex:

Please Login or Register  to view this content.

**CK76** · 05-10-2018, 07:40 AM

One other method.

1. Make sure that you turn on Iterative Calculation. Using options.
2. Enter following formula in A1 and copy down.
=IF($F$1<>"",IF(A1="",RANDBETWEEN(1,10),A1),"")

3. Enter any value in F1 and it will generate numbers in column A.
4. Clear F1 and enter value again to reset.

See attached.