Forum Discussion
Generating Random Number, Excluding Cell in Next Calculation
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.
I Have one more question. When you say, "sort the table with the Rand function" do you mean the following steps: click on a column, click "sort & filter", click "sort by random number", Sort on "Values". And then what order? I am not sure what you mean when you say to sort by the rand function. Thank you! IngeborgHawighorst
John_M2205 I meant: Sort the table manually, using the sort command on the column with the RAND function. Sort order does not really matter, unless you use the second technique where you have zero values in the column. In that case, sort descending.
Wow! Thank you so much!!!IngeborgHawighorst