Some Topics Not in Excel Brief Edition Textbook

 

  1. Solver – a tool used to perform what-if analyses to determine the effect of changing values in two or more cells (adjustable cells) on another (target or result) cell. Excel figures out adjustments to the adjustable cells that will result in the desired value in the target cell. Solver can also produce reports about the solution.

·        Click on cell where target of calculation exists – the target cell

·        Choose Tools/Solver

·        Select “Value Of” button

·        Type desired target value

·        Specify adjustable cells in “By Changing Cells” (most easily by clicking on cell and putting comma clicking on cell, etc

·        If constraints are desired/needed, Choose Add, and fill in constraint

a.       Click on cell to choose “Cell Reference”

b.      Use pull-down list to choose >= or <= or = etc

c.       Type value for constraint (e.g. B4 >= 35)

d.      Choose Ok to finish or Add to add another constraint

·        Choose Solve

·        From Solver Results Dialog Box, choose whether to keep the solution or restore the original values. In addition, you can choose to create a report such as an answer report (this will be put in another sheet.)

Solver is like goal-seek except more than one cell is adjustable.

 

 

  1. Scenarios – A scenario is a named set of input values that you can substitute in a worksheet to see the effects of a possible alternative course of action. Scenarios are designed to help forecast the outcome of various possible actions. You can create and save different groups of scenario values on a worksheet and then switch to any of these scenarios to view the results. . You can also create reports in separate sheets that summarize the scenarios you create.

·        Click on cell where target of calculation exists

·        Choose Tools/Scenarios

·        Click Add

·        In Add Scenario dialog box:

a.        Enter the name for the scenario

b.      Select cells that will be adjusted in the scenario by going to “changing Cells” and clicking on cells in spreadsheet (comma between each)

c.       Change comment if desired

d.      Click Ok

e.       In Scenario Values dialog box, type values for cells in the current scenario

f.        Click ok to finish or Add  to add another scenario

g.       (repeat above steps if you choose Add)

·        To see different scenarios’ results, in Scenario Manager dialog box, select scenario name and click “Show” (or double-click scenario name)

·        To create a scenario summary report, choose Summary from Scenario Manager dialog box, then specify the range of cells containing results (by selecting). The report will be created in another sheet.

 

 

  1. XY Scatter Plot – with line fitting

This is used when data is samples, to try to predict what other unseen data points might be.

·        Select the data to be plotted

·        Start Chart Wizard

·        Select Scatter (no line)

·        Do anything necessary in wizard – titles, axes, gridlines, legend etc

·        After finishing wizard, choose menu Chart > Add Trendline

·        Choose type of trendline (linear, polynomial, logarithmic, …)

·        Select Options tab

·        Check – Display equation on Chart

·        Check – Display R-squared value on chart

 

 

  1. IF function – The IF function is used in a formula – it checks if certain conditions are met and then takes action based on the results of the check. The syntax is:

·        =IF(logicalTest,valueIfTrue,valueIfFalse)

The logical test is a mathematical “expression” that makes a comparison using “logical operators” -  =             <             >            >=            <=            <>            AND            OR            not

The parts can be built up with some help using the Paste Function button

Example:

            A            B         

1            Hours            OT

2            42            =IF(A2>40,(A2 – 40) * 0.5, 0)

3            38            =IF(A3>40,(A3 – 40) * 0.5, 0)

 

B2 is filled with 1.0

B3 is filled with 0