CODEWITHSUNDEEP
excelrandomnumbers
sakkthi
sakkthi

Reputation: 305

Generate N random numbers whose sum is a constant K - Excel

How can I generate those numbers in Excel.

I have to generate 8 random numbers whose sum is always 320. I need around 100 sets or so. http://en.wikipedia.org/wiki/User:Skinnerd/Simplex_Point_Picking. Two methods are explained here.

Or any other way so I can do it in Excel.

Upvotes: 19

Views: 99648

Answers (3)

JB-007
JB-007

Reputation: 2441

I'm a bit late to the game here - but fyi if only integers required then:

=LET(x_,RANDARRAY(8,1,1,1000000,1),y_,ROUND(x_*320/SUM(x_),0),y_)

is somewhat similar to the favourite soln above, albeit parsimonious (formula in single cell required to produce desired array , no helper column). Also addresses insignificant decimal points, albeit you may need to allocate back the deficit / surplus due to the occasional rounding error which may yield a sum total of 321 or 319. Could do this in a random fashion again using something like index(y_,randbetween(1,8))+320-sum(y_) in formula above - or resort to the infamous helper fn..

Someone commented the favourite soln above (and thus mine, since it stems from a similar concept/approach) is not uniform - I'm not sure this was required; a uniform spread would impede the random nature (and is arguably far simpler as you simply divide a sizeable range into distinct octiles, and follow the same approach already laid out here - not sure where/why the notion that a random spread should be arbitrarily/mechanically 'forced' to adopting some type of non-random spread.. anyways... I obviously haven't read the problem properly (ehem).

@Alfi Robles (24.11.21) asked the question "if you wanted to limit the number of dcp to 2": here you go:

=LET(x_,RANDARRAY(8,1,1,1000000,1),y_,ROUND(x_*320/SUM(x_),1),w_,320-SUM(y_),z_,VSTACK(DROP(y_,-1),TAKE(y_,-1)+w_),z_)

(it's cell independent, so no screenshot required - so long as you throw it into a cell that doesn't spill below Excel's row limit it doesn't matter where you do it mate).

Upvotes: 4

JB-007
JB-007

Reputation: 2441

I'm a bit late to the game here - but fyi if only integers required then:

=LET(x_,RANDARRAY(8,1,1,1000000,1),y_,ROUND(x_*320/SUM(x_),0),y_)

is somewhat similar to the favourite soln above, albeit parsimonious (formula in single cell required to produce desired array , no helper column). Also addresses insignificant decimal points, albeit you may need to allocate back the deficit / surplus due to the occasional rounding error which may yield a sum total of 321 or 319. Could do this in a random fashion again using something like index(y_,randbetween(1,8))+320-sum(y_) in formula above - or resort to the infamous helper fn..

Upvotes: 4

Razvan
Razvan

Reputation: 2596

You could use the RAND() function to generate N numbers (8 in your case) in column A.

Then, in column B you could use the following formula B1=A1/SUM(A:A)*320, B2=A2/SUM(A:A)*320 and so on (where 320 is the sum that you are interested into).

So you can just enter =RAND() in A1, then drag it down to A8. Then enter =A1/SUM(A:A)*320 in B1 and drag it to B8. B1:B8 now contains 8 random numbers that sum up to 320.

Sample output:

enter image description here

Upvotes: 36

Related Questions