Calculating Random Numbers into a single Number

[Cherish1980]

Hello,
I know how to calculate random numbers on excel, but I was wondering if there was a formula to calculate random numbers into a single number. For example, If I needed 4 random numbers to equal 652, how would I write that formula? Is it even possible? Please let me know. Thanks!

 

[gardnertoo]

Here's a way that works if you need exactly four random numbers to add up to a pre-determined sum:

Book1
	A	B	C	D	E	F
1	Target	Num 1	Num 2	Num 3	Num 4	sum
2	650	400	176	47	27	650
Sheet1

 

[gardnertoo]

...and here's a version that allows you to change how many random numbers are generated:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Target	Num 1	Num 2	Num 3	Num 4	Num 5	Num 6	Num 7	Num 8	Num 9	Num 10	Num 11	Num 12	Num 13	Num 14	Num 15	Num 16
2	650	32	58	51	18	61	72	9	124	26	199	0	0	0	0	0	0
3
4	How many numbers:
5	10
6
7	Checksum:
8	650
Sheet1

This one is not EXACTLY random. Each "random" number except the n'th one (n=how many numbers do you want) is in the range of 0 - target/n. I did this to prevent it from randomly filling up and generating zeros after that.

 

[Cherish1980]

Thank you so much!
Now, one more question, can I make it so that the numbers are within a range, like from 100 - 200?

 

[gardnertoo]

That wasn't as easy as I had thought it would be, but YES it can be done:

Mrexcel random.xls
	A	B	C	D	E	F
1	Target	Num 1	Num 2	Num 3	Num 4	Num 5
2	650	193	121	164	172	0
3
4	How many numbers:	Possible?	Checksum:	lower range	upper range
5	4	1	650	100	200
6
7	Absolute Max	200	200	200	200	200
8	Max for sum	347	256	236	172	0
9	chosen max	200	200	200	172	0
10	span	100	100	64	0	-100
11	chosen min	100	100	136	172	100
12	min for sum	47	56	136	172	0
13	absolute min	100	100	100	100	100
14
15	numbers left	3	2	1	0	0
16	lowest sum to come	303	201	100	0	0
17	highest sum to come	603	401	200	0	0
Sheet1

You'll get a message of "undefined" if the combination of conditions you impose cannot be met. For example, you can't get add up to 300 using four values each between 100 and 200, nor can you add up to 1200 with those conditions.