1 / 95

REVIEW

REVIEW. Midterm Review ICS 101 Fall 2004. Inserting a Function. 0. 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. The Function Wizard.

nanji
Download Presentation

REVIEW

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. REVIEW Midterm Review ICS 101 Fall 2004

  2. Inserting a Function 0

  3. Inserting a Function 0 Description

  4. The Function Wizard 0 Enter arguments into text boxes Collapse button shrinks dialog box if necessary Value returned by the function (answer) is displayed

  5. The Function Wizard 0

  6. 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.

  7. 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.

  8. 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.

  9. 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.

  10. 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.

  11. 0 = PMT(rate,nper,pv,fv,type)

  12. 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

  13. 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

  14. Using the FV Function 0 $598,905.34

  15. 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

  16. Goal Seek 0

  17. 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

  18. 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.)

  19. Inserting the If function 0

  20. Just adding the function 0

  21. Using the IF Function Incorrectly 0 Value_if_true entered as a conditional test. Function will return True or False

  22. 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

  23. VLOOKUP 0 Breakpoints are Sorted

  24. 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

  25. Using the AutoFilter Command Click the dropdown on the Homework column, then select Poor as the criteria

  26. Auto Filter, custom

  27. More Functions • HLOOKUP • COUNT • COUNTA

  28. Opening Additional Windows 0 • New Window command (Window menu) opens a new window

  29. Opening Additional Windows 0

  30. 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

  31. Check to Copy To Move or to Copy 0 Move sheet to… In same workbook Move to different workbook

  32. 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

  33. 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

  34. Using Worksheet References 0 Referenced worksheet is an absolute reference Exclamation mark separates worksheet reference from cell reference

  35. 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

  36. Shift Grouping Worksheets 0

  37. Grouped 0 Must make changes in the worksheet that has this

  38. AutoFormat Command 0 • Applies a predefined format to selected cells • First select the cells to format… selection

  39. AutoFormat Command 0 • Then, select the AutoFormat.

  40. Auto Format 0

  41. 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

  42. An External Reference 0 External reference surrounded by brackets Active cell contains references to cells in other workbooks

  43. Create a Workspace 0 Four open workbooks Save as a Workspace Opening the workspace will open all four workbooks and tile them

  44. Create a Workspace 0

  45. Opening a Workspace 0

  46. Inserting Comments 0 Comments provide explanation for values and/or descriptions of formulas

  47. Inserting a Hyperlink http://www2.hawaii.edu/~blanca

  48. Inserting a Hyperlink to another place in this same document B10

  49. Inserting a Hyperlink to a document not yet created Future document name

  50. Inserting a Hyperlink to an e-mail address

More Related