Forum Discussion
Generating Random Number, Excluding Cell in Next Calculation
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.
I think IngeborgHawighorst will have used the RANK function to rank each instance of a random number within the list. This will generate values 1-46 that are used as the index (row number) parameter within the INDEX function.
I am puzzled by the "@" symbol that appears before the INDEX function in the formula bar (blocking an array calculation to return a single value). Who is it that is using an up-to-the-minute version of Office 365?
[Just checked back; it was Ingeborg's post that included the symbol]
PeterBartholomew1 Yes, I use Office Insider, which already includes the new Dynamic Array functions. It's sometimes hard to write functions the old way, because things now just work. The backwards compatibility is achieved with the @ symbol that tells a function not to use the new array way of things, so it is backwards compatible with older versions of Excel. It will show in the screenshot, but not if you open an Excel file.
Wow! Thank you so much!!!IngeborgHawighorst
