Excel Formulas

Question

Hi&nbsp;Im populating some figures, but i would like to know a way I can randomise numbers.&nbsp;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.&nbsp;Any help would be appreciated if understood.&nbsp;Thanks

peterbartholomew1 · Answer

d_bo7&nbsp;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.&nbsp; According to the chosen scheme the 7th number might be the only one to exceed 500 or might come out negative.&nbsp;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.&nbsp; First I scaled the numbers to give the correct sum (in this case 1000).&nbsp; Since these values are not integers, I separated the integer and fractional parts.&nbsp; The sum of the resulting integers will be less than 1000 so an adjustment is needed to round some values up, rather than down.&nbsp; For that, I ranked the fractional parts.&nbsp; 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.&nbsp;&nbsp;rand&nbsp;result&nbsp;&nbsp;NameRefers to&nbsp;0.5584&nbsp;166&nbsp;&nbsp;totalRand= SUM(rand)&nbsp;0.3426&nbsp;101&nbsp;&nbsp;scaled= 1000*rand/totalRand&nbsp;0.7837&nbsp;233&nbsp;&nbsp;roundDown=INT(scaled)&nbsp;0.4879&nbsp;144&nbsp;&nbsp;remainder=MOD(scaled,1)&nbsp;0.2082&nbsp;61&nbsp;&nbsp;correction=1000-SUM(roundDown)&nbsp;0.7535&nbsp;224&nbsp;&nbsp;U={1,1,1,1,1,1,1}&nbsp;0.2414&nbsp;71&nbsp;&nbsp;array= SIGN(remainder&lt;=TRANSPOSE(remainder))&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;adjustment= INDEX( array, 0, MATCH( correction, MMULT( U, array ), 0 ) )totals:3.3757&nbsp;1000&nbsp;&nbsp;result= roundDown + adjustment

haytham amairah · Answer

Hi d_bo7,&nbsp;Please check out this https://www.mrexcel.com/forum/excel-questions/242354-generating-set-random-numbers-total-set-value.html.I think the solution you looking for is in the fifth reply to that conversation.&nbsp;Hope that help

twifoo · Answer

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.

Forum Discussion

Excel Formulas

3 Replies

Resources

	rand	result	Name	Refers 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