Google Sheets & Solver - figuring out hobby budget

starl

Administrator
Joined
Aug 16, 2002
Messages
6,091
Office Version
  1. 365
Platform
  1. 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
ABCDEFGH
1CostShippingQtyTotalBudget25
2Gawr Gura Pin15.54.9500
3Snaccident153.500
4OTGW Rock13300
5Loungefly Inside Out20000
6Loungefly Alice Roses10000
7Pin 612.99500
8Pin 717400
9Pin 811800
10Pin 916000
110
Sheet1
Cell Formulas
RangeFormula
E2:E10E2=(B2+C2)*D2
E11E11=SUM(E2:E10)


1671136863851.png
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
What is the ultimate goal? Are you looking to find the combination of items that add up to the highest value less than or equal to the budget? Or are you looking to find the most individual items you can get under the budget? Could you order more than 1 of a given item? How many items will there be at a time? This list has 9, which is very doable, but the number of combinations grows rapidly. This is called a knapsack problem, which can be quite hard to solve.

I'm not very familiar with Google Sheets. Does it have Solver? If not, does it have LET, SEQUENCE and some of the newer array functions, or equivalents?
 
Upvote 0
The idea is to figure out what items and how many of each can be ordered and fall within (under or equal) the budget. Yes, it can be more than 1 of the same item.
Yes, there is a Solver Addon for Google Sheets that works (almost) the same as Excel's solver. The only difference I found was a constraint I had to add to ensure the value in column D was greater than 0. Excel didn't require that, Google Sheets did.
She (my friend) doesn't have Excel, hence looking for a Google Sheets solution. I thought Solver would be the way to go.. I know little to nothing about most of the newer functions. From what I can tell, there is a SEQUENCE function, but don't if it's the same. LET is not available.
 
Upvote 0
@Tetra201 seems to have solved it (still testing). The solution, that I have yet to break.

Cell D11: =COUNTIF(D2:D10,0)+H1-E11
1671658418713.png
 
Upvote 0
The Countif is to help Solver minimize the possible number of solutions by narrowing its focus (a very simplified version of the explanation @Tetra201 gave me)
 
Upvote 0

Forum statistics

Threads
1,223,947
Messages
6,175,562
Members
452,652
Latest member
eduedu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top