Random number generator: how to eliminate zero?

EdNerd

Active Member
Joined
May 19, 2011
Messages
456
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

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Try this instead:
MrExcelPlayground6.xlsx
ABCD
1Off365 Answerolder excel
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.
 
Upvote 0
Solution
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.
 
Upvote 0
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.
 
Upvote 0
Try this instead:
MrExcelPlayground6.xlsx
ABCD
1Off365 Answerolder excel
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top