Excel Solver Help!

**RAISETOWIN** · 02-15-2009, 05:11 PM

I'm new to the forum and joined because I will be using excel alot in my college courses over the next few years.

This is an optimization problem in one of my text books. I attached the file as well as a screen shot.

My "Objective" is to maximize profit per box of books that are shipped. The cell reference for the objective is H11.

My "Decision Variables" are F5:F10 ( Number of books in a box by any particular author.)

The problem is with my "Constraints" and how to enter them into Solver.
They are:
1. Each box must contain 20 books or less.
2. At least three different authors in the box.
3. Between 4 and 8 books from the authors you have in the box.
4. The total weight of the box must be 50 pounds or less.

This is a screen shot and there is also an xls. attachment.
\1

I particularly need help with constraints 2 and 3. I would appreciate your feedback.

Thanks
Justin

**mrice** · 02-15-2009, 05:54 PM

Suggest that you set up a cell containing a COUNTIF formula to count how many cells in the book count range are zero. If you have a minimum of three different authors, then this gives you a maximum of three zeros.

Similary you need to a count of the number of cells in the book count range of greater than or equal to 4 to be three or greater.

You also need to specify the number of books as an integer.

Hope this helps.

**RAISETOWIN** · 02-15-2009, 06:19 PM

Originally Posted by mrice

Suggest that you set up a cell containing a COUNTIF formula to count how many cells in the book count range are zero. If you have a minimum of three different authors, then this gives you a maximum of three zeros.

Similary you need to a count of the number of cells in the book count range of greater than or equal to 4 to be three or greater.

You also need to specify the number of books as an integer.

Hope this helps.

OK the first idea definately helped. Now I can use that cell as a constraint.
But I dont understand how your second idea is going to garauntee that the authors that are used will have between 4 and 8 books in the box. Could you elaborate.

**mrice** · 02-15-2009, 06:39 PM

If you set up a formula which sums the countif's for zeros and for >= 4 then this should have a value of 6.

**dogfood9** · 10-31-2010, 09:23 PM

Hello. I am experiencing the same problem as Justin. I have the same case, but also the same problem. I am able to use solver to get a solution, unless I use the COUNTIF function. If I use COUNTIF as suggested, solver tells me that it is unable to find a feasible solution. I have googled around some more, and I've seen at least one person say that COUNTIF may not be used in a cell used as a constraint. Help, please!

**shg** · 11-01-2010, 01:04 AM

Welcome to the forum, dogfood. Smithfield is my favorite ham, and almost (Newport) my hometown.

Please take a few minutes to read the forum rules, and then start your own thread.

Thanks.

**Alf** · 11-01-2010, 03:37 AM

Some hints.

The range "F5:F10" must have a integer constraint as you don't put part of books in a box.

Add a range say "J5:J10" and set a binary constraint on this range. The sum of this range "J11" should be => 3 (a mimum of 3 authors in each box)

HTH

Alf