# Can't Solve Math Problem with Excel?

#### BwayJoey

##### New Member
I'm trying to get all of the possible combinations of 3 that cost less than \$4 and their values in a list using an excell formula? Is it possible?

There are 7 things at the store and you must buy 3 different ones without spending more than \$4. All 7 items are valued differently than their price. What 3 things would you buy to get the most value? Any money you save from the \$4 gets added to the purchase's value.

Toy \$2.00 - Value 4
Magazine \$1.75 - Value 4
Ice Cream \$1.50 - Value 3
Drink \$1.25 - Value 3
Lotto \$1.00 - Value 2
Candy \$0.75 - Value 2
Newspaper \$0.50 - Value 1

I can do the math, but I can't do it with excell to check my answer...

Toy, Newspaper, Candy - Cost \$3.25 Value \$7.75
Toy, Newspaper, Lotto - Cost \$3.50 Value \$7.50
Toy, Newspaper, Drink - \$3.75 Value \$8.25
Toy, Newspaper, Ice Cream - \$4.00 Value \$8.00
Toy, Candy, Lotto - \$3.75 Value \$8.25
Toy, Candy, Drink - \$4.00 Value \$9.00
Magazine, Newspaper, Candy - \$3.00 Value \$8.00
Magazine, Newspaper, Lotto - \$3.25 Value \$7.75
Magazine, Newspaper, Drink - \$3.50 Value \$8.50
Magazine, Newspaper, Ice Cream - \$3.75 Value \$8.25
Magazine, Candy, Lotto - \$3.50 Value \$8.50
Magazine, Candy, Drink - \$ 3.75 Value \$9.25
Magazine, Candy, Ice Cream - \$4.00 Value \$9.00
Magazine, Lotto, Drink - \$4.00 Value \$9.00
Ice Cream, Newspaper, Candy - \$2.75 Value \$7.25
Ice Cream, Newspaper, Lotto - \$3.00 Value \$7.00
Ice Cream, Newspaper, Drink - \$3.25 Value \$7.75
Ice Cream, Candy, Lotto - \$3.25 Value \$7.75
Ice Cream, Candy, Drink - \$3.50 Value \$8.50
Drink, Newspaper, Candy - \$2.50 Value \$7.50
Drink, Newspaper, Lotto - \$2.75 Value \$7.25
Drink, Candy, Lotto - \$3.00 Value \$8.00
Lotto, Newspaper, Candy - \$2.25 Value \$6.75

So, the answer is that even though it's possivle to spend the full \$4 in a few different ways, you're actually best spending \$3.75 on the Magazine, candy and drink in order to get the most value \$9.25.

Simple enough with so few options, but the problem has the potential to help with the purchase decision when there are let's say 50 options, prices and values and 10 different items need to be purchased. If there is a way to solve it in excell I'd appreciate some ideas. Thanks!

### Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

#### Oaktree

##### MrExcel MVP
Re: Can't Solve Math Problem with Excell?

Welcome to the board.

This is a perfect use for the solver add-in.

Set up your table so item label, Cost, Value, and a new field for IncludeFlag are each in separate columns

Set up 3 totals:

(1) SUMPRODUCT(Cost,IncludeFlag)
(2) SUMPRODUCT(Value,IncludeFlag) + (4-total from [1] above)
(3) SUM(IncludeFlag)

Then, your parameters of the solver model are to maximize (2) according to the constraints:

(1) <=4
(3) = 3
IncludeFlag Bin (for Binary)

Click "options" and set the tolerance to 0%

Run the model, and you should see the validation.

#### BwayJoey

##### New Member
I'm sorry, but I only know basic excel. is there anyway you could show me screenshots of what you mean. otherwise sumproduct and includeflag, etc... are another language to me.

#### Oaktree

##### MrExcel MVP
IncludeFlag is just a label for the new column you're adding. A 1 in this column will be your "flag" for including that product in your optimal mix. Likewise, a 0 will be a flag for excluding that product.

SUMPRODUCT is a function within Excel.

=SUMPRODUCT(A1:A2,B1:B2) will SUM the PRODUCT of the ranges (i.e. A1*B1 + A2*B2).

As such, SUMPRODUCT(cost,includeflag) will be something like =SUMPRODUCT(A2:A10,B2:B10) where the actual ranges depend on where you've put the cost and includeflag fields on your sheet.

Doesn't particularly matter where you put those formulas on your sheet... you'll just reference them in your solver model (tools --> solver).

If you're new to solver/optimization, there are several examples on the solver website at http://www.solver.com/solutions.htm

Replies
5
Views
726
Replies
6
Views
298
Replies
1
Views
689
Replies
4
Views
574
Replies
7
Views
956

1,181,892
Messages
5,932,668
Members
436,850
Latest member
Jasperlee93

### 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.

### Which adblocker are you using?

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

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