Hello. Is is possible, while using the RANDBETWEEN function in a single cell, to force Excel to use each number within the range once, before repeating a number?
Hello. Is is possible, while using the RANDBETWEEN function in a single cell, to force Excel to use each number within the range once, before repeating a number?
I like to be optimistic that most things are possible with enough time, effort, and ingenuity.
Are you required to use RANDBETWEEN() in a single cell to accomplish this task? It might be possible using RANDBETWEEN() in a single cell (I haven't spent enough time thinking about it that way), but I would tend to think about this problem differently and spread it out over several cells. I would tend to think about this as a "shuffle" problem where I want to shuffle the "deck" of 100 numbers into a random order. Here's how I would probably proceed (assuming you are allowed to approach the problem this way):
1) How many times through the "deck" of 100 will you need to go? Generate a row of 100 random numbers for each pass through the "deck" using the RAND() function. I entered =RAND() into CW1 and copied/pasted into CW1:GR20 (assuming 20 passes through the deck is enough).
2) I need to look at each row of random numbers and "sort" or "rank" each entry in the row to get my random sequence of numbers for 1 to 100. I can use the RANK() function for this. =RANK(CW1:$CW1:$GR1) into A1. Note the mix of relative and absolute references.
3) Copy A1 and Paste into A1:CV20
Reading A1:CV20 left to right and top to bottom, I should now have a sequence of random numbers 1 to 100, without duplication within a specified row.
I expect that that sequence of formulas will not do exactly what you want, but the overall concept is how I would generate this set of random numbers (shuffle the numbers 1 to 100 as many times as needed, then read/rank the numbers to get 1 to 100 in random order(s)). In order to help further, I would probably need to know something about how your spreadsheet will be setup to use these random numbers.
Originally Posted by shg
Thank you for the reply.
I like your description of a deck of cards. That's effectively what I'm hoping to replicate. Going through the entire deck, then reshuffling, then doing it again. I would probably need to go through the "deck" of 100 50+ times during use.
I'm designing a game using Excel as a helper file. The Excel part contains multiple "Virtual Dice" of 20 & 100 sided dice. These are represented by multiple cells (about 10-12) representing the different dice, which are used at different points in the game. So I'm debating keeping the results truly, completely random, OR shifting to more of a deck of cards idea as you've mentioned. I just don't know how to replicate the second option to compare the two and decide what's the best way to move forward. I hope that helps with understanding a bit more about what I'm aiming to do.
UPDATED: Oops! I noticed that when I originally was playing with this I was doing it for random numbers from 1-10. When I then did it for 1-100, I didn't update some of the formulas. I've now made it a little more dynamic so it should work no matter how many rows of data you have.
The newly attached file should give you an accurate list of random numbers between 1-100 without using the same number twice. (Column A is the list of random numbers).
OLD: I believe the attached does what you want. The first column shows random numbers from 1-100 without using the same number twice.
I started playing with the formulas so much that I'm sure you can re-do them, but they work and you can drag them to whatever number you want (they would need to be dragged down and across). While this may not be pretty, you can probably dress it up so it's more useful, but like I said, it works.
Last edited by Gregb11; 05-23-2020 at 07:58 PM. Reason: correction
If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.
Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks