generating a set of random numbers to total a set value

yaldan · Jan 15, 2007

Is it possible to generate a set of random numbers between say 50 - 150 whereby the sum of those random numbers totals a value in the spreadsheet which is determined by two other values in the spreadsheet?

Eg: value 1 = 10, value 2 = 300. The difference = 290. Now generate 6 or so random numbers between 50 - 150 that come to a total of 290.

If so could you helpme with formula or script.

Regards

Yaldan

SteveO59L · Jan 15, 2007

If you had 6 random numbers between 50 and 150, even if they were all 50, they would never sum to 290.

SIXTH SENSE · Jan 15, 2007

could number be repeated or each number should be unique from each other?

SteveO59L · Jan 15, 2007

Enter the formula =randbetween(50,150) in cells G11:G16, and =sum(g11:g16) in cell G18.

This will generate 6 random numbers between 50 and 150, and their sum.

If you target value is in D18, then the following will refresh the random values until the values in D18 and G18 are equal.

Do Until Range("g18").Value = Range("d18").Value
Calculate
Loop

End

However, you would need some form of error trapping to make sure that your target value is never less than (6 * 50) 300, and never more than (6 * 150) 900, otherwise you would go into and endless loop as you cant reconcile between the two numbers

steve case · Jan 15, 2007

Generate six Random numbers in Range A1:A6 [=rand()]

Cell A9 enter your target sum total

In Cell A8 a formula to devide the target sum by the sum of the six random numbers:
=(A9/(SUM(A1:A6)))

Range B1:B6 multiply each of the six random numbers by contents of Cell A8
=A1*$A$8

Cell B8 Run a check:
=SUM(B1:B7)

kachaloo · Mar 31, 2013

steve case said:
Generate six Random numbers in Range A1:A6 [=rand()]

Cell A9 enter your target sum total

In Cell A8 a formula to devide the target sum by the sum of the six random numbers:
=(A9/(SUM(A1:A6)))

Range B1:B6 multiply each of the six random numbers by contents of Cell A8
=A1*$A$8

Cell B8 Run a check:
=SUM(B1:B7)

You are genius....
I have been looking for this and everybody said it is impossible.....

Smailor · Jul 22, 2015

Just wow. Truly helpful. Was searching for solution for hours.

hselek · Feb 9, 2016

steve case said:
Generate six Random numbers in Range A1:A6 [=rand()]

Cell A9 enter your target sum total

In Cell A8 a formula to devide the target sum by the sum of the six random numbers:
=(A9/(SUM(A1:A6)))

Range B1:B6 multiply each of the six random numbers by contents of Cell A8
=A1*$A$8

Cell B8 Run a check:
=SUM(B1:B7)

What do we do if we need those numbers to be an integer ? Please help...

Eric W · Feb 9, 2016

You could do something like this:

Excel 2010

	A	B	C	D
1	Target	Min	Max	Number of random numbers
2	100	5	25	7
3
4	7
5	19
6	18
7	19
8	18
9	10
10	9

Sheet1

Worksheet Formulas

Cell	Formula
A4	=RANDBETWEEN(B2,C2)
A5	=IF(ROW()=$D$2+3,$A$2-SUM(OFFSET($A$4,0,0,$D$2-1)),IF(ROW()>$D$2+3,"",RANDBETWEEN($B$2,$C$2)))
B4	=IF(OR($C$2D$2<$A$2,$B$2$D$2>$A$2),"Can't be done!","")

Put the values in A2:D2 that you want. Put the formula in A4. Then put the formula in A5 and copy down as far as you need. The last number in the list (A10 in this example) is a calculated number designed to make the list add up. It could theoretically be less than the minimum or higher than the maximum. If that happens, just press F9 to recalculate until you get a valid number. The formula in B4 is just a check to see if the values in A2:D2 are viable.

Let me know if this helps.

hselek · Feb 9, 2016

Eric W said:
You could do something like this:

Excel 2010

A B C D
1 Target Min Max Number of random numbers
2 100 5 25 7
3
4 7
5 19
6 18
7 19
8 18
9 10
10 9

<tbody>
</tbody>
Sheet1

Worksheet Formulas

Cell Formula
A4 =RANDBETWEEN(B2,C2)
A5 =IF(ROW()=$D$2+3,$A$2-SUM(OFFSET($A$4,0,0,$D$2-1)),IF(ROW()>$D$2+3,"",RANDBETWEEN($B$2,$C$2)))
B4 =IF(OR($C$2*D$2<$A$2,$B$2*$D$2>$A$2),"Can't be done!","")

<tbody>
</tbody>

<tbody>
</tbody>

Put the values in A2:D2 that you want. Put the formula in A4. Then put the formula in A5 and copy down as far as you need. The last number in the list (A10 in this example) is a calculated number designed to make the list add up. It could theoretically be less than the minimum or higher than the maximum. If that happens, just press F9 to recalculate until you get a valid number. The formula in B4 is just a check to see if the values in A2:D2 are viable.

Let me know if this helps.

Thanks for your answer

What I need in my particular situation is 2 numbers both of which does not exceed 25 but also not less than 10 to target 50 when added to each other. But in this example the generated numbers can exceed my maximum.
I would be really glad if you could help.
Thanks again

generating a set of random numbers to total a set value

yaldan

New Member

Excel Facts

SteveO59L

Well-known Member

SIXTH SENSE

Well-known Member

SteveO59L

Well-known Member

steve case

Well-known Member

kachaloo

New Member

Smailor

New Member

hselek

New Member

Eric W

MrExcel MVP

hselek

New Member

Similar threads

Forum statistics

Share this page

generating a set of random numbers to total a set value

New Member

Excel Facts

Well-known Member

Well-known Member

Well-known Member

Well-known Member

New Member

New Member

New Member

MrExcel MVP

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock