How to generate integer random numbers that equal to another random number?

Question

I am running a simulation in Excel, and need to generate a group of integer random numbers summing up to another random integer, how can I possibly do it? For instance I have an integer random number in A1, how to have three integer random numbers summing to the value in A1? I tried the following: in A1 I have an random integer

round(0,5)

In the range B1:B3 I have

randbetween(0,A1)

In cell C1 I put

=round(B1/SUM($B$1:$B$3)*$A$1,0)

and copied to C2,C3. But the problem is occasionally the random numbers will round up to bigger value than A1, for instance A1=3, when C1:C3 equal to 0, 1.5, 1.5, they will round up to 0,2,2 which sum up to 4. How should I solve it?

Thank you so much in advance!

Answer 1

Try these formulas:

\begin{eqnarray*} B1 &:& \quad\text{=FLOOR(RAND()*(A1+1),1)} \\ C1 &:& \quad\text{=FLOOR(RAND()*(A1+1),1)} \\ D1 &:& \quad\text{=MIN(B1,C1)} \\ E1 &:& \quad\text{=MAX(B1,C1)-D1} \\ F1 &:& \quad\text{=A1-E1-D1} \\ \end{eqnarray*}

Columns $D1,E1,F1$ are the three numbers required. $A1,B1$ are two random "dividing lines" between $0$ and $A1$ inclusive. Obviously, you can copy these for as many rows as you want. I've assumed you're interested in integer values only.