Determining random numbers that add up to a predetermined sum

marianaar 

There are a number of issues.  The first point to note is that LET is an Excel 365 function so will not work in legacy versions of Excel.

= LET(
  rand, -target*LN(RANDARRAY(n))/n,
  upper, 1+INT(target*rand/SUM(rand)),
  remainder, MOD(rand,1),
  k, SUM(upper) - target,
  limit, LARGE(remainder,k),
  return, upper - (remainder>=limit),
  return)

It is possible to revert the formula to past standards by recognising that each partial formula is assigned a name for use in subsequent lines.  To remove a name one replaces the later occurrences by the formula it represents, so creating a single nested formula that does not require LET.  The other catch is that the random numbers 'rand' must then be held within a helper range, otherwise different instances within the same formula will contain different random numbers.

The next point that also affects the OP.

In the original reply I drew the sample from a uniform distribution before adjusting it to account for the particular sample results.  I have since come to the conclusion that an exponential distribution is more suitable, in order to ensure that the distribution mean is likely to correspond to that required of the sample.  The sample values are then given by inverting the cumulative distribution, which gives rise to the natural logarithm.  Working within bounds is also possible but sometimes it will not be possible to correct the sample mean (by scaling and rounding), and hence the total, without violating one bound or the other.