Mar 07 2021 08:37 PM - edited Mar 07 2021 08:42 PM
Hello Everyone,
My goal is to create a random set of numbers that add up to a particular total. In the image below, you'll see a column of 10 numbers and its total in A12. What function would I need to insert in order to randomize the 10 numbers, but still end up with a total of 310?
Please help..??
Mar 08 2021 01:16 AM
How to insert random (integer) numbers between two numbers without repeats in Excel?
Cclick on header for more infos)
This method will apply the RAND function and RANDBETWEEN function to insert random numbers between two numbers into a specified range in Excel. Please view below formulas:
Merci pour votre patience et votre temps.
Nikolino
Je sais que je ne sais rien (Socrate)
Mar 08 2021 01:19 AM
Hi @Zan_Hanifee
If duplicate numbers are acceptable then you can use achieve your goal by using below different formuals:
Paste =RANDBETWEEN(1,310) in A1
Paste =IFERROR(RANDBETWEEN(1,(310-SUM($C$1:C1))),0) in A2 & drag till A9
Paste =IF(SUM(C1:C9)=310,0,310-SUM(C1:C9)) in A10
You may also refer the attached file.
Thanks
Tauqeer
Mar 08 2021 02:16 AM
Mar 08 2021 02:18 AM
Mar 08 2021 02:23 AM
Mar 08 2021 02:26 AM
Mar 08 2021 02:28 AM
Mar 08 2021 02:51 AM
A solution using Excel 365.
= LET(
rand,RANDARRAY(N),
upper, 1+INT(target*rand/SUM(rand)),
remainder, MOD(rand,1),
k, SUM(upper) - target,
limit, SMALL(remainder,k),
upper - (remainder<=limit) )
where target =310 and N = 10 for the case described.
Even then, I am not absolutely sure that every possibility has an equal probability of being selected.
Mar 08 2021 03:38 AM
To avoid zero numbers (I guess you mean integers under numbers) and avoid rounding errors we could split the range on 3 parts and calculate as here
Mar 08 2021 04:20 AM
Mar 13 2021 12:48 AM
Sep 05 2021 08:33 PM
Hello @Peter Bartholomew
I'm struggling with following this "LET" equation. Is it possible for you to break it down a bit more for me? I've been imputing the equation into excel and I'm receiving an error message. As an example, I'm trying to come up with 5 random numbers between 3 and 8, but the sum of these numbers needs to be 21. How would I type this into the formula?
Thank you!
Sep 06 2021 04:54 AM
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.