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.
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.
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.