Functions and Data Organization

1. Chapter 6 Functions and Data Organization

2. Modifying and Creating Cell Styles • If a cell style will be used over and over again it can be modified in the cell styles gallery • Home ⇒ Cell Styles ⇒ right-click a style and select modify • Home ⇒ Cell Styles ⇒ New Cell Style

3. Inserting and Deleting Rows and Columns • To add a row/column • Select row/column where new column should appear • Home ⇒ Insert ⇒ Insert Sheet Rows/Columns • To delete a row/column • Select a cell • Home ⇒ Delete ⇒ Delete Sheet Rows/Columns

4. Practice: Planets • Complete part 1 of 2

5. Using Functions to Perform Calculations • Function: • Performs a calculation that results in a single value • Requires data, called arguments, to perform its calculations • Arguments • Enclosed cell references in parenthesis =SUM(G1:G3) • Most commonly used functions are: • SUM • AVERAGE • MIN • MAX Function Argument

6. Using Absolute Cell References in Formulas • Absolute Cell Reference • A cell that does not change when copied • Contains a dollar sign ($) in front of both the column letter and row number ($A$1) • To create • Press F4 key after entering cell reference • Mixed Cell Reference • Combination of a relative and absolute cell reference • $A1 • Column is absolute • Row is relative

7. Practice: Employee Commission • Complete Employee Commission

8. Inserting a Function into a Formula • Instead of typing a function • Formulas ⇒ Insert Function • Can also be inserted by clicking a button in the Function Library group on the Formulas tab

9. Common Error Values • A cell with an invalid formula displays an error value and a green triangle in the upper-left corner of the cell. • #DIV/0 the formula is trying to divide by zero • #REF the formula contains a reference that is not valid • #NUM a numeric value is invalid, such as a value is too large or too small • #VALUE the formula is using the wrong type of argument, such as a label instead of a value • #### the result of the formula is too wide to fit in the column or the result is a negative time or data value

10. Common Error Values • Some formulas may produce a result, but also display a green triangle in the cell, which indicates a possible formula error • To correct a formula • Select the cell • Click Error Checking to display the error and a list of options • Common Formula Errors • Formula Omits Adjacent Cells • The formula includes a range of values and the range does not include a value in an adjacent cell • Inconsistent Formula in Region • The formula does not match the pattern of formulas near it

11. Common Error Values • To check the entire worksheet for errors: • Formulas → Error Checking • A dialog box will be displayed with options for correcting common errors that are found

12. Practice: Commission Summary • Complete Commission Summary

13. The ROUND Function • The ROUND Function • Changes a value by rounding it to a specific number of decimal places • Different than formatting to a certain number of decimal places • Changes the actual number value while formatting just changes the way the number looks

14. Sorting Data • Sorting • Arranging data in a specified order • Select a range • Data ⇒ Sort A to Z or Sort Z to A • Ascending • Low to high (A to Z) • Alphabetical order • Descending • High to low (Z to A) • Chronological Order • When data is times or dates

15. Practice: Planets • Complete Planets part 2 of 2

16. The IF Function • The IF Function • Used to make a decision based on a comparison • Has 3 arguments • =IF(<comparison>, <value if true>, <value if false>) • =IF(C4<E7, 10, 20) • Can contain • Values • Text • Cell references • calculations • Comparison argument must contain one of the following relational operators • = equal to <= less than or equal to • < less than >= greater than or equal to • > greater than <> not equal to

17. Printing a Large Worksheet • Orientation • Portrait • Allows more rows to be printed on a sheet • Landscape • Allows more columns to be printed on a sheet • Change margins • Insert Page Breaks • Set Print Area

18. Practice: Payroll • Complete Payroll part 1 of 3

19. Amortization Tables and the PMT Function • Amortization • Method for computing equal periodic payments for an installment loan • Car loans • Mortgages • Each portion consists of two parts • A portion to pay interest • A portion to pay on the principal • Amortization Table • Displays the interest and principal amounts for each payment of an installment loan • PMT Function • Used to calculate the equal periodic payment for an installment loan • =PMT(<rate>,<term>,<principal>)

20. Practice: LOAN • Complete Loan

21. Using Multiple Sheets • Multiple sheets • Can be used to organize, store, and link related information • To insert a new sheet • Home ⇒ Insert ⇒ Insert Sheet • To print entire workbook • File ⇒ Print ⇒ click Print Active Sheets ⇒ select Print Entire Workbook

22. Copying and Moving Data Between Sheets • Copying and Moving • Cut, Copy and Paste buttons on Home tab • Select the Source • Home ⇒ Cut/Copy • Click sheet tab of the worksheet that is to receive the copied data • Select destination where data to be pasted • Home ⇒ Paste

23. Practice: CAR SALES • Complete Car Sales

24. Asking “What If?” • What If question • Asks how a value or set of values impacts results • Spreadsheet model • A worksheet that includes related data and formulas for analyzing the data • What If analysis • Used to make predictions • Data ⇒ What-if Analysis ⇒ Scenario Manager • Create possible scenarios • Select Show to display a scenario in the active worksheet • Select Summary to create a scenario report on a separate sheet

25. Practice: FUNDRAISER • Complete Fundraiser