1 / 16

Lesson 16: Working with Formulas and Functions

Lesson 16: Working with Formulas and Functions. Learning Objectives. After studying this lesson, you will be able to: Create formulas to calculate values, utilizing the proper syntax and order of operations

presta
Télécharger la présentation

Lesson 16: Working with Formulas and Functions

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. Lesson 16: Working with Formulas and Functions

  2. Learning Objectives • After studying this lesson, you will be able to: • Create formulas to calculate values, utilizing the proper syntax and order of operations • Employ a variety of methods to use the IF logical function and statistical functions that determine the sum, average, count, maximum, and minimum of a range of numbers • Use relative, absolute, and mixed cell references in formulas • Modify and copy formulas • Display the formulas contained within cells rather than the resulting values

  3. Formulas and Functions • Formula • Simply a math problem done in Excel • Function • Prewritten formula that helps simplify complex procedures (for numbers and text)

  4. AutoSum • Automatically sums a column or row of numbers Here, cell B9 was active and then the AutoSum command was issued; Excel will propose to add the numbers above the cell

  5. Status Bar Functions & Customization Right-clicking on the status bar displays a menu from which you can add items to or delete them from the Status Bar Notice the functions displayed on the status bar

  6. Creating Formulas • Start with “ = ” • Use cell and range references when possible • Arithmetic operators in formulas: • Addition: + • Subtraction: - • Multiplication: * • Division: / • Exponentiation: ^ • Percent: % • Grouping: ( )

  7. Cell and Range References • Advantages: • When references are used, formulas can be copied to other cells • Since a reference refers to a cell or a range of cells, the formula results are automatically recalculated when the data is changed in the referenced cell(s)

  8. Please Excuse My Dear Aunt Sally • Determines the order of operations in formulas: • Parentheses • Exponents • Multiplication • Division • Addition • Subtraction Tip! To remember the order of operations, use the pseudonym “Please excuse my dear Aunt Sally.”

  9. Using Cell References in Formulas • Relative: When you copy a formula, the cell references update automatically and refer to new cells relative to the new formula cell • Absolute: Absolute references always refer to the same cell, regardless of which cell the formula is moved or copied to Relative cell reference Absolute cell reference Note! Absolute cell references are denoted with $ signs.

  10. Modifying Formulas • Can modify in one of two places: • Formula Bar • In cell Notice the insertion point in the active cell (B6) that is being edited Referenced cells are outlined in various colors

  11. Copying Formulas • Copy and Paste • OR • AutoFill If you use the fill handle, you can click the AutoFill Options button to choose how to copy or fill the information from the source to destination cells

  12. Displaying Formulas • Choose to display formulas, rather than the resulting values, in cells Tip! Use the Show Formulas button ( ) to toggle between displaying formulas and values

  13. Formula AutoComplete • Jumps into action once you type “ = ” and the beginning letters of a function in a cell Choose from the wide variety of functions suggested by Formula AutoComplete

  14. The Function Wizard • Clicking the Insert Function button displays the Insert Function dialog box The Function Arguments dialog box allows you to determine the cell or range of cells to be included in the function

  15. Creating Formulas with the IF Function • IF function displays a value or message based on a logical test you design. 15

  16. Lesson 16: Working with Formulas and Functions

More Related