Can't Solve Math Problem with Excel?

[BwayJoey]

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!

 

[Oaktree]

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]

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]

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