Hi,
I'm know a formula can be written that generates a set of random numbers that adds up to a specific sum,
or a formula that generates a set of random numbers in a specific interval. However, it is not clear to me how
to integrate these two formulas to solve my problem.
Ideally, my worksheet should look something like this:
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2015-02-25[/TD]
[TD]random no.1[/TD]
[TD]MAX (random no.1)[/TD]
[TD]random no.1[/TD]
[TD]MAX (random no.1)[/TD]
[TD]MAX (B1+D1)[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2015-02-26[/TD]
[TD]random no.2[/TD]
[TD]MAX (random no.2)[/TD]
[TD]random no.2[/TD]
[TD]MAX (random no.2)[/TD]
[TD]MAX (B2+D2)[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2015-02-27[/TD]
[TD]random no.3[/TD]
[TD]MAX (random no.3)[/TD]
[TD]random no.3[/TD]
[TD]MAX (random no.3)[/TD]
[TD]MAX (B3+D3)[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]2015-02-28[/TD]
[TD]random no.4
[/TD]
[TD]MAX (random no.4)[/TD]
[TD]random no.4[/TD]
[TD]MAX (random no.4)[/TD]
[TD]MAX (B4+D4)[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]SUM[/TD]
[TD][/TD]
[TD]SUM[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
The values which should be returned are put in bold and italic (B1:B4 and D1:D4).
These values should be random while satisfying the following criteria:
1) be less than a user defined maximum located in a cell adjacent to it (maximum for B1 is in C1 and so on...),
2) add up to a user defined maximum located in F1:F4,
3) add up to a user defined maximum located in B5 and D5.
Also, the sum values in B5 and D5 are dynamic (decreasing) based on a date value.
For example: let's say on 2015-02-25 we wanted to generate 4 random numbers (B1:B4) that add up to 10 (B5),
Initially our formula returned 4,3,3,0. Since today's date is 2015-02-26, the value in B1 (which is 4) no longer counts and our new sum
value is 10-4=6, so the new set of numbers could be 1, 1, 4 or 2, 2, 2 etc. (the last remained values in columns B and D will always equal their respective sums). Obviously, this example ignored horizontal conditions
that must be met as well.
If something is not clear due to my limited english (or any other reason), please ask for clarification.
Any help will be greatly appreciated.
Stefan
I'm know a formula can be written that generates a set of random numbers that adds up to a specific sum,
or a formula that generates a set of random numbers in a specific interval. However, it is not clear to me how
to integrate these two formulas to solve my problem.
Ideally, my worksheet should look something like this:
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2015-02-25[/TD]
[TD]random no.1[/TD]
[TD]MAX (random no.1)[/TD]
[TD]random no.1[/TD]
[TD]MAX (random no.1)[/TD]
[TD]MAX (B1+D1)[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2015-02-26[/TD]
[TD]random no.2[/TD]
[TD]MAX (random no.2)[/TD]
[TD]random no.2[/TD]
[TD]MAX (random no.2)[/TD]
[TD]MAX (B2+D2)[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2015-02-27[/TD]
[TD]random no.3[/TD]
[TD]MAX (random no.3)[/TD]
[TD]random no.3[/TD]
[TD]MAX (random no.3)[/TD]
[TD]MAX (B3+D3)[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]2015-02-28[/TD]
[TD]random no.4
[/TD]
[TD]MAX (random no.4)[/TD]
[TD]random no.4[/TD]
[TD]MAX (random no.4)[/TD]
[TD]MAX (B4+D4)[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]SUM[/TD]
[TD][/TD]
[TD]SUM[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
The values which should be returned are put in bold and italic (B1:B4 and D1:D4).
These values should be random while satisfying the following criteria:
1) be less than a user defined maximum located in a cell adjacent to it (maximum for B1 is in C1 and so on...),
2) add up to a user defined maximum located in F1:F4,
3) add up to a user defined maximum located in B5 and D5.
Also, the sum values in B5 and D5 are dynamic (decreasing) based on a date value.
For example: let's say on 2015-02-25 we wanted to generate 4 random numbers (B1:B4) that add up to 10 (B5),
Initially our formula returned 4,3,3,0. Since today's date is 2015-02-26, the value in B1 (which is 4) no longer counts and our new sum
value is 10-4=6, so the new set of numbers could be 1, 1, 4 or 2, 2, 2 etc. (the last remained values in columns B and D will always equal their respective sums). Obviously, this example ignored horizontal conditions
that must be met as well.
If something is not clear due to my limited english (or any other reason), please ask for clarification.
Any help will be greatly appreciated.
Stefan
