Forum Discussion
Generating Random Number, Excluding Cell in Next Calculation
Hello.
I currently have a column of data that I need to select a random number from. I have the correct formula for that. The issue is that I need to exclude the cell that was "picked" in the previous formula from the next result I need to generate (when I drag the formula down). Here is the formula I am using to generate the random number: =INDEX($B2:$B47,RANDBETWEEN(1,COUNTA($B2:$B47)),1).
Here's an example if this is hard to understand. Cell A1= 3, Cell A2=4, Cell A3=5, Cell A4=6. My formula in B1 chose the number 4. Now, in B2, I need a number that excludes cell A2 (4). Let's say the next number is 3. Now in B3, I need a formula that generates a random number but excludes both A1 and A2. If anyone can help, please do!
9 Replies
This is probably jumping the gun somewhat because dynamic arrays are only just starting to be released to Office 365 users. DAs allow the same strategy of generating a set of random numbers and using their sort order that was used by IngeborgHawighorst to be applied directly to the list of values without the use of a helper range or index lookup.
= SORTBY( ValueList, RANDARRAY(COUNT(ValueList)) )
Hello John_M2205 ,
what is the expected result? What does the formula calculate? How does "excluding" look like, compared to not excluding? Maybe you can create a small data sample and upload the workbook here.
Here is my example! The expected result is a return from the S&P 500 from a previous year. Once that return is generated, I do not want it generated again once I drag the formula down. For example, the first number generated from the collection of data (column b) was 1.06. Since that number was generated, I do not want that number to be considered when I generate the next random number. Thanks for your response and I appreciate any feedback!IngeborgHawighorst
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.
