Getting RANDBETWEEN to generate all numbers between x and y

Excel_VBA

New Member
Joined
Dec 19, 2009
Messages
42
Let's say I am using RANDBETWEEN (1,245). When I use this, although random numbers are generated, there are some numbers that do not appear between 1 and 245.

Is it possible to randomly generate numbers between x and y, but make all numbers between X and Y appear in any order?
 
How about creating a column with the values of 1 through 245, then a helper column with =RAND() dragged down, and sort on the second column? Sorting makes the volatile function RAND recalculate, so you'll get a different order each time.
 
Upvote 0
Let's say I am using RANDBETWEEN (1,245). When I use this, although random numbers are generated, there are some numbers that do not appear between 1 and 245.

Is it possible to randomly generate numbers between x and y, but make all numbers between X and Y appear in any order?

A1: 245

A2, just enter and copy down:
Rich (BB code):
=IF(ROWS($A$2:A2)<=$A$1,RAND(),"")
B2, just enter and copy down:
Rich (BB code):
=IF($A2="","",RANK($A2,$A$2:INDEX(A:A,$A$1+ROW($A$1))))
B1, control+shift+enter, not just enter:
Rich (BB code):
="All distinct? "&IF(SUM(IF(FREQUENCY($A$2:INDEX(A:A,$A$1+ROW($A$1)),
  $A$2:INDEX(A:A,$A$1+ROW($A$1))),1))=$A$1,"Yes","No")
for a fancy diagnostic.
 
Upvote 0
How about creating a column with the values of 1 through 245, then a helper column with =RAND() dragged down, and sort on the second column? Sorting makes the volatile function RAND recalculate, so you'll get a different order each time.

Thank you to both of you. It works!
 
Upvote 0
No problem :) Glad I could help :beerchug: Appreciate the feedback!
 
Upvote 0

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