Excel considers blank cells to be 0s so you needn't fill in these cells with anything to start. Solver will do that for you.
In cell C16, sum up the number of items to be bought above as:
=SUM(C2:C15)
And below that you can sum up the total calorie count of these items (which you'll want eventually to equal 2,400) using the SUMPRODUCT formula:
=SUMPRODUCT(B2:B15,C2:C15)
Now you're ready to build the model, so bring up the Solver window by pressing the Solver button on the Data tab.
Note: The Solver window, shown in Figure 1-23 in Excel 2011, looks pretty similar in Excel 2010, 2011, and 2013. In Excel 2007, the layout is slightly different, but the only substantive difference is that there is no algorithm selection box. Rather, there's an "Assume Linear Model" checkbox under the Options menu. We'll learn all about these elements later
.
The main elements you plug into Solver to solve a problem are an objective cell, an optimization direction (minimization or maximization), some decision variables that can be changed by Solver, and some constraints.
In your case, the objective is to minimize the total items in cell C16. The cells that can be altered are the item selections in C2:C15. And the constraints are that C17, the total calories, needs to be equal to 2,400. Also, we'll need to add a constraint that our decisions be counting numbers, so we'll need to check the non-negative box (under the options menu in Excel 2007) and add an integer constraint to the decisions. After all, you can't buy 1.7 sodas.
To add in the total calorie constraint, press the Add button and set C17 equal to 2,400.
Similarly, add a constraint setting C2:C15 to be integers as shown in Figure 1-25 below.
Press OK.
In Excel 2010, 2011, and 2013, make sure the solving method is set to Simplex LP. Simplex LP is appropriate for this problem, because this problem is linear (the "L" in LP stands for linear). By linear, I mean that the problem involves nothing but linear combinations of the decisions in C2 through C15 (sums, products with constants such as calorie counts, etc.).
If we had non-linear calculations in the model (perhaps a square root of a decision, a logarithm, or an exponential function), then we could use one of the other algorithms Excel provides in Solver.
In Excel 2007, you would denote the problem as linear by clicking the Assume Linear Model under the Options screen. Your final setup should appear as in Figure 1-26.
All right! Go ahead and press the Solve button. Excel should find a solution almost immediately. And that solution is 5. Now, your Excel might pick a different 5 items than mine in the screenshot, but the minimum is 5 nonetheless.
Excerpted with permission from the publisher, Wiley, from Data Smart: Using Data Science to Transform Information into Insight by John W. Foreman. Copyright 2013.
John W. Foreman, author of Data Smart: Using Data Science to Transform Information into Insight, is Chief Data Scientist for MailChimp.com, where he leads a data science product development effort called the Email Genome Project. As an analytics consultant, John has created data science solutions for The Coca-Cola Company, Royal Caribbean International, Intercontinental Hotels Group, Dell, the Department of Defense, the IRS, and the FBI. For more information, visit http://www.john-foreman.com and follow the author on Twitter .