I need to generate random numbers whose sum is K. Also these random number need to be divided by a constants J to give me the sum of L. Let me explain by way of a little data grid.
Total of Sum K 5044.00
Total of Sum L 6837.42
Capture.JPG
Item Random number Sun K Constant J Sum L
1 0.00000000 0.00000000 0.3333 0.00000000
2 0.10868186 131.19367850 0.3750 349.84980932
3 0.59150255 714.02342638 0.4167 1713.51914179
4 0.00669870 8.08623823 0.5000 16.17247646
5 0.00000000 0.00000000 0.5833 0.00000000
6 0.74483945 899.12176693 0.6250 1438.59482708
7 0.70382438 849.61103911 0.7500 1132.81471881
8 0.47850676 577.62225548 0.8333 693.17443355
9 0.60530603 730.68608678 0.8750 835.06981346
10 0.93912902 1133.65550860 0.9167 1236.67013047
11 0.00000000 0.00000000 1.0000 0.00000000
4.17848875 5044.00 7.2083 7415.86535094
Sum K = 5044.00 Sum L = 6837.42
If I don't want the item included I just replace the generated Random # with 0.
Random number are generated with =RAND()
Ok so to Calculate K is quite easy >> =Item1/SUM(Random numbers)*5044.00
To Calculate Item1 Const L >> Item1 K / Item1 Const J.
SO here is the problem The sum of Column K = 5044, I need the Sum of L to be 6837.42.
Any help with this formula would be appreciated. I can paste my code if needed.
Bookmarks