Random number generator: how to eliminate zero?

EdNerd

Active Member
I am trying to generate a set of five random numbers, values from 1 to 5. Currently, I am using the formula
=CHOOSE(RANDBETWEEN(1,5),\$L\$2,\$L\$3,\$L\$4,\$L\$5,\$L\$6)
where L2 to L6 contain the numbers 1 through 5.

Given that a number could appear multiple times, I'd like to eliminate that number after the second time it appears. So the third random number would be drawn from a set of numbers that only appear 0 or 1 times. So down column M is =IF(COUNTIF(B\$2:B\$6,L2)<2,COUNTIF(B\$2:B\$6,L2),0).

Unfortunately, this introduces a 0 into my number set, and I can't use a 0.
Is there any way to do this, but eliminating any 0s that occur?

Ed

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

JamesCanale

Well-known Member
MrExcelPlayground6.xlsx
ABCD
210.2478057311
320.595795333
430.7104992344
540.6094147622
650.3974493255
710.80454496
820.05852595
930.31153881
1040.30488403
1150.33617329
Sheet2
Cell Formulas
RangeFormula
C2:C6C2=INDEX(A2:A11,MATCH(LARGE(B2:B11,SEQUENCE(5)),B2:B11,0))
D2D2=INDEX(\$A\$2:\$A\$11,MATCH(LARGE(\$B\$2:\$B\$11,1),\$B\$2:\$B\$11,0))
D3D3=INDEX(\$A\$2:\$A\$11,MATCH(LARGE(\$B\$2:\$B\$11,2),\$B\$2:\$B\$11,0))
D4D4=INDEX(\$A\$2:\$A\$11,MATCH(LARGE(\$B\$2:\$B\$11,3),\$B\$2:\$B\$11,0))
D5D5=INDEX(\$A\$2:\$A\$11,MATCH(LARGE(\$B\$2:\$B\$11,4),\$B\$2:\$B\$11,0))
D6D6=INDEX(\$A\$2:\$A\$11,MATCH(LARGE(\$B\$2:\$B\$11,5),\$B\$2:\$B\$11,0))
B2:B11B2=RAND()
Dynamic array formulas.

Eric W

MrExcel MVP
Another way:

Book1
ABL
1
21311
31512
41513
51114
61415
Sheet3
Cell Formulas
RangeFormula
B2:B6B2=INDEX(\$L\$2:\$L\$6,AGGREGATE(15,6,{1;2;3;4;5}*{1,1}/(COUNTIF(\$B\$1:\$B1,\$L\$2:\$L\$6)<{1,2}),RANDBETWEEN(1,11-ROWS(\$B\$2:\$B2))))

This is specific to a set of 5 numbers, but it's easy enough to adapt for arbitrary set sizes at the expense of making the formula longer.

EdNerd

Active Member
Another way:

This is specific to a set of 5 numbers, but it's easy enough to adapt for arbitrary set sizes at the expense of making the formula longer.
Thanks for your input, Eric. I could not figure out how to implement this, though. The solution from James works well, so I will use that.

EdNerd

Active Member
MrExcelPlayground6.xlsx
ABCD
210.2478057311
320.595795333
430.7104992344
540.6094147622
650.3974493255
710.80454496
820.05852595
930.31153881
1040.30488403
1150.33617329
Sheet2
Cell Formulas
RangeFormula
C2:C6C2=INDEX(A2:A11,MATCH(LARGE(B2:B11,SEQUENCE(5)),B2:B11,0))
D2D2=INDEX(\$A\$2:\$A\$11,MATCH(LARGE(\$B\$2:\$B\$11,1),\$B\$2:\$B\$11,0))
D3D3=INDEX(\$A\$2:\$A\$11,MATCH(LARGE(\$B\$2:\$B\$11,2),\$B\$2:\$B\$11,0))
D4D4=INDEX(\$A\$2:\$A\$11,MATCH(LARGE(\$B\$2:\$B\$11,3),\$B\$2:\$B\$11,0))
D5D5=INDEX(\$A\$2:\$A\$11,MATCH(LARGE(\$B\$2:\$B\$11,4),\$B\$2:\$B\$11,0))
D6D6=INDEX(\$A\$2:\$A\$11,MATCH(LARGE(\$B\$2:\$B\$11,5),\$B\$2:\$B\$11,0))
B2:B11B2=RAND()
Dynamic array formulas.

Thank you, James. I forgot to mention I'm using XL2016, not 365 - sorry to make you do double work!

The solution you provided works well. But I am having a bit of trouble working out how it works.
Can you please explain a bit how you came up with this?

JamesCanale

Well-known Member
By putting random numbers next to two sets of 1-5 - by using the "large" function, I basically get them sorted randomly. But since there are only two sets of 1-5, I can't get anything three times. So for any giving 'drawing' I take the five largest random numbers - but really the 1-5 that corresponds to the those. And I just take the top 5. It would work just as well taking the top 5 "SMALL" ones too.

EdNerd

Active Member
By putting random numbers next to two sets of 1-5 - by using the "large" function, I basically get them sorted randomly. But since there are only two sets of 1-5, I can't get anything three times. So for any giving 'drawing' I take the five largest random numbers - but really the 1-5 that corresponds to the those. And I just take the top 5. It would work just as well taking the top 5 "SMALL" ones too.
Ah!! That makes sense. Thank you for the explanation - I never knew you could do something like that in a formula.

Replies
2
Views
123
Replies
0
Views
95
Replies
41
Views
827
Replies
3
Views
92
Replies
6
Views
251

1,191,623
Messages
5,987,740
Members
440,106
Latest member
davcurnutt

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back