Random numbers in somewhat ascending order

speedbird168

New Member
Joined
Jun 24, 2013
Messages
4
I have a random set of numbers 1-100 in a column which I generated with =rankbetween() and shuffle with =rank(). How can I maintain a degree of random but create a degree of ascending order (ie. 2,8,1, 9,6,12,11...88,100,79)?
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

XOR LX

Well-known Member
Joined
Jul 2, 2012
Messages
4,517
I think you'd need to first explain a bit more what you mean by 'degree of ascending order'. From your example, I can't deduce any conditions/criteria which you have used to arrive at this definition, nor can I imagine any myself in which 88,100,79 would be considered 'ascending'.

Regards
 

speedbird168

New Member
Joined
Jun 24, 2013
Messages
4
I think you'd need to first explain a bit more what you mean by 'degree of ascending order'. From your example, I can't deduce any conditions/criteria which you have used to arrive at this definition, nor can I imagine any myself in which 88,100,79 would be considered 'ascending'.

Regards
Assuming 1-100 is in ascending order(?) from small to large, I want to randomize the numbers but maintain a degree of order from small to large so that the general weighting remains from small towards large eg. a randomized 1-10 would then look something like 2,4,1,3,5,7,8,10,8,9
Many thanks for making the effort to reply.
 

Dan Rittgers

Active Member
Joined
Dec 28, 2007
Messages
415
Assuming 1-100 is in ascending order(?) from small to large, I want to randomize the numbers but maintain a degree of order from small to large so that the general weighting remains from small towards large eg. a randomized 1-10 would then look something like 2,4,1,3,5,7,8,10,8,9
Many thanks for making the effort to reply.
This is a stratified sample.

Insert a column for the Strata range:
1- 100 Strata A
101 - 200 Strata B

Apply the random number, but do not forget to """value""" the random number because every time you hit F9, or make another entry the "random number" changes. If you are having a third party reviewing the sample they will doubt the integrity og the process without
 

speedbird168

New Member
Joined
Jun 24, 2013
Messages
4
This is a stratified sample.

Insert a column for the Strata range:
1- 100 Strata A
101 - 200 Strata B

Apply the random number, but do not forget to """value""" the random number because every time you hit F9, or make another entry the "random number" changes. If you are having a third party reviewing the sample they will doubt the integrity og the process without
Thanks--still don't see how 1-100 can maintain a degree of random ascending order unless fragmented into small ranges of say 1-10...
 

Dan Rittgers

Active Member
Joined
Dec 28, 2007
Messages
415
Thanks--still don't see how 1-100 can maintain a degree of random ascending order unless fragmented into small ranges of say 1-10...
Column A contains the value within the population

Column B contains the Labels for the Strata A, B, C, etc and is dependent upon a range you assign 1-100 represents the range for Strata A, 101-1000 is Strata B

Column C Contains the Random Number

Sort columns any direction you want Strata ascending/descending and then Random Number ascending/descending

Column C controls the random nature of the sample.

I stratified 800,000 lines of data in Access and the ranges fit within the parameter:

Strata was within one standard deviation of mean
 

speedbird168

New Member
Joined
Jun 24, 2013
Messages
4
Thank you very much--I'm still stumbling, clearly not very good at this. If you have a screenshot I could look at for a small set it would be fantastic. No bother if you're busy
Rgds
 

Watch MrExcel Video

Forum statistics

Threads
1,099,961
Messages
5,471,724
Members
406,779
Latest member
a_faulding

This Week's Hot Topics

Top