Solver Scenario for Optimal Whole Number w/Office97

[CDobyns]

Every time I think I know what I'm doing with Excel Solver - I surprise myself (by what I apparently don't understand . . .).

I'm trying to solve for the maximum number of company store "certificates" I need to buy, up to say a maximum of $600, in order to buy the maximum number of movie tickets possible (also from the company store) for my employees - but to the closest number of whole movie tickets possible, and since for some reason, you can't "add" actual $$$ money to your purchase to "round up" a fractional amount remaining at the company store (nor can you get change back from a partial certificate purchase).

I already (now) know that 17 is the optimal number, to purchase 60 movie tickets, but assuming the below represents columns and rows, cell B3 would be the cell to change, and B5 would be the target cell, which would be set to <=$600 - again, with objective that both B3 and B4 would result in whole numbers.

Movie Ticket Price.......$8.50
Certificate Price..........$30.00
# of Certificates.........17
# of Movie Tickets......60
Total Certificate Cost..$510.00

All that said, I can't see to get the set-up correctly to get Solver to return 17 in B3. Any help?

 

[CDobyns]

That's (kind of) a cute perspective on the question - although this is as much a hypothetical challenge now, as it is practical (but I think you already knew that). And given that I'm using a portion of our recognition and rewards budget to purchase the Certificates = Movie Tickets, the fact that any of that $$$ should get wasted (when there isn't a whole lot to start with), would be as important to them, as it is to me.

I've got to believe that Solver can (could) have still come up with the solution to this. I suspect I (we) just haven't contemplated what the alternate "set-up" to the problem needs to be.

 

[GooberTron]

After sleeping on this problem, you can set a lower limit for solver as a constraint to help find the match. The real problem being that there is really only one solution (in blocks of $510) if you want absolutely no remainder (similar to a lowest common factor or whatever it is called).

 

[shg]

Solver is an overkill for this. =LCM(300,85)/10 returns 510.

 

[CDobyns]

Whether Solver is (or isn't) overkill for this problem (Hint: It IS overkill), I'm always happy (happ-ier) when I can solve my own problems. So, with a little bit of extra effort (and a little more sleep time), I think I identified the solution to make Solver deliver the correct solution.

What was needed was to expand the Target Cells to include both B3 and B4, and to add the following constraints: $B$4 = Integer and $B$4 = $B$5 / $B$1.

By linking the outcome of the number of tickets to the product of the Total Cost divided by the Cost of the Tickets and constraining B4 to an integer as well, Solver was able to resolve the problem with a single answer (which we already new to be correct). Maybe it was the "long way around", but knowing some of this will create the nexus for the next problem that has multiple variables and is solved through an iterative solution approach.