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)?
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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...
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,188
Members
448,554
Latest member
Gleisner2

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