Google Sheets & Solver - figuring out hobby budget

[starl]

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)

 

[Eric W]

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?

 

[Eric W]

Just as a starting point, you could try changing the Solver parameters to:

 

[starl]

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.

 

[starl]

@Tetra201 seems to have solved it (still testing). The solution, that I have yet to break.

Cell D11: =COUNTIF(D2:D10,0)+H1-E11

 

[starl]

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)