Haizen has to arrange transport for ‘N’ employees where ‘N’ can be 1 to 500. There are three types of vehicles available with different capacity and different cost. He wants a Formula for the lowest cost combination of vehicles for any number, ‘N’, passengers. This lengthy and informative Episode #1830 walks through exploring a brute-force method, then moving to Solver, then using VBA with Solver to solve for all 476 non-trivial cases.

Since this video is fairly long, we’ve set up ‘jump points’ so that you may jump to any part of the video:

• Defining the Problem – Transport N employees using lowest cost combination of cars, vans, and bus (0:15)
• Filling numbers from 1 to 500 using Fill Series Dialog (0:50)
• Using formulas to figure out total cost and capacity of manually selected items (1:38)
• Setting up conditional formatting using a formula (2:53)
• Setting up a model with input, output, and constraint cells (3:35)
• Solver is not in my Data tab of the ribbon (3:58)
• Set up Solver the first time (4:15)
• Defining a constraint in Solver (4:30)
• Specify that some input cells must be integer (4:48)
• Re-running Solver for new case (6:24)
• Allowing your workbook to have macros (7:8)
• Changing Macro Security (7:36)
• Recording a Macro (7:51)
• Switching to VBA to see the recorded Macro (8:24)
• Record Solver Code gives a Compile Error when you run the macro (8:56)
• Recap of the manual steps to solve each passenger level (9:45)
• Macro to log the Solver results in a lookup table (10:13)
• Making Solver Not Display OK After Running with VBA (11:29)
• Using a simple loop with GoTo (13:5)
• Charting Results to Look for Patterns (14:17)
• Making a Copy of the Macro for Single Use (15:30)
• Adding a Shape to the Sheet to run the macro (16:5)
• Using the Macro to solve a single case for new costs (16:42)
• Making a second macro button to run all (17:5)
• Wrap-Up (18:32)
• Link to Jon Peltier site for more details on using Solver with VBA (18:55)

…This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill’s book!

“The Learn Excel from MrExcel Podcast Series”

Visit us: MrExcel.com for all of your Microsoft Excel Needs!