ExcelNewbie2020
Active Member
- Joined
- Dec 3, 2020
- Messages
- 333
- Office Version
- 365
- Platform
- Windows
having multiple projects with limited budget. Using excel formula, need to maximize the number of quantity without exceeding the set budget cost.. This scenario could be solved by the solver, but the solver needs to recalculate everytime the data change.
Excel_Free_Workbook_Solver_Basics_XelPlus.xlsx | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | |||
1 | RAW DATA | EXPECTED RESULT | ||||||||||
2 | Project name | Total Costs | Qty | Project name | Total Costs | Qty | ||||||
3 | Project A | 6400 | 35 | Project A | 6400 | 35 | ||||||
4 | Project B | 7900 | 20 | Project D | 6500 | 40 | ||||||
5 | Project C | 6000 | 13 | Project F | 3200 | 30 | ||||||
6 | Project D | 6500 | 40 | Project G | 8700 | 32 | ||||||
7 | Project E | 3400 | 25 | Project I | 9000 | 36 | ||||||
8 | Project F | 3200 | 30 | Project E | 3400 | 25 | ||||||
9 | Project G | 8700 | 32 | 37200 | 198 | |||||||
10 | Project H | 9600 | 15 | |||||||||
11 | Project I | 9000 | 36 | |||||||||
12 | 60700 | |||||||||||
13 | ||||||||||||
14 | Constraints: | |||||||||||
15 | budget cost | 40000 | ||||||||||
16 | ||||||||||||
17 | ||||||||||||
18 | ||||||||||||
19 | ||||||||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
I9:J9 | I9 | =SUM(I3:I8) |