Excel: Maximum Test Coverage Problem

John,
This is the type of problem that Solver is meant to handle, and will do a good job on. Before setting it up, however, I'd like to know how to calculate the threshhold priority test coverage.

Assuming that each test in Category A contributes 50%/3 = 16.67% of the test coverage (and similar for Category B and C), then the attached workbook shows how to solve the problem using Solver.

Problem Set-up:
1) List your tests, their contribution to priority test coverage and cost in separate columns
2) Add a column for a value of 1 (test is part of threshhold group) or 0 (test is not part of threshhold group)
3) Put formulas in cells for the total priority test coverage and total cost. These could be SUMPRODUCT formulas, or you could multiply out the cost and contribution in the columns for contribution and cost (as I did in the sample workbook), then sum them up.
4) Launch the Solver dialog from the Tools...Solver menu item (Excel 2003 and earlier). Excel 2007 puts it in the Data menu.
5) You want to minimize the value of the cell containing the Total cost, subject to the constraints that the 1 and 0 cells have a "bin" (binary) value and that the total priority test coverage cell is 80% or more.
6) Click Solve, then accept the answer.

For your sample problem, the answer was to turn on Categories A and B and leave Category C entirely off.

Since the Solver add-in is an optional install in all versions of Excel, you will probably need to install it. You'll need your Office CD-Rom for this purpose. But first, check to make sure that it is enabled (if it has already been installed):

1) Open Tools...Add-Ins menu item and check the Solver checkbox. You are now ready to use Solver (no need for steps 2 through 9 below)

If you didn't see Solver in the add-ins list in step 1:
2) Open the Start menu...Control Panel...Add or Remove Programs
3) Choose Microsoft Office, then click the "Change" button
4) In the next dialog, choose the "Add or Remove Features" option, then click the "Next" button
5) Check the box for "Advanced Customization of Applications" in the next dialog, then click the "Next" button
6) Find the Solver add-in. Its location varies, but in Office 2003 it is listed under Microsoft Office...Microsoft Office Excel...Add-Ins
7) Click the arrow to the right of the icon, and choose "Run from my computer"
8) Click the "Update" button, and be prepared to insert your Office CD-ROM
9) Once Office has finished updating, launch Excel and repeat step 1

If you want to read up on how Solver works, the add-in author has a pretty good tutorial at http://www.solver.com/tutorial.htm


Brad