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!
