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

Question

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.

so apparently you have the algorithm for that. what is your question ? — njzk2, Feb 14 '14 at 14:46
generate 7 random numbers, and for 8th use `=320-sum(A1:A7)` where `A1:A7` first 7 random numbers — Dmitry Pavliv, Feb 14 '14 at 15:19

score 35 · Accepted Answer · edited Aug 13 '16 at 13:26

35

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

edited Aug 13 '16 at 13:26

Maytham Fahmi

26,353
12
100
121

answered Feb 14 '14 at 15:10

Razvan

2,286
16
22

4

This does not give a uniform distribution (in case that matters) https://stackoverflow.com/a/8068956/2075003 – n1000 Jul 20 '18 at 19:04
What if you wanted to limit the number of decimal spaces to just 2? – Alfie Robles Nov 24 '21 at 16:00

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

1 Answers1

Linked