0

I'm trying to create a table of random values that correspond to priority of a choice on a website. Basically trying to preemptively do some guess work here.

Essentially what I want to do is know how to generate a random number between 1-4 where I can't get the same number twice, in the row. Preferably not volatile, but i can handle it if it recalculates every time I calculate.

For example I want to generate a value between 1-4, over 4 columns, so one column would have 1, one would have 3, one would have 2, and one would have 4.

I've tried the Randbetween() function, but sadly, that allows for repeats. If there was a thing like Distinct(Randbetween()), that would solve my problem.

Any suggestions? Cheers.

3
  • The various random function to the best of my knowledge are all Volatile functions. Reading your question is it safe to assume your are only looking at integers? ie no 1.5 results allowed? – Forward Ed Aug 29 '19 at 18:39
  • Yeah correct, I'm looking at integers. I just need it to be unique per row, ex: generate a value between 1 -4 over 4 columns. cell a2 is 2, cell a3 is 3, cell a4 is 4, cell a5 is 1. aplogies about the typo in the question will edit that now. – Dan Council Aug 29 '19 at 18:46
  • is a helper row acceptable? Kind of need it for referencing in subsequent part of formula as nesting several randoms will not give the same choice as the preivous random generation – Forward Ed Aug 29 '19 at 19:07
0

If you're willing to stick with your range of one through four, you can do it by setting up all 24 options in a helper array, and then selecting one option randomly.

On Sheet2, in Cells A1 through B24, place the following:

1   1234
2   1243
3   1324
4   1342
5   1423
6   1432
7   2134
8   2143
9   2314
10  2341
11  2413
12  2431
13  3124
14  3142
15  3214
16  3241
17  3412
18  3421
19  4123
20  4132
21  4213
22  4231
23  4312
24  4321

That's the 24 possible permutations of 1234, which is all of the possible ways you can select the numbers 1 through 4 without replacement.

Then, go back to Sheet1. In A1, put this formula:

=INT(RAND()*24)+1

(I did that because I'm working with an old enough version of Excel that I don't have the RANDBETWEEN function. But you can also use =RANDBETWEEN(1,24).)

Then, in B1, put this:

=VLOOKUP(A1,Sheet2!A$1:B$24,2,FALSE)

That will look in the array on Sheet2. It'll look in the first column for whatever value is in A1 (the random number between 1 and 24), and give you the value in the second column (1234, 1243, 1324, etc.).

Then, in C1 through F1, put these:

C1:   =MID($B1,1,1)
D1:   =MID($B1,2,1)
E1:   =MID($B1,3,1)
F1:   =MID($B1,4,1)

That will break apart that four-digit number in B1 into it's component parts. So now, C1 through F1 will have the numbers 1 through 4, in random order, without duplication.

Caveat

This doesn't scale very well. Your helper array on Sheet2 will need to have N! rows, where N is the number of values you want. And that grows fast. If you want random numbers between 1 and 7, you'll need 5,040 rows in your helper array. But if you just want to do this for random numbers between 1 and 4, it's manageable.

1
  • This works PERFECT for my situation. thank you so much. I see what you mean about the lack of scalability for this, but it generates the pre-emptive test data I need. Thanks so much. You saved me a lot of headache on this holiday weekend – Dan Council Aug 30 '19 at 13:44
1

Random functions, by design, can produce repeats -- or they would not be considered random. What you need is a randomly sorted list of numbers. One easy way to produce numbers from a list in random order is to use an array. For example, to produce numbers 1 through 13 for value of cards in a deck in non-repeating random sequence:

  1. Create an array, 1.. 13, of numbers. (OK, 0.. 12 for a purist ;-)
  2. Insert the item number in each place in the array, so the array now holds values 1 through 13 in sequence.
  3. Now, stepping through the array, switch each value with that of a random item, 1.. 13.

The array will now hold all 13 numbers, but in random order... none missing or repeated.

This can be done with a very small macro.

0

With the assistance of a helper row and with the realization that this is only 4 numbers without a repeat, you could use one of the two following NOT SO ELEGANT solutions:

In A1 place the following formula and copy it to the left for 4 columns (end in D1):

=RANDBETWEEN(1,4-(COLUMN(A1)-1))

Basically it will create a decreasing range of random numbers. It follows the principal that if you have a bag of 4 balls and you pull one out, then the next time you go to pull a ball there is 1 less to choose from so only 3 then 2 then 1 then 0. It does not address the the issue repeats. The other aspect of why this is done is once the volatile results are on the page they will not recalculate until something on the page/sheet changes. This means they are static for other formulas to reference until a recalculation occurs.

The next not so elegant step is to check for repeats. When a repeat occurs you need to add 1. This is a check needs to be repeated after you add 1 in case the new number is also a repeat. So for a small number of integers to deal with it can be done in a couple of ways, but start getting much beyond 5 integers and the formula starts to get unruly.

Since the first digit cannot be a repeat, simply make the formula in A2 point at A1.

=A1

OPTION 1 - Checking Duplicates with IF

In B2 place the following formula and copy to the right:

=IF(COUNTIF($A$2:A2,B1)=1,IF(COUNTIF($A$2:A2,B1+1)=1,IF(COUNTIF($A$2:A2,B1+2)=1,3,2),1),0)+B1

now technically for B2 you only need part of the above formula, but as you copy to the right, or conversely as the number of integers your are dealing with increases, you need to nest an additional IF function. This formula goes the the ultimate case and save you from having essentially a custom formula in each column.

The custom formula pattern would look like the following from A2 to D2:

=A1
=B1+IF(COUNTIF($A$2:A2,B1)=1,1,0)
=C1+IF(COUNTIF($A$2:B2,C1)=1,IF(COUNTIF($A$2:B2,C1+1)=1,2,1),0)
=D1+IF(COUNTIF($A$2:C2,D1)=1,IF(COUNTIF($A$2:C2,D1+1)=1,IF(COUNTIF($A$2:C2,D1+2)=1,3,2),1),0)

OPTION 2 - Adding ALL true results

This solution is similar to the above, but it drops the IF and goes straight to the math portion. For demonstration purposes I placed the following equation in the third row. Again start column A by pointing it at A1 to get that initial value, then in B onward start looking at duplicates.

=B1+(COUNTIF($A$2:A2,B1)=1)+(COUNTIF($A$2:A2,B1)=1)*(COUNTIF($A$2:A2,B1+1)=1)+(COUNTIF($A$2:A2,B1)=1)*(COUNTIF($A$2:A2,B1+1)=1)*(COUNTIF($A$2:A2,B1+2)=1)

Again this is the ultimate case. notice the pattern thought. After each + a new countif is added and it was multiplied by the result of the result before the previous +. So the pattern from A3 to D3 is as follows if you wanted to go with custom formulas:

=A1
=B1+(COUNTIF($A$2:A2,B1)=1)
=C1+(COUNTIF($A$2:B2,C1)=1)+(COUNTIF($A$2:B2,C1)=1)*(COUNTIF($A$2:B2,C1+1)=1)
=D1+(COUNTIF($A$2:C2,D1)=1)+(COUNTIF($A$2:C2,D1)=1)*(COUNTIF($A$2:C2,D1+1)=1)+(COUNTIF($A$2:C2,D1)=1)*(COUNTIF($A$2:C2,D1+1)=1)*(COUNTIF($A$2:C2,D1+2)=1)

As you can see that formula is going to expand pretty quick as you add more integers. might start getting into number of characters allowed in a formula restriction pretty quick.

POC

0

You can use this Array (CSE) formula as one step solution:

enter image description here

  • Formula in Cell G2:

    {=LARGE(ROW($1:$15)*NOT(COUNTIF($G$1:G1, ROW($1:$15))), RANDBETWEEN(1,16-ROW(G1)))}
    
  • Finish formula with Ctrl+Shift+Enter & fill down.


enter image description here

  • Formula in Cell K2:

    =LARGE(ROW($1:$4)*NOT(COUNTIF($J$2:J2, ROW($1:$4))), RANDBETWEEN(1, 5-COLUMN(A1)))

  • Finish formula with Ctrl+Shift+Enter & fill right.

You may adjust Row & cell references in the formula as needed.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.