Excel Formulas

Copper Contributor

Hi

 

Im populating some figures, but i would like to know a way I can randomise numbers.

 

E.g If I have Mon, tues, wed, thur, fri etc with a numbered total at the end of the week be it 1000...how could I achieve each day saying - a random number under 1000, but with all the days totalling 1000.

 

Any help would be appreciated if understood.

 

Thanks

3 Replies

Hi @d_bo7,

 

Please check out this conversation.

I think the solution you looking for is in the fifth reply to that conversation.

 

Hope that help

If your labels for each day of the week are in A1:G1, H1 contains “Total”, A2:G2 are the random numbers, and H2 contains the predetermined sum, the formulas are:
1. A2:F2=RANDBETWEEN(1,H2/7)
2. G2=H2-SUM(A2:F2)
The foregoing formulas return G2 as the balancing number, which is at least equal to the result of H2/7.

@d_bo7 

This is, regrettably, a far more complex solution.

The trouble with selecting 6 values and hoping the 7th can be used to balance everything out is that the final number is not uniformly distributed over the same range as the first 6.  According to the chosen scheme the 7th number might be the only one to exceed 500 or might come out negative.

 

What I did instead was to use a sequence of named array formulas to turn a range containing 7 random numbers on the interval [0,1] into the required sequence.  First I scaled the numbers to give the correct sum (in this case 1000).  Since these values are not integers, I separated the integer and fractional parts.  The sum of the resulting integers will be less than 1000 so an adjustment is needed to round some values up, rather than down.  For that, I ranked the fractional parts.  Sadly, both RANK and COUNTIFS require range references rather than arrays, so, to avoid using a further helper range, I was left with a horrible calculation involving a 7x7 comparison array and MMULT.

 

 rand result  NameRefers to
 0.5584 166  totalRand= SUM(rand)
 0.3426 101  scaled= 1000*rand/totalRand
 0.7837 233  roundDown=INT(scaled)
 0.4879 144  remainder=MOD(scaled,1)
 0.2082 61  correction=1000-SUM(roundDown)
 0.7535 224  U={1,1,1,1,1,1,1}
 0.2414 71  array= SIGN(remainder<=TRANSPOSE(remainder))
      adjustment= INDEX( array, 0, MATCH( correction, MMULT( U, array ), 0 ) )
totals:3.3757 1000  result= roundDown + adjustment