![]() ![]() For more information, visit and follow the author on Twitter. 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. Foreman, author of Data Smart: Using Data Science to Transform Information into Insight, is Chief Data Scientist for, where he leads a data science product development effort called the Email Genome Project. Now, your Excel might pick a different 5 items than mine in the screenshot, but the minimum is 5 nonetheless.Įxcerpted with permission from the publisher, Wiley, from Data Smart: Using Data Science to Transform Information into Insight by John W. Excel should find a solution almost immediately. Your final setup should appear as in Figure 1-26.Īll right! Go ahead and press the Solve button. In Excel 2007, you would denote the problem as linear by clicking the Assume Linear Model under the Options screen. 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. 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.). ![]() Simplex LP is appropriate for this problem, because this problem is linear (the "L" in LP stands for linear). In Excel 2010, 2011, and 2013, make sure the solving method is set to Simplex LP. Similarly, add a constraint setting C2:C15 to be integers as shown in Figure 1-25 below. To add in the total calorie constraint, press the Add button and set C17 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. ![]() And the constraints are that C17, the total calories, needs to be equal to 2,400. The cells that can be altered are the item selections in C2:C15. In your case, the objective is to minimize the total items in cell C16. 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. We'll learn all about these elements later Rather, there's an "Assume Linear Model" checkbox under the Options menu. In Excel 2007, the layout is slightly different, but the only substantive difference is that there is no algorithm selection box. Note: The Solver window, shown in Figure 1-23 in Excel 2011, looks pretty similar in Excel 2010, 2011, and 2013. ![]() Now you're ready to build the model, so bring up the Solver window by pressing the Solver button on the Data tab. In cell C16, sum up the number of items to be bought above as:Īnd 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: Excel considers blank cells to be 0s so you needn't fill in these cells with anything to start. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |