Find Array that corresponds to certain totals

[juriemagic]

Good day,

A guy called Razvan posted a reply on Stack Overflow on 14th Feb 2014, showing how random numbers could be generated which would sum to a specified total . I was wondering however if this could be taken two steps further. In my attachment I have in the grey area Razvan's part. The random numbers will always tally up to 1000 in this case. To the right of that are my additions. Would it be possible either by code or formulae, (I would guess code) to have just one set of numbers generate, then have the code looking at E22 and compare the difference between E22 and the desired figure, which is in G1, determine the best possible place in columns B and C (or B or C),to make adjustments (By calculations), then make the adjustment, and then step 2, to compare cells F22 with H1 and do the same adjustments. IMPORTANT: The second adjustment should be made looking at columns C and E or (C only or E only). Please try and help, this is for a major project I need to get done in the course of about one week...I really appreciate and rate your assistance very highly on this one. Even if we could get the first calculation part going, it would already be a fantastic start.

[shg]

..........

[shg]

Maybe this:

Row\Col	A	B	C	D	E
1			Target
2			2100		C2: Input
3	Multiplier	Rand()	Values
4	0.05	0.4432	42.8554		A4 and down: Input
5	0.10	0.4904	47.4161		B4 and down: =RAND()
6	0.15	0.7018	67.8579		C4 and down: =B4*$C$2/$B$22
7	0.25	0.6865	66.3787
8	0.50	0.8773	84.8317
9	0.75	0.1291	12.4859
10	1.00	0.6906	66.7796
11	1.25	0.9668	93.4825
12	1.50	0.8985	86.8781
13	2.00	0.9568	92.5139
14	2.25	0.0494	4.7794
15	2.50	0.9984	96.5390
16	3.00	0.1624	15.7062
17	3.75	0.1125	10.8786
18	5.00	0.8237	79.6494
19	7.50	0.8259	79.8585
20	10.00	0.1845	17.8384
21
22		21.7178	2100.0000		B22 and across: =SUMPRODUCT($A4:$A20, B4:B20)

The numbers generated by Razvan's method have an odd distribution, though; more mid-size numbers than the Dirichlet distribution that occurs with string cutting*, as a result of dividing the numbers by their sum to normalize them. It's not obvious to me how to apply string-cutting to achieve a desired weighted sum, though.

* For n numbers totaling t, cut a piece of string of length t in n-1 random places. The resulting lengths are the numbers.

[juriemagic]

Thank you so much shg!, your code is working great!. Thank you very very much!!

[shg]

You're welcome.