Day 10: Excel Chapter 8, 9, 10

1. Day 10:Excel Chapter 8, 9, 10 Tazin AfrinTazin.Afrin@mail.wvu.edu September 23, 2013

2. Solver • Solver is an add-in application that manipulates variables based on constraints to find the optimal solution to a problem. • Solver is one of the most sophisticated what-if analysis tools, and people use Solver in a variety of situations and industries.

3. objective and variable cell • The objective cell is the cell that contains the formula-based value that you want to maximize, minimize, or set to a value in Solver. • A changing variable cell is a cell containing a variable whose value changes until Solver optimizes the value in the objective cell.

4. Load solver add-in • Files -> Options-> Add Ins • Manage add-in -> Excel add-in -> Go • Select solver add-in -> Ok

5. Set objective and variable cell • Data -> Solver • Set objective variablecell and value • Set changing variable cells • Close and save the workbook. • Add constraints.

6. solve • Chose a solving method: • GRG Nonlinear: guaranteed local optimal • Simplex LP: guaranteed global optimal, must be linear • Evolutionary: good solutions for non smooth functions • Solve

7. Chapter 9 and 10 • Multiple Worksheets • Group • Fill across worksheets • Enter and format data

8. Hyperlink • Enter data in cell • Insert -> hyperlink • Select link and range of data

9. 3D formulas • A 3-D formula is a formula or function that refers to the same range in multiple worksheets. • 'Worksheet Name'!RangeOfCells =SUM('FirstWorksheet:LastWorksheet'!RangeOfCells)

10. Templates • File->New-> • Recent Templates • Sample Templates • My Templates • Office.com Templates

11. questions ?

12. Thank You Log Off