 • 464K Members
• 11.6K Online
• 560K Conversations

# Excel Formulas

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

# Re: Excel Formulas

Hi @d_bo7,

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

Hope that help

# Re: Excel Formulas

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.

# Re: Excel Formulas

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 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
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies