millhouse123
Active Member
- Joined
- Aug 22, 2006
- Messages
- 335
I am newbie to solver and trying to solve what I think would be a very simple problem but solver is cant find a solution and I can't figure out what I am doing wrong. There is clearly a soltiom which I included in the mini sheet.
Here are my solver constraints. I have tried everything I can think of but still get non solveable.
Here are my solver constraints. I have tried everything I can think of but still get non solveable.
Solver Example.xlsx | ||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | |||
1 | Contraints | |||||||||||||||
2 | B8:D10 | Integer, value must be between 1 and 3 | ||||||||||||||
3 | Totals for rows and columns need to = 6 | |||||||||||||||
4 | L8:L10 = 3 | |||||||||||||||
5 | Counting occurences of each # by activity, should only be 1 activity per hour and should be different each hour | |||||||||||||||
6 | ||||||||||||||||
7 | Activity 1 | Activity 2 | Activity 3 | Activity 1 | Activity 2 | Activity 3 | ||||||||||
8 | Hour 1 | 1.00 | 3.00 | 2.00 | 6.00 | = | 6.00 | 3 | 1 | 1 | 5 | = | 3 | |||
9 | Hour 2 | 1.00 | 1.00 | 1.00 | 3.00 | = | 6.00 | 3 | 1 | 1 | 5 | = | 3 | |||
10 | Hour 3 | 1.00 | 2.00 | 3.00 | 6.00 | = | 6.00 | 3 | 1 | 1 | 5 | = | 3 | |||
11 | 3.00 | 6.00 | 6.00 | |||||||||||||
12 | = | = | = | |||||||||||||
13 | 6.00 | 6.00 | 6.00 | |||||||||||||
14 | ||||||||||||||||
15 | Solution | |||||||||||||||
16 | 1 | 2 | 3 | |||||||||||||
17 | 2 | 3 | 1 | |||||||||||||
18 | 3 | 1 | 2 | |||||||||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
I8:I10 | I8 | =COUNTIF(B$8:B$10,1) |
J8:J10 | J8 | =COUNTIF(C$8:C$10,2) |
K8:K10 | K8 | =COUNTIF(D$8:D$10,3) |
L8:L10,E8:E10 | L8 | =SUM(I8:K8) |
B11:D11 | B11 | =SUM(B8:B10) |
Named Ranges | ||
---|---|---|
Name | Refers To | Cells |
solver_adj | =Sheet1!$B$8:$D$10 | B11, I8:I10, E8 |
solver_lhs2 | =Sheet1!$B$8:$D$10 | B11, I8:I10, E8 |
solver_lhs3 | =Sheet1!$B$8:$D$10 | B11, I8:I10, E8 |
solver_lhs4 | =Sheet1!$B$8:$D$10 | B11, I8:I10, E8 |
solver_lhs7 | =Sheet1!$B$8:$D$10 | B11, I8:I10, E8 |
solver_lhs8 | =Sheet1!$B$8:$D$10 | B11, I8:I10, E8 |
solver_lhs9 | =Sheet1!$B$8:$D$10 | B11, I8:I10, E8 |