Scribd Logo
Upload

Welcome to Scribd!

  • 155 views

    Excel Formulas

    Uploaded by

    Brandly Nyamapnzi
    This document provides an overview of common Excel formulas organized into categories such as basic math, absolute cell references, conditional statements, lookup functions, text functions, date functions, and more. It lists the formula, function, and an example for each. For example, the basic math section lists the SUM function formula as =SUM(cell range) and an example as =SUM(B2:B9) to add up numbers in a range of cells. Absolute cell references are explained as using $ before the cell reference so that it does not change when copied or moved, such as $A$1.

    Copyright:

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

    Excel Formulas

    Uploaded by

    Brandly Nyamapnzi
    155 views2 pages
    This document provides an overview of common Excel formulas organized into categories such as basic math, absolute cell references, conditional statements, lookup functions, text functions, date functions, and more. It lists the formula, function, and an example for each. For example, the basic math section lists the SUM function formula as =SUM(cell range) and an example as =SUM(B2:B9) to add up numbers in a range of cells. Absolute cell references are explained as using $ before the cell reference so that it does not change when copied or moved, such as $A$1.

    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 common Excel formulas organized into categories such as basic math, absolute cell references, conditional statements, lookup functions, text functions, date functions, and more. It lists the formula, function, and an example for each. For example, the basic math section lists the SUM function formula as =SUM(cell range) and an example as =SUM(B2:B9) to add up numbers in a range of cells. Absolute cell references are explained as using $ before the cell reference so that it does not change when copied or moved, such as $A$1.

    Copyright:

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

    Excel Formulas

    Uploaded by

    Brandly Nyamapnzi
    This document provides an overview of common Excel formulas organized into categories such as basic math, absolute cell references, conditional statements, lookup functions, text functions, date functions, and more. It lists the formula, function, and an example for each. For example, the basic math section lists the SUM function formula as =SUM(cell range) and an example as =SUM(B2:B9) to add up numbers in a range of cells. Absolute cell references are explained as using $ before the cell reference so that it does not change when copied or moved, such as $A$1.

    Copyright:

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

     

    Excel Formulas
    Basic math
    Function Formula Example
    To add up the total =SUM(cell range) =SUM(B2:B9)
    To add individual items =Value1 + Value 2 =B2+C2
    Subtract =Value1 - Value 2 =B2-C2
    Multiply =Value1 * Value2 =B2*C2
    Divide =Value1 / Value2 =B2/C2
    Exponents =Value1 ^ Value2 =B2^C2
    Average =AVERAGE(cell range) =AVERAGE(B2:B9)
    Median =MEDIAN(cell range) =MEDIAN(B2:B9)
    Max =MAX(cell range) =MAX(B2:B9)
    Min =MIN(cell range) =MIN(B2:B9)

    Absolute cell references


    When a formula contains an absolute reference, no matter which cell the formula
    occupies the cell reference does not change: if you copy or move the formula, it refers
    to the same cell as it did in its original location. In an absolute reference, each part of
    the reference (the letter that refers to the row and the number that refers to the column)
    is preceded by a “$” – for example, $A$1 is an absolute reference to cell A1. Wherever
    the formula is copied or moved, it always refers to cell A1.

    Conditional statements
    Function Formula Example
    If statement =IF(logical test, “result if =IF(B2>69,”Pass”,”Fail”)
    the test answer is true”,
    “result if the test answer is
    false”)
    Exact =EXACT(Value1, value2) =EXACT(B2, C2)

    Lookup Within a Range


    Function Formula Example
    Looks up a value in the =VLOOKUP(value, =VLOOKUP(A2,
    leftmost column and table, output $C$2:$D$6,
    Percent Grade
    returns a value in the same column, find 2,TRUE)
    row of the column you closest match?) 0 F
    specify. 60 D
    70 C
    80 B
    Values must be listed in ascending order, as displayed in the table:
    90 A
     

    Pulling things apart


    Function Formula Example
    To select a certain number =LEFT(cellwithtext, =LEFT(A2, 6)
    of characters from the left number of characters to be
    returned)
    To select a certain number =RIGHT(cellwithtext, =RIGHT(A2, 6)
    of characters from the right number of characters to be
    returned)
    Extract information from =MID(cellwithtext, start =MID(A2, 9, 4)
    the middle position, number of
    characters you want
    returned)
    Find text in a field =SEARCH(“text you want =SEARCH(“,”, A2)
    to find”, where you want to
    find it)
    Separate a last name LEFT and SEARCH =LEFT(A2, SEARCH(“,”,
    (Example: Smith, Jane) functions A2)-1)
    Separate a first name MID and SEARCH =MID(A2, SEARCH(“,”,
    (Example: Smith, Jane) functions A2)+2, 20)

    Putting things together


    Function Formula Example
    To combine cells with a =CONCATENATE(text, “ ”, =CONCATENATE(A2, “ “,
    space in-between text) B2)
    To combine cells with a =text & “ “ & text =A2 & “ “ & B2
    space in-between (second
    option)

    Dealing with dates


    Function Formula Example
    Return the year =YEAR(datefield) =YEAR(A2)
    Return the month =MONTH(datefield) =MONTH(A2)
    Return the day =DAY(datefield) =DAY(A2)
    Return the day of the week =WEEKDAY(datefield) =WEEKDAY(A2)
    (1 = Sunday, 2 = Monday,
    3 = Tuesday, etc.)
    To create a date from year, =DATE(year, month, day) =DATE(B2, C2, D2)
    month, and day

    You might also like