Join Curt Frye for an in-depth discussion in this video Adding data to the worksheet, part of Solving Optimization and Scheduling Problems in Excel.
- Solving a Mixture Problem means organizing your worksheet effectively. Once you have the skeleton for your solution entered into a worksheet, you can continue by entering the data that Solver will use in its evaluations. This movie's sample file, NonlinearMixture_Data, is an Excel workbook you can find in the Chapter03 folder of the Exercise Files Collection. I've taken the work that I did in the previous movie and changed column widths and added formatting to make things easier to read.
Now, I can continue on and enter in the data that we'll use as part of our solution. First in cells B5 through D5, I need to enter in the Cost per gallon for each of my inputs. Octane83, in this example cost $1.92, so I'll type 1.92 in B5 and press tab. Then in cell C5 I have a cost of $2.45, so I'll type 2.45, press tab.
Then Octane95 costs $3.05 as a raw material so I'll type 3.05 and enter. Then I'll select cells B5 through D5 and on the HOME tab of the ribbon I'll click the $ sign for the Accounting Number Format. That just indicates in the spreadsheet that these are costs so they're dollar values. Now, in cells B6 through D6, I can enter the number of gallons that I have available of each of these inputs.
For the first I have 54000 gallons, and I'll press tab, then in C6 I'll type 35000 and then in D6 I'll type 20000 and enter. Now I'll select cells B6 through D6 and again on the HOME tab in the Number group, I'll apply formatting, I'll use the Comma Style, and I will decrease the decimal so it shows two zero decimal places. So I have 54,000, 35,000 and 20,000. And that's it! Some problems are like this, you have very few input values and a lot of calculations.
Of course, we cheated a little bit by entering the Target Octanes in cells A11 through A13 and the Input Octanes in B10 through D10 as numbers as opposed to labels. We'll use these values in our formulas to calculate the octanes of our mixtures and in our Solver criteria. Now, if this framework is in place, we can start adding our formulas.
- Finding target values using Goal Seek
- Organizing worksheets
- Finding a solution using Solver
- Solving linear and nonlinear mixture problems
- Solving transportation problems
- Solving resource scheduling or sports scheduling problems
- Analyzing Solver results