Generating Random Number, Excluding Cell in Next Calculation

Hi John_M2205 ,

 

You can do this with a helper table. You basically want to create a list of the 46 row numbers sorted randomly, which you can then feed into the Index function. To do this

 

- create a helper column with the row numbers from 1 to 46 on the same or a different sheet. 

- next to that column enter the =Rand() function and copy down. 

- turn these two columns into an Excel table

- sort the table by the column with the Rand function

- you now have a randomly sorted list of the unique row numbers

- point the Index function to the row number column of the table. 

 

=@INDEX($B$2:$B$47,Table1[@[unique numbers]])

 

Or, if you don't want to use structured referencing, you can use

 

=@INDEX($B$2:$B$47,F2)

 

Every time you sort the helper table, the Rand() function will recalculate, so if you want to shuffle your numbers, just sort the Rand() column. 

 

Your original formula counts the number of entries in column B, so you may have a varying number of rows. To cater for that, create as many row numbers in the helper as you will need for your largest possible data set. Then, instead of just the Rand() function, use this formula for the sort column:

 

=IF(COUNT(B:B)>=ROW()-1,RAND(),0)

 

This will generate random numbers only for the row numbers with content and create 0 values for the rest. Now you can sort from large to small to get the random order of the row numbers.