Hi guys. Is it possible for us to process a combinatorics problem from Excel?
For example, say I have 2 columns A & B. Column A contains 100 orders of quantities of items from 1-10 (with repetitions), and Column B contains rate per unit of that item. Let's say I set a parameter in which I say that the total no.of items required is 150. I want the output in two ways :
1. Total no.of combinations of item quantities possible that total up to 50 (10+10+10+10+10, 10+10+10+10+9+1, 10+8+2+10+10 and so on).
2. Output showing the break up of those combinations alongwith the corresponding average rate from those specified in column B.
Is this possible via a VBA code?
Input
<tbody>
</tbody>
Output parameter value = 10
Output
<tbody>
</tbody>
<tbody>
</tbody>
It would be great if someone could come up with a VBA for this. Would save up a lot of manual time and labour.
For example, say I have 2 columns A & B. Column A contains 100 orders of quantities of items from 1-10 (with repetitions), and Column B contains rate per unit of that item. Let's say I set a parameter in which I say that the total no.of items required is 150. I want the output in two ways :
1. Total no.of combinations of item quantities possible that total up to 50 (10+10+10+10+10, 10+10+10+10+9+1, 10+8+2+10+10 and so on).
2. Output showing the break up of those combinations alongwith the corresponding average rate from those specified in column B.
Is this possible via a VBA code?
Input
Order quantities | Rates |
1 | 12.25 |
2 | 11.30 |
3 | 14.70 |
4 | 10.55 |
4 | 10.10 |
3 | 9.85 |
2 | 10.80 |
1 | 11.95 |
<tbody>
</tbody>
Output parameter value = 10
Output
No.of possible combinations : _____ |
<tbody>
</tbody>
List of combinations | ||
Quantities Combinations | Average Rate | |
4+4+2 | 10.17 | |
4+3+3 | 11.55 | (Picking up the value of 4 as 10.10) |
4+3+3 | 11.70 | (Picking up the value of 4 as 10.55) |
4+3+2+1 | 10.68 | |
4+2+2+1+1 | 11.37 |
<tbody>
</tbody>
It would be great if someone could come up with a VBA for this. Would save up a lot of manual time and labour.