Scribd Logo
Upload

Welcome to Scribd!

  • 80 views

    Excel 365-2019 Lesson 4 Presentation

    Uploaded by

    Bernardo Setúbal
    This document provides an overview of formulas in Microsoft Excel. It describes what formulas are and how they are used to perform calculations with values, cell references, and functions. It covers creating and editing simple formulas, using math operators and cell references, absolute and relative cell references, referencing other worksheets, displaying formulas, and using common math, statistical, and logic functions like SUM, AVERAGE, IF, and more. The objectives are to understand and apply the core components of formulas in Excel.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd

    Excel 365-2019 Lesson 4 Presentation

    Uploaded by

    Bernardo Setúbal
    80 views19 pages
    This document provides an overview of formulas in Microsoft Excel. It describes what formulas are and how they are used to perform calculations with values, cell references, and functions. It covers creating and editing simple formulas, using math operators and cell references, absolute and relative cell references, referencing other worksheets, displaying formulas, and using common math, statistical, and logic functions like SUM, AVERAGE, IF, and more. The objectives are to understand and apply the core components of formulas in Excel.

    Original Description:

    aaaa

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    This document provides an overview of formulas in Microsoft Excel. It describes what formulas are and how they are used to perform calculations with values, cell references, and functions. It covers creating and editing simple formulas, using math operators and cell references, absolute and relative cell references, referencing other worksheets, displaying formulas, and using common math, statistical, and logic functions like SUM, AVERAGE, IF, and more. The objectives are to understand and apply the core components of formulas in Excel.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    80 views19 pages

    Excel 365-2019 Lesson 4 Presentation

    Uploaded by

    Bernardo Setúbal
    This document provides an overview of formulas in Microsoft Excel. It describes what formulas are and how they are used to perform calculations with values, cell references, and functions. It covers creating and editing simple formulas, using math operators and cell references, absolute and relative cell references, referencing other worksheets, displaying formulas, and using common math, statistical, and logic functions like SUM, AVERAGE, IF, and more. The objectives are to understand and apply the core components of formulas in Excel.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    of 19

    1

    Microsoft Office
    Excel

    Microsoft Excel
    Lesson 4: Using Formulas

    2
    Microsoft Office
    Excel

    Lesson Objectives
    • describe what formulas are​ and • use mixed absolute and relative cell
    how they are used addresses​
    • create and edit simple formulas​ • display formulas​​
    • use math operators and understand • use common functions​
    the order of calculations • use statistical functions
    • reference other worksheets • use the IF function
    • use absolute and relative cell • use text functions
    references​

    3
    Microsoft Office
    Excel

    Creating and Editing Formulas


    • A formula is simply a calculation involving any combination of values, cell
    references, and/or built-in functions
    • All formulas must begin with an equals (=) sign
    – Data without this sign is considered a value
    – Including a cell address is referred to as cell referencing
    – Examples of a simple formula:
    =A10
    =(100+5%)-(100*0.7)
    =B15*6.2%
    =B15*C15/100

    4
    Microsoft Office
    Excel

    Creating and Editing Formulas


    • To enter a formula:
    – Click in the cell where you want to see the results
    – Type the formula or type and use the point method to select cells
    • Formulas can be copied to other cells
    • displays when a formula is different from others in same column or row
    • Click the smart tag to display a pop-up menu to view the
    perceived error and see options for correcting or ignoring it

    5
    Microsoft Office
    Excel

    Mathematical Operators
    Standard Excel Operators Standard Precedence Rule
    ^ Exponentiation and roots • Brackets or parentheses
    * Multiplication • Negation (for example -2)
    / Division • Exponents and roots
    + Addition • Multiplication and division
    – Subtraction • Addition and subtraction

    6
    Microsoft Office
    Excel

    Cell References
    • To use cell referencing:
    – Type the cell reference (cell address); or
    – Click the cells you want to reference; or
    – Create a formula that uses a function to operate on a range of cells
    • Reference a range of cells using this form:
    <starting cell address>:<ending cell address>
    – Examples
    • C11:E18
    • E18:C11
    • B:C (Columns)
    • 3:3 (Row)

    7
    Microsoft Office
    Excel

    Using Absolute and Relative Cell References


    • Using Absolute Cell References
    – Refers to a fixed (non-moving) location on worksheet
    – To change a relative cell address to an absolute cell address:
    • Type a dollar sign before the row number and/or column letter ($E$5); or
    • Press F4
    – Press once to make both the column and row reference absolute
    – Press twice to make only the row reference absolute
    – Press three times to make only the column reference absolute
    – Press four times to remove the absolute references on both the column and row

    8
    Microsoft Office
    Excel

    Using Mixed Absolute and Relative Cell


    References
    • Cell addresses do not need both absolute column and row references
    • Keep the $ with the column or row that must stay “fixed”
    – if you intend to copy the formula to other cells in the same row and keep the
    column reference locked, place the $ in front of the column letter
    – if you intend to copy the formula to other cells in the same column and keep the
    row reference locked, place the $ in front of the row number

    9
    Microsoft Office
    Excel

    Referencing Other Worksheets


    • Formulas can reference cells in other
    worksheets in the same workbook
    – ‘<worksheet name>’!<cell reference>
    – ! (bang) symbol indicates that the referenced cell is
    located in a different worksheet
    – You must use single quotes if the worksheet name
    includes blank spaces

    10
    Microsoft Office
    Excel

    Displaying Formulas
    • To view the formula in a selected cell:
    – Look in the Formula Bar to see the formula; or
    – Press F2
    • To display all formulas in a worksheet at once:
    – On the Formulas tab, in the Formula Auditing group, click Show Formulas; or
    – On the File tab, click Options to open the Excel Options dialog box, click
    Advanced, then in the Display options for this worksheet area, select Show
    formulas in cells instead of their calculated results, and click OK

    11
    Microsoft Office
    Excel

    Using Math and Statistical Functions


    • Excel provides a built-in library of functions, which enables the calculation of
    long and complex formulas
    • The format is: FUNCTION(arguments)
    The Difference between a Function and a Formula
    Function Formula
    Does not need the “=“ character Always has “=“ as the first character

    Examples: SUM(C5:C15) Example: =A10+5+B3


    MIN(C5:C15) =A15+SUM(E51:E56)
    MAX(C5:C15) =MAX(C5:C15)*2.5%
    AVERAGE(C5:C15) =(B103/C18)-SUM(’NW’!E52:E58)

    12
    Microsoft Office
    Excel

    Using Math and Statistical Functions


    • Arguments can be values (numeric, text or date, depending on the type of
    function) or cell references
    – Most common type of argument is a cell range
    • Many functions allow you to enter a variable number of arguments
    • You can enter a cell range either by typing the cell reference(s) directly, or by
    using the pointing method
    – To use the pointing method, click the first cell and drag to the last cell to select a
    cell range
    • Excel visually identifies the cell range, applying color to each cell included in the cell range

    13
    Microsoft Office
    Excel

    Using the SUM Function


    • The SUM Function
    – You can manually type the SUM function into a cell
    – Examples:
    =SUM(15,25,62,73,63,10,23)
    =SUM(B6:B13)
    =SUM(B6:B7)-SUM(B12:B15)

    – You can also use the AutoSum button in the Ribbon


    – To use the AutoSum button:
    • Click the Formulas tab, then in the Function Library group, click AutoSum
    • Click the Home tab, then in the Editing group, click AutoSum

    14
    Microsoft Office
    Excel

    Using Statistical Functions


    =AVERAGE Calculates the arithmetic mean average of the numbers in the specified
    range (sum total of the cell range, divided by the total number of cells
    containing a number)
    =MIN Finds and displays the lowest numeric value in the specified range

    =MAX Finds and displays the largest numeric value in the specified range
    =COUNT Counts the number of cells in the specified range that contain numeric or
    date values
    =COUNTA Counts the number of cells in the specified range that contain any value
    (numeric, date, or text)
    =COUNTBLANK Counts the number of empty (do not contain any value) cells in the
    specified range
    15
    Microsoft Office
    Excel

    Using the IF Function


    • The IF function evaluates a logical test and performs one of two different
    calculations based on the result
    =IF(logical test,value if true,value if false)

    Logical Test Specify what the IF statement will evaluate


    Value if True If the Logical Test is found to be true, then the result of the IF function
    will be whatever is in this section of the formula
    Value if False If the Logical Test is found to be false, then the result of the IF function
    will be whatever is in this section of the formula

    16
    Microsoft Office
    Excel

    Using the IF Function


    • Some comparison operators you can use are:
    = Equal to
    > Greater than
    < Less than
    >= Greater than or equal to
    <= Less than or equal to
    <> Not equal to

    • Qualifiers are punctuation marks used to identify or define different types of


    data
    – Example: text used in a formula requires double quotes as qualifiers
    =IF(A1=10, “text A”,IF(A1=20, “text B”, “text C”))

    17
    Microsoft Office
    Excel

    Using Text Functions


    LEFT Extract the specified number of characters starting from the left side of the text string​

    Extract the specified number of characters starting from the right side of the text
    RIGHT
    string

    MID Extract the specified number of characters starting from any position in the text string​

    UPPER Convert all characters in a text string to uppercase

    LOWER Convert all characters in a text string to lowercase

    LEN Count the number of characters in a text string

    Combine the text from multiple ranges and/or strings (both functions perform the
    CONCAT​ and
    same task but the number of arguments in each function differ; the TEXTJOIN function
    TEXTJOIN
    offers greater specificity)

    18
    Microsoft Office
    Excel

    Lesson Summary
    • describe what formulas are​ and • use mixed absolute and relative cell
    how they are used addresses​
    • create and edit simple formulas​ • display formulas​​
    • use math operators and understand • use common functions​
    the order of calculations • use statistical functions
    • reference other worksheets • use the IF function
    • use absolute and relative cell • use text functions
    references​

    19

    You might also like