Random List of Numbers

[andrewwglenn]

I am trying to generate a sequence of incremented integers where some are known and some are populated randomly within a sequence construct. For example:

#<10<#<#<20<#<#<30<<<100 <#<#<1010, etc.

The idea is that each of the "#" is replaced with a unique integer that is greater than the previous and less than the next (whether the next is random or known).

The problem I encounter is tackling the 2nd consecutive random integer (shown above in bold). I haven't figured out how to guarantee that the integer is greater than the value directly preceding it. Is this possible without VBA or looping?

Ultimately, my goal is to be able to randomly generate an incremented list within these constraints as often as necessary.

Thanks.


Andrew

 

[StephenCrump]

VBA would be neater. You'll need some helper cells using a formula approach.

Here's one way you could do it:

C2: =B2+MATCH(RANDBETWEEN(1,COMBIN($F2-B2-1,COLUMNS(C2:$F2)-1)),C4:C20) Copy across to D2:E2
C4: =1
C5: =COMBIN($F$2-B$2-1-ROWS(C$5:C5),COLUMNS(C$2:$F$2)-2)+C4 Copy down and across

Excel 2010
	A	B	C	D	E	F
1		Anchor				Anchor
2		10	14	17	18	20
3
4			1	1	1
5			29	5	2
6			50	8	3
7			65	10	#NUM!
8			75	11	#NUM!
9			81	#NUM!	#NUM!
10			84	#NUM!	#NUM!
11			85	#NUM!	#NUM!
12			#NUM!	#NUM!	#NUM!
13			#NUM!	#NUM!	#NUM!
14			#NUM!	#NUM!	#NUM!
15			#NUM!	#NUM!	#NUM!
Sheet1