PowerPoint Presentation to Accompany GO! with Microsoft ® Office 2007 Introductory Chapter 11

1. PowerPoint Presentation to Accompany GO! with Microsoft® Office 2007 Introductory Chapter 11 Using Functions and Tables

2. Objectives • Use SUM, AVERAGE, MEDIAN, MIN, and MAX Functions • Use COUNTIF and IF Functions, and Apply Conditional Formatting • Use a Date Function • Freeze Panes and Create anExcel Table

3. Objectives • Format and Print a Large Worksheet • Use Financial Functions • Use Goal Seek • Create a Data Table

4. Constant Review • Using the $ sign when referencing a cell fixes the cell when copying or using auto fill. • You can fix the Column: $C1 • You can fix the Row: C$1 • Or both: $C$1

5. Use SUM, AVERAGE, MIN, MEDIAN, and MAX Functions • A function is a predefined formula that Excel has already provided. • A function performs calculations by using specific values in a particular order or structure. • Functions are grouped into categories such as Financial, Logical, etc.

6. Use SUM, AVERAGE, MIN, MEDIAN, and MAX Functions

7. Use SUM, AVERAGE, MIN, MEDIAN, and MAX Functions

8. Use SUM, AVERAGE, MIN, MEDIAN, and MAX Functions • Functions can be inserted in several ways: • Use the Function Libraryor Insert Functionbutton. • Use the Formula AutoComplete. • Copy a similar function from another cell and edit the arguments. • Select Insert Function from any of the displayed menus in the Function LibraryGroupor press Shift + F3.

9. Formatting multiple, disjoint, cells

10. Use COUNTIF and IF Functions, and Apply Conditional Formatting

11. Use COUNTIF and IF Functions, and Apply Conditional Formatting

12. Conditional Formatting

13. Use a Date Function • TheNOWfunction: • retrieves date and time from your computer's calendar and clock and inserts the information into the selected cell • result is formatted as a date and time • places a sequential number in the cell that corresponds to the date and time

14. Freeze Panes and Create an Excel Table • Freeze Panescommand takes selected rows or columns and then freezes them into place. • The locked rows and columns become separate panes. • A pane is a portion of a worksheet window bounded and separated by other portions, such as vertical or horizontal bars.

15. Freeze Panes and Create an Excel Table

16. Freeze Panes and Create an Excel Table

17. Freeze Panes and Create an Excel Table

18. Format and Print a Large Worksheet

19. Use Financial Functions • Interest = The amount charged for use of borrowed money. • Interest percentage = Rate. • The initial amount of loan = Present value(Pv).

20. Use Financial Functions • Principal = The total amount that a series of future payments isworth now. • The number of time periods (No# of payments) = nper. • The value at the end of the time periods is Future value (Fv). • The future value is usually zerofor loans (need to pay them off).

21. Use Financial Functions

22. Use Goal Seek • Goal Seekis a method to find a specific value for a cell by adjusting the value of one other cell. • Goal Seekworks backward from the desired outcome, to find the input necessary to achieve the goal. • Goal Seekis one of Excel’s What-If Analysis tools.

23. Use Goal Seek

24. Create a Data Table • Data Table: • a range of cells that show how changing certain values will affect the results • makes it easy to calculate multiple versions in one operation, and then view and compare the results • A One-variable data table will change the value in only one cell. • A Two-variable data tables will change the values in two cells.

25. Create a Data Table

26. Create a Data Table

27. Covered Objectives • Use SUM, AVERAGE, MEDIAN, MIN, and MAX Functions • Use COUNTIF and IF Functions, and Apply Conditional Formatting • Use a Date Function • Freeze Panes and Create anExcel Table

28. Covered Objectives • Format and Print aLarge Worksheet • Use Financial Functions • Use Goal Seek • Create a Data Table