Generating Random Number, Excluding Cell in Next Calculation

Copper Contributor

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

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!@Ingeborg Hawighorst 

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. 

2019-08-05_09-17-36.jpg

 

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.

 

 

@John_M2205 

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 @Ingeborg Hawighorst to be applied directly to the list of values without the use of a helper range or index lookup.

 

= SORTBY( ValueList, RANDARRAY(COUNT(ValueList)) )

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!  @Ingeborg Hawighorst 

@John_M2205 

I think @Ingeborg Hawighorst 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]

@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.

@Peter Bartholomew  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.