REVIEW Midterm Review ICS 101 Fall 2004
Inserting a Function 0 Description
The Function Wizard 0 Enter arguments into text boxes Collapse button shrinks dialog box if necessary Value returned by the function (answer) is displayed
PMT function 0 PMT(rate,nper,pv,fv,type) • Rateis the interest rate for the loan. If you make monthly payments on a four-year loan at an annual interest rate of 10 percent, use 10%/12 for rate If you make a payment every 2 months then divide by 6 since there will be 6 payments in a year.
PMT function 0 PMT(rate,nper,pv,fv,type) • Nper is the total number of payments for the loan. If your loan expands for 4 years, then use 4*12 for nper. This way you will be making 48 payments during the duration of your loan. If you were to make a payment per year then 4 will be used as Nper.
PMT function 0 PMT(rate,nper,pv,fv,type) • Pv is the present value, or the total amount that a series of future payments is worth now; also known as the principal. This is the amount that you want financing for. Write it down as negative because you owe it.
PMT function 0 PMT(rate,nper,pv,fv,type) • Fv is the future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (zero), that is, the future value of a loan is 0.
PMT function 0 PMT(rate,nper,pv,fv,type) • Type is the number 0 (zero) or 1 and indicates when payments are due. • 0 payments are due at the end of the period • 1 payments are due at the beginning of the period.
0 = PMT(rate,nper,pv,fv,type)
The FV function 0 • Returns the future value of a series of payments • For example, contributions to your 401K or IRA • Based on: • Number of periods • Expected rate of return • Amount invested each period
Using the FV Function 0 • Rate divided by number of periods • Number of payments in all periods • Payments per year (must be negative) Amount of contribution, rate of return, and years contributing are all expressed as assumptions
Using the FV Function 0 $598,905.34
The Goal Seek Command 0 • Allows you to set an end result and vary the inputs (assumptions) to produce that result • Only one input can be varied at a time • All other assumptions remain constant • For example, set a desired monthly car payment • Vary the amount financed • Interest rate and number of months remain the same
Using the Goal Seek Command 0 Enter the cell containing the desired result Enter the desired value Enter the cell containing the desired result Only one value can change at a time
The IF Function 0 • Enables decision making in a worksheet • Requires three arguments: • A condition • A value if the condition is true • A value if the condition is false • Condition must be able to be evaluated as true or false • Uses relational operators (=, <, etc.)
Using the IF Function Incorrectly 0 Value_if_true entered as a conditional test. Function will return True or False
The VLOOKUP function 0 • Allows Excel to look up a value in a table and return a related value • Requires three arguments: • the numeric value (or cell) to look up • the range of the table • the column number containing the value you want to return
VLOOKUP 0 Breakpoints are Sorted
Using the VLOOKUP Function 0 Look up the value found in cell I4, in this case, the semester average This argument tells the function where to look. Absolute references used for the table Look in the second column of the table, NOT in column J
Using the AutoFilter Command Click the dropdown on the Homework column, then select Poor as the criteria
More Functions • HLOOKUP • COUNT • COUNTA
Opening Additional Windows 0 • New Window command (Window menu) opens a new window
Copying to Another Workbook 0 Workbook name is displayed in the title bar. Active window is highlighted Select Sheet1 in Atlanta workbook, hold down the Ctrl key and drag to Summary workbook
Check to Copy To Move or to Copy 0 Move sheet to… In same workbook Move to different workbook
Tabs 0 Use these arrows to see the tabs Right click on the tab, Then select what you want to do You can change the tab order by dragging
Worksheet References 0 • Reference cells in other worksheets in the same workbook • Requires the name of the worksheet before the cell range • Exclamation mark separates worksheet and cell reference • =Atlanta!B3 +Boston!B3+Chicago!B3 • Worksheet reference is absolute • Cell reference canbeabsolute, relative, or mixed • Can be entered by pointing
Using Worksheet References 0 Referenced worksheet is an absolute reference Exclamation mark separates worksheet reference from cell reference
To add references to cells from several worksheets 0 This works to add different cells for each worksheet • <Shift> and select the tab by pointing • Then you can release the shift • Go to the tab and point the cells that you need to use
Shift Grouping Worksheets 0
Grouped 0 Must make changes in the worksheet that has this
AutoFormat Command 0 • Applies a predefined format to selected cells • First select the cells to format… selection
AutoFormat Command 0 • Then, select the AutoFormat.
Formatting the Documentation Worksheet 0 Worksheets are listed and described Title is merged and centered. Font is bold, with an offsetting background Wrap text in this cell, in much the way a word processor wraps text
An External Reference 0 External reference surrounded by brackets Active cell contains references to cells in other workbooks
Create a Workspace 0 Four open workbooks Save as a Workspace Opening the workspace will open all four workbooks and tile them
Inserting Comments 0 Comments provide explanation for values and/or descriptions of formulas
Inserting a Hyperlink http://www2.hawaii.edu/~blanca
Inserting a Hyperlink to a document not yet created Future document name