Some Topics Not in Excel Brief Edition Textbook
· 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.
· 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.
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
· =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