Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- 393K Members
- 6,888 Online
- 424K Conversations

- Home
- :
- Excel
- :
- General Discussion
- :
- Generating Random Number, Excluding Cell in Next Calculation

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Home
- :
- Excel
- :
- General Discussion
- :
- Generating Random Number, Excluding Cell in Next Calculation

Conversation Options

- Subscribe to RSS Feed
- Mark Conversation as New
- Mark Conversation as Read
- Pin this Conversation for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-01-2019 11:36 AM

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!

Labels:

9 Replies

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-01-2019 01:31 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-02-2019 08:37 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-04-2019 02:19 PM - edited 08-04-2019 02:29 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-05-2019 01:48 AM

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)) )**

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-05-2019 09:27 AM

Wow! Thank you so much!!!@Ingeborg Hawighorst

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-05-2019 10:24 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-05-2019 02:07 PM - edited 08-05-2019 02:11 PM

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]

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-08-2019 02:05 PM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-08-2019 02:08 PM

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

Related Conversations

How to Prevent Teams from Auto-Launch

chenrylee
in
Microsoft Teams
on
06-27-2019
86K
Views

4 Likes

28 Replies

Early preview of Microsoft Edge group policies

Sean Lyndersay
in
Discussions
on
06-14-2019
17.9K
Views

20 Likes

65 Replies

*Updated 9/3* Syncing in Microsoft Edge Preview Channels

Elliot Kirk
in
Articles
on
05-01-2019
34.1K
Views

23 Likes

201 Replies

How to download windows server 2019 update to 1903

Cmakar37
in
Windows Server for IT Pro
on
07-03-2019
10.3K
Views

0 Likes

5 Replies

This form can't be distributed as it is asking for personal or sensitive information

NZLaSalle
in
Microsoft Forms
on
07-19-2019
5,343
Views

4 Likes

56 Replies

$cred = Get-Credential without asking for prompts in powershell

TejCGS
in
Office 365
on
04-25-2019
18.6K
Views

0 Likes

6 Replies

Share

Popular

Learning Resources

Programs

Values

- Contact us
- Privacy & cookies
- Terms of use
- Trademarks
- About our ads
- © 2017 Microsoft