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!
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,983
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
7,983
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
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,009
Messages
5,508,761
Members
408,692
Latest member
OptimalKR

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top