generating a set of random numbers to total a set value

[yaldan]

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

 

[masooque]

I'm glad I could help. I'm one of the few people left in the world who's not on Facebook. If you have a question, send me a message here and I'll get back to you.

Respected Sir Mr. Eric,
Nice to hear from you.... thanksss again..... sure i'll contact you.. nice to meet you.

 

[khenmasterz]

OK, I created some formulas that will always generate a valid set of numbers, with no repeated F9. But as I warned you, the formulas are more complicated.

Excel 2010

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

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula A4 =RANDBETWEEN(MAX($B$2,$A$2-(($D$2-ROWS($A$4:$A4))*$C$2)),MIN($C$2,$A$2-(($D$2-ROWS($A$4:$A4))*$B$2))) A5 =IF(ROW()=$D$2+3,$A$2-SUM($A$4:$A4),IF(ROW()>$D$2+3,"",RANDBETWEEN(MAX($B$2,$A$2-(SUM($A$4:$A4)+($D$2-ROWS($A$4:$A5))*$C$2)),MIN($C$2,$A$2-(SUM($A$4:$A4)+($D$2-ROWS($A$4:$A5))*$B$2))))) B4 =IF(OR($C$2*D$2<$A$2,$B$2*$D$2>$A$2),"Can't be done!","") B10 =SUM(A4:A20) <tbody> </tbody>

<tbody>
</tbody>

Here's your 2-number example:
Excel 2010

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

<tbody>
</tbody>

Sheet1​

From your example in post #13:
Excel 2010

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

<tbody>
</tbody>

Sheet1​

Since your numbers 3 and 4 are fixed, subtract their total (50) from the overall total (85) to get the value (35) that numbers 1 and 2 must sum to.

Greeting sir,

i've got task from my sister to help her make formula, would you like to help me create random number for specific criteria, any help would be appreciated

	A	B	C	D	E	F	G	H	I
1	List Of Fixed Number as Criteria	Number of random numbers		Mark1	Mark2	Mark3	Mark4	Mark5	SUM of fix number
2	0	5		0	15	20	20	20	75
3	5			15	20	5	10	5	55
4	10			20	20	20	20	20	100
5	15
6	20

<tbody>
</tbody>

i would like to make mark1-5 to be random from specific number in column A to sum specific number in column I

pls help me figured out the exact formula.

thx in advanced

 

[cidfidou]

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.

Hi Eric,

I have been looking for something like that for ages. Thank you so much for sharing your knowledge.

Is there a way to avoid negative number?

Target	Min	Max	Number of radom
5	0	3	5
3
3
0
1
-2

<colgroup><col style="width:48pt" width="64"> <col style="width:74pt" width="98"> <col style="width:48pt" width="64"> <col style="width:89pt" width="118"> </colgroup><tbody>
</tbody>

 

[Eric W]

I'm glad you found it useful.

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.

That's why you're getting a negative number in the last row. Just press F9 to get a different set of answers. If you want the formulas to make sure that the numbers are valid without F9, refer to the set of formulas in post #14. More complicated, but you won't get any negative numbers.


Hope this helps!

 

[cidfidou]

I'm glad you found it useful.


That's why you're getting a negative number in the last row. Just press F9 to get a different set of answers. If you want the formulas to make sure that the numbers are valid without F9, refer to the set of formulas in post #14. More complicated, but you won't get any negative numbers.


Hope this helps!

Thanks Eric. Fantastic formula. You saved me a big headache. Thank you so much for sharing your knowledge with your the world

 

[Eric W]

Happy to help!

 

[nicknick43]

Hey Eric I'm wondering if you could help me with a complication to this. I'd like to have a list of items in a sheet and step by step want the following to happen:

For a set item I want it to generate a set number between 3-5. Along with a secondary number between 8-15

Next I want to generate 4-5 more items from the listed.

Then generate a set number 4-5 for each along with a corresponding number between 10-20.

I want all items to add to 280+

You could use exercise as an example:
Set item is: Bench Press: random set number is 4. Random corresponding (rep) number is 12. Total is 48

Now have 4- 5 more items chosen from a list of supplementary movements.

If it was 4 then the totals would have to equal or surpass 232.

Let me know if that makes sense and thank you for any help ahead of time.

 

[Eric W]

Welcome to the forum!

Sorry I didn't reply sooner. I usually unsubscribe from threads when they seem to be resolved, and I just happened to notice this. In any event, here's my first stab at it:

	A	B	C	D	E	F	G
1	Exercises		List	Sets	Reps	Total	Running Total
2	Bench Press		Pull ups	3	15	45	45
3	Squats		Squats	3	9	27	72
4	Push-ups		Curls	3	15	45	117
5	Curls		Burpees	3	15	45	162
6	Sit-ups		Chin ups	5	15	75	237
7	Leg Raises		Leg Raises	3	13	39	276
8	Chin ups		Bench Press	3	10	30	306
9	Pull ups
10	Crunches
11	Burpees
12
13
14
15
16
17
18
19
20

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula D2 =IF(C2="","",RANDBETWEEN(3,5)) E2 =IF(C2="","",RANDBETWEEN(8,15)) F2 =IF(C2="","",D2*E2) G2 =IF(C2="","",SUM($F$2:F2)) <tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula C2 {=IF(SUM($F$1:$F1)>280,"",INDEX(A:A,SMALL(IF(ISERROR(MATCH($A$2:$A$20,$C$1:$C1,0)),ROW($A$2:$A$20)),RANDBETWEEN(1,COUNTA(A:A)-ROW()+1)))&"")} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

You have a list of exercises in column A. The formulas allow you to enter whatever you want up to row 20, but we can raise that if needed. Then the formula in C2 randomly selects an exercise from column A. As you drag down, it ensures you don't get duplicates. Then D2 generates a random number 3-5, E2 generates a random number 8-15, F2 is D2*E2, and G2 is a running total of column F. When that total exceeds 280, the formula in C will not generate any more exercises. Press F9 to get a different set.

In this example, the running total hit 276 on the Leg Raises. I considered tweaking the formulas to round up the last exercise, either the sets or reps, so that I don't need to list another exercise. But that's probably much easier to do by just looking at it.

In any case, take a look at it and let me know what you think.

 

[veljanin]

hi Eric,

do you maybe have a formula that would total to specific value in both rows and columns?
the thing is that I need random data table that has specific sums across row and columns.

thanks,
Veljko

Here is a version that works horizontally, with 5 values as in your example:

Excel 2010

	A	B	C	D	E	F	G	H	I	J	K	L	M
1		Min	Max	Number of random numbers
2		5	25	5			Mark1	Mark2	Mark3	Mark4	Mark5	Total
3							11	6	6	5	6	34
4							17	5	5	6	6	39
5							13	6	7	9	8	43
6							#NUM !	#NUM !	#NUM !	#NUM !	#NUM !	23
7
8

<tbody>
</tbody>

Sheet15​

Worksheet Formulas Cell Formula G3 =RANDBETWEEN(MAX($B$2,L3-(($D$2-COLUMNS($G3:G3))*$C$2)),MIN($C$2,L3-(($D$2-COLUMNS($G3:G3))*$B$2))) H3 =IF(COLUMN()=$D$2+COLUMN($G$3)-1,$L3-SUM($G3:G3),IF(COLUMN()>$D$2+COLUMN($G$3)-1,"",RANDBETWEEN(MAX($B$2,$L3-(SUM($G3:G3)+($D$2-COLUMNS($G3:H3))*$C$2)),MIN($C$2,$L3-(SUM($G3:G3)+($D$2-COLUMNS($G3:H3))*$B$2))))) <tbody> </tbody>

<tbody>
</tbody>

Paste the G3 formula in G3. Then paste the H3 formula in H3, then copy H3 across to K3. Then copy G3:K3 and paste them down the column as far as you need. The #NUM ! values in row 6 indicate that there is no possible solution.

As far as the algorithm, here's a quick rundown:

If you are selecting 5 numbers that must add up to a certain value, and you have already picked 4 of them, then the fifth number MUST be the target sum, minus the sum of the other four. Part of the H3 formula does just that, it looks to see if it's in the right column, and if so, does the subtraction.

Now for the previous 4 numbers, we have defined low and high values, B2 and C2. However, the more numbers we pick, the more constraints we have on the maximum and minimum values. For example, look at row 3. The first number picked was 11. Now we want to pick the second number. The total is 34, minus 11, leaves us with 23. Now let's assume that the 3rd, 4th and 5th numbers are all as low as possible, which is 5. 23 - 5 - 5 - 5 = 8. So although the theoretical maximum number for the second number is 25, at this point in the selection process, the actual maximum is 8. We do this using MIN(25, 8). Using a similar process assuming that the 3rd, 4th and 5th numbers are as high as possible (25), we find that the minimum value is MAX(5, -8), or 5. So we do a RANDBETWEEN(5, 8).

And we just repeat. The G3 formula calculates a RANDBETWEEN based on the number of values, the max and min values, and the target number. H3 to J3 perform the same thing, only they include the sum of the previously selected numbers so far. And the K3 formula is just the target minus the sum of the numbers chosen.

In principle, not too hard, but it is kind of tricky to incorporate into formulas. Take a look and let me know if you can't figure out the pieces.

Out of curiosity, where are you located, and what subject are you teaching?

Good luck!

 

[Eric W]

Hi @meiko87

I got your PM, and I'm replying this way since you can't receive PMs until you've posted some messages. I've resubscribed to this thread for now. Hopefully, you'll get the tag.

Your question is: why do I get a long string of the minimum value at the end of my list? The answer is basically that these are not "true" random numbers, they are constrained by the requirement that they add up to a given number. The first few in the series are pretty random, but if they start to average above the midpoint, then they don't leave as much room for the succeeding numbers. At some point, the formulas say: "I've got 2000 left and 5 numbers to choose, so they all must be 400." This effect is much more pronounced with large ranges, like yours. Maybe I could change the way they work. Instead of just checking to see how much I have left, I could readjust the boundaries as I go down the list, the first number must be between 400 and 2000, based on that the next number can be between 400 and 1900, the next one between 500 and 1950, and so on. I'd have to think about that some more. It's a slightly different way of creating some pseudo-random numbers.

As far as creating 2 decimal places, multiply the target, min, and max values by 100. Then take the resulting list and divide by 100. Voila!

If I think of a better way to create your pseudo-random list, I'll post again here.