starl
Administrator
- Joined
- Aug 16, 2002
- Messages
- 6,091
- Office Version
- 365
- Platform
- Windows
Trying to help a friend figure out which hobby items she can get based on her budget. I originally set it up in Excel with Solver, then switched to Google Sheets and Solver. But I have no idea what I'm doing and it doesn't always work. The last 2 constraints are me trying to get this to work with a set value. That is - I originally had the Value Of set to 25. But this limited it to finding exactly 25 when I wanted the Max to be 25. So, I set it to Max and added those constraints to try and trick solver. It works - sometimes. So my question is - is there a way to get this to work consistently? Another solution entirely? Has to be Google Sheets solution.
Pin Budget.xlsx | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | |||
1 | Cost | Shipping | Qty | Total | Budget | 25 | ||||
2 | Gawr Gura Pin | 15.5 | 4.95 | 0 | 0 | |||||
3 | Snaccident | 15 | 3.5 | 0 | 0 | |||||
4 | OTGW Rock | 13 | 3 | 0 | 0 | |||||
5 | Loungefly Inside Out | 20 | 0 | 0 | 0 | |||||
6 | Loungefly Alice Roses | 10 | 0 | 0 | 0 | |||||
7 | Pin 6 | 12.99 | 5 | 0 | 0 | |||||
8 | Pin 7 | 17 | 4 | 0 | 0 | |||||
9 | Pin 8 | 11 | 8 | 0 | 0 | |||||
10 | Pin 9 | 16 | 0 | 0 | 0 | |||||
11 | 0 | |||||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
E2:E10 | E2 | =(B2+C2)*D2 |
E11 | E11 | =SUM(E2:E10) |