1 / 17

Decision Making and Worksheet Operations

Learn about decision making functions, goal seek command, worksheet operations, and printing worksheets in this chapter. Explore financial, statistical, and decision making functions and how to use them effectively. Practice exercises included.

eppersonj
Download Presentation

Decision Making and Worksheet Operations

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. Chapter 4 Decision Making

  2. Agenda • Function • Goal Seek command • AutoFilter command • Worksheet operation • Printing worksheet

  3. Function - Definition • A predefined computational task • Requires arguments: values the function uses to calculate answers • Returns a value

  4. Function - type • Financial functions (PMT and FV) • Statistical functions (MAX, MIN, AVERAGE, COUNT, and COUNTA) • Decision making functions (IF, VLOOKUP, and HLOOKUP) • Goal Seek command

  5. Financial Function • PMT • Calculates a periodic payment of a loan payment • Based on: interest (monthly), rate, number of periods (month), and amount financed (negative) • FV • Returns the future value of a series of payments such as contributions to a 401K or IRA • Based on: number of periods (year), expected rate of return (yearly), and amount invested each period (negative)

  6. The Goal Seek Command • Allowing to set an end result and vary the inputs (assumptions) to produce the result • Only one input can be varied at a time • All other assumptions remain constant • Tool menu, goal seek

  7. Statistical Function • MAX, MIN, and AVERAGE functions • Return highest, lowest, and average values from an argument list • Argument list may include cell references, cell ranges, values, functions, or formulas • Cells that are empty or contain text are not included • COUNT and COUNTA functions • COUNT returns number of cells containing numeric entries or formulas that return a number • COUNTA also includes cells with text

  8. Decision Making Function • VLOOKUP: 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 criteria and return value) • The column number containing the return value

  9. Decision Making Function • IF: enables decision making • 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 • Relational operators (=, <, etc.)

  10. Function - Creation • Insert menu, function • Select the name of the function • Use Wizard to enter the arguments • Point to enter cell references • the Collapse button to collapse or display the dialog box

  11. Functions versus Formulas • Function • Automatically adjusted as rows or columns being deleted or added within the range referenced by the function • Formula • Adding a row adjusts the cell references in the formula, without including the new row in the formula • Deleting a row causes a #REF error message

  12. The AutoFilter Command • Display a selected set of rows within a worksheet • Display rows that meet selected criteria • Other rows are hidden, not deleted • Data menu, Filter, AutoFilter • Select criteria title, the dropdown arrow

  13. Worksheet Operation • Use relative and absolute references correctly • Relative cell references for changing value to copy a cell or cells • Absolute references for the constant value to copy a cell or cells • F4 key • Mixed references • Either changing the row or the column • Isolate the assumption • Formulas in cells refer to the assumptions area, not to the actual values

  14. Worksheet Operation • Scrolling causing the screen to move horizontally or vertically as changing the active cell • Drag the horizontal or vertical scroll bars • Click above or below vertical scroll bars • Click to the left or right of horizontal scroll bars • Fill handle for copying • Freezing Panes allowing row and column headings to remain visible while scrolling • Window menu, freeze panes or unfreeze panes • Hiding rows and columns making rows and columns invisible on the monitor or printed paper • Select the column or columns, right click, hide or unhide • Select the row or rows, right click, hide or unhide

  15. Printing Worksheet • Page Preview command (View menu) • Page Setup command (File menu) lets you change how the sheet prints • Change from portrait (8 ½ x 11) to landscape (11 x 8 ½) • Change margins • Scale the worksheet to print on one sheet

  16. Points to Remember • Function • Type • Creation • Goal Seek command • AutoFilter command • Worksheet operation • Printing worksheet

  17. Assignment • Practice exercises: 3, 7 and 10 • Due date:

More Related