Can't Solve Math Problem with Excel?

BwayJoey

New Member
Joined
Aug 10, 2010
Messages
2
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

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,004
Office Version
  1. 365
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
Joined
Aug 10, 2010
Messages
2
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
Joined
Jun 20, 2002
Messages
8,004
Office Version
  1. 365
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
 

Watch MrExcel Video

Forum statistics

Threads
1,133,272
Messages
5,657,774
Members
418,413
Latest member
Radoslaw Poprawski

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
Top