Generate Random number with conditions

Nicoletta991  Previously, I wrote:

``For starters, if I want to generate 20 random non-negative integers (i.e. zero or more) with a mean of exactly 48, that is the same as saying that I want the 20 integers to sum to 960 (20*48).  I would accomplish that with the following paradigm.``

The following is an application of that paradigm to your data and requirements, as I understand them.  Also see the attached Excel file.  Of course, you should take this with a grain of salt, pending answers to the many questions that I asked earlier.

Key Formulas:

C2:  =ROUND(INDEX($L$2:$L$4, E2) * SUMIFS($F$2:F2, $E$2:E2, E2)

         / INDEX($M$2:$M$4, E2) - SUMIFS($C$1:C1,$E$1:E1, E2), 0)

E2:  =MATCH(B2, $I$2:$I$4)

F2:  =RAND()

K2:  =COUNTIFS($B$2:$B$401, ">=" & I2, $B$2:$B$401, "<" & I3)

K4:  =COUNTIF($B$2:$B$401, ">=" & I4)

L2:  =J2*K2

M2:  =SUMIFS($F$2:$F$401, $B$2:$B$401, ">=" & I2, $B$2:$B$401, "<" & I3)

M4:  =SUMIFS($F$2:$F$401, $B$2:$B$401, ">=" & I4)

Other Formulas:

B12:  =CHOOSE(RANDBETWEEN(1,3), RANDBETWEEN(1,999),

           RANDBETWEEN(1000,1700), RANDBETWEEN(1701,5000))

O2:  =SUMIFS($C$2:$C$401, $B$2:$B$401, ">=" & I2, $B$2:$B$401, "<" & I3)

O4:  =SUMIFS($C$2:$C$401, $B$2:$B$401, ">=" & I4)

P2:  =AVERAGEIFS($C$2:$C$401, $B$2:$B$401, ">=" & I2, $B$2:$B$401, "<" & I3)

P4:  =AVERAGEIFS($C$2:$C$401, $B$2:$B$401, ">=" & I4)

For the example, I generate random times starting in B12; and the required mean in J4 is an arbitrary choice.  Of course, you would enter the actual data or substitute the formulas that you already use.

The table in columns H:M is a lookup table.  For the time ranges in column I, I assumed that the "100" that you wrote is a typo, and it should be 1000.

In column E ("idx"), we determine the table index that corresponds to the time in column B.

The table index is used in the formulas in column C to generate the random numbers.  In recent versions of Excel, you could use the LET function to calculate "idx" and assign it to a LET variable.  That would obviate the need for column E.

The formulas in columns O:P are just for "proof of concept".  They are not necessary.