The VBA code is a bit hard to follow – a lot of nested loops – but the algorithm itself is quite straightforward. Specifically, the Sudoku.xlsm file now has an Algorithm sheet that walks you through the algorithm implemented in the VBA code. ( Note: I changed these files slightly in April 2016. (You can check out the VBA code to see how it works.) And if you want to go a step farther, with 16x16 grids instead of the usual 9x9 grids, check out the Sudoku16.xlsm file. But if you want to see how powerful VBA programs can be, check it out the Sudoku.xlsm file. Automating Sudoku with VBA: Sudoku.xlsm (or Sudoku16.xlsm) If you love the laborious effort of working Sudoku puzzles-the pencil marks, the erasing, etc.-then you should not open these files.Although Wayne's approach is to use Excel formulas, this file uses a VBA program to perform the calculations. Transient queueing analysis: Transient Queue.xlsm Did you know that you can analyze queues with time-varying behavior (e.g., arrival rates that increase during peak periods of the day) with spreadsheets, using analytical approximations, not simulation or steady-state analysis? This is indeed possible, as Wayne Winston has pointed out in the 4th edition of his Operations Research book.The probability of picking all winners will still be infinitesimally small.)
(I didn’t update this file after 2013, but you can if you like. Also, here is a version that calculates the probability of a perfect bracket, i.e., successfully predicting the winner of each game: March Madness Probabilities 2013.xlsx.
NCAA March Madness simulation: Here is the simulation for 2019: March Madness 2019 with Data Table.xlsx.
How To Enable Solver Table In Excel For Mac Shortcut Try it out, but keep in mind that I do not provide support for it.
Installation instructions are in the zip file. It provides only the basics: summary measures and useful statistical charts. This is a mini version of StatPro I created (mostly to sharpen my programming skills). This solution uses all the resources available.O StatBasics for Excel 2007 : StatBasics for Excel 2007.zip. This solution gives the maximum profit of 25600. Check 'Make Unconstrained Variables Non-Negative' and select 'Simplex LP'.Ĭonclusion: it is optimal to order 94 bicycles and 54 mopeds. Click Add to enter the following constraint.Ħ. Enter OrderSize for the Changing Variable Cells.ĥ. You have the choice of typing the range names or clicking on the cells in the spreadsheet.Ĥ. The result should be consistent with the picture below. On the Data tab, in the Analyze group, click Solver.Įnter the solver parameters (read on). To find the optimal solution, execute the following steps.ġ. We shall describe next how the Excel Solver can be used to quickly find the optimal solution.
It is not necessary to use trial and error. This solution has a total profit of 19000. With this formulation, it becomes easy to analyze any trial solution.įor example, if we order 20 bicycles, 40 mopeds and 100 child seats, the total amount of resources used does not exceed the amount of resources available. Total Profit equals the sumproduct of UnitProfit and OrderSize. The amount of storage used equals the sumproduct of the range C8:E8 and OrderSize. Insert the following three SUMPRODUCT functions.Įxplanation: The amount of capital used equals the sumproduct of the range C7:E7 and OrderSize. To make the model easier to understand, create the following named ranges. What is the overall measure of performance for these decisions? The overall measure of performance is the total profit of the three products, so the objective is to maximize this quantity.Ģ. For example, each bicycle uses 300 units of capital and 0.5 unit of storage.Ĭ. What are the constraints on these decisions? The constrains here are that the amount of capital and storage used by the products cannot exceed the limited amount of capital and storage (resources) available. What are the decisions to be made? For this problem, we need Excel to find out how much to order of each product (bicycles, mopeds and child seats).ī. Under Add-ins, select Solver Add-in and click on the Go button. To formulate this linear programming model, answer the following three questions.Ī. To load the solver add-in, execute the following steps. The model we are going to solve looks as follows in Excel.ġ. You can find the Solver on the Data tab, in the Analyze group. Under Add-ins, select Solver Add-in and click on the Go button.Ĥ.