Functions Used in Excel

This worksheet is designed to give you some practice in designing formulas (15 questions) and evaluating formulas (40 formulas). Hand in to your lab instructor the 15 formulas and the 40 values from the assessment of the given forulas.

Creating some formulas can be very time consuming. Some of the more common types of formulas are already done for us by Excel, and these are called FUNCTIONS. Functions save time and increase accuracy because they reduce the number of keystrokes and the chance for error. A function contains a predefined set of instructions that you issue by using a function call (naming the function) and then telling Excel where to find the data to act on. All Excel functions begin with an equal sign if the function is at the beginning of a formula. Excel has over 300 functions. Most of these can be grouped into one of the following categories:

  • Database - areas of a worksheet are organized as a database
  • Date and Time
  • Financial - calculate payments on a loan, depreciation, present and future values, internal rate of returns, net present value and other annuity calculations.
  • Information - functions to analyze cells, columns, rows, ranges, and areas. ISBLANK is one that determines if the cell is blank.
  • Logical - IF function
  • Lookup and Reference - used to retrieve a value or cell reference. (LOOKUP, MATCH, INDEX)
  • Math and Trig
  • Statistical - T-test, Chi-Squared, and F-test among many others
  • Text - find or edit text. LOWER can be use to change the text to lowercase. PROPER to convert the text to lowercase with the first character capitalized

    • A typical Excel function consist of these parts:

    =FunctionName(Arguments)


    The input to a function consists of the arguments (if any) contained in parentheses. An argument is a piece of data that is used as input to a function. The meanings of these arguments vary from function to function. If multiple arguments appear, they are separated by commas. Sometimes arguments consist of a range of cells; other times, they can have completely different roles. The point to remember is that the function itself has all the built-in routines for producing the correct answer given the correct input.

    Obtaining Detailed Information about any Excel function

  • Choose, Help, Microsoft Excel Help Topics.
  • On the Contents tab, select Reference Information, then select Worksheet Functions Listed by Category.
  • Select the category and then the function that you want help on.

    • Using the Function Wizard

    Creating functions can seem difficult, especially with the potentially different ways to spell a function name (AVG, AVE, AVERAGE) and the potential number of arguments possible. Use the Function Wizard to make your job much easier.

    To insert a function and its arguments into a worksheet:
  • Select the cell where you want to enter the function. If you are entering a formula in the formula bar, move the insertion point to where you want the function inserted
  • Choose Insert, Function
  • Select the type of function you want from the Function Category list (listed above).
  • Choose the specific function that you want from the Function Name list box. Read the description in the lower part of window to check that you want this function.
  • Choose the Next button.
  • Enter the arguments in each argument text box. You can type the cell references or numbers, click on the cell to enter, or drag across multiple cells to enter. Notice the description of each argument as you select the text box.
  • You can then choose back, finish or cancel.

    • Date and Time Functions

    To keep track of the date and time, Excel counts the days that have passed since January 1, 1900. Excel uses a date serial number that starts with January 1, 1900 as day 1, and all days from this date forward are numbered.

    A basic need of any order form is to insert the current date on each order. Rather than change the existing date or type it for each order, you can insert a function that will automatically insert the current date in a selected cell. To do this, you use the NOW function, which inserts a serial number that Excel uses to determine the current date and time.

      Inserting a Date
    1. Select the cell where you want the date to appear.
    2. Click Insert then click Function. The Insert Function Dialog opens.
    3. Click the Date and Time radio button, click NOW, click the Insert button, then press the Enter Key.
    4. With the cell still selected, click Format, click Number, and then click Date. Select the desired data format, then click the OK button.

    Date and Time Matches

    When you search for dates by using comparison criteria, use the comparative operators (= > >= < <= <>). Type the dates the same way you would type them for a worksheet cell. For example, to search a list for dates greater than October 16, 1998, you could enter the following criteria for the DATE field as either "10/16/98" or "16 Oct 98". You can use a date that exits in any of Excel's predefined date formats in the criteria. (look under Format->cells ...->date)

    Arithmetic Functions

    There are many functions in Excel that work on numbers. Here are a few that we will use for this course.

    1. =Sum(number1, number2, ...) adds all the numbers in a range of cells Remember numberinteger can be a single cell, a range, a value, or a formula (minus the =)
    2. =Average(number1, number2, ...) Note the number used in averaging (the divisor) is the number of numberinteger
    3. =MAX(number1,number2, ...)
    4. =MIN(number1,number2, ...)
    5. =COUNT(value1,value2 ...) - counts the number of cells that contain numbers and numbers within the list of arguments The syntax for the count parameter says valueinteger. Count "looks" into the cell reference to determine it there is a "value" placed in it. If nothing or a character is in this cell it does not "count" it.
    6. =COUNTA(value1,value2 ...) - counts the number of cells that are not empty
    7. =COUNTBLANK(value1,value2 ...) - counts the number of cells that are blank
    8. =COUNTIF(range, criteria) - counts the number of cells within a range that meet the given condition. To count the number of cells in the range A1:A12 that contain the value 12,
      enter the formula =countif(A1:A12, 12) To count the number of cells in the range A1:A12 that contain the either the value of 1 or value 12,
      enter the formula =countif(A1:A12,1) + countif(A1:A12, 12) To count the number of cells in the range A1:A12 that contain a value from 1 to 12,
      enter the formula =countif(A1:A12,">=1") - countif(A1:A12, "<=12") To count the number of cells in the range A1:A12 that contain a word, for example "yes"
      enter the formula =countif(A1:A12,"yes")

    Logical Functions

    1. The If function is a logical function.
    2. The If function requires the spreadsheet to make a decision based on the condition of the if function.
    3. The If function evaluates data based on the "If-then_else" principle. If something is true, then one action is take, but if it's not true, a different action will be taken.
    4. =IF(C3 = 0, 0, C2/C3). In this formula we are checking if the denominator is zero. If it is, we should not divide by zero, but insert the value zero. If C3 does not contain zero, Excel will divide the c2/c3 and place it in the referenced cell.
    5. =IF(ISBLANK(C3),"NO VALUE",C3)
      In this formula, Excel first checks to see if there's anything in cell C3. If there is not, the condition is true and Excel puts the text string NO VALUE in the cell. If the cell is not blank (has something in it), Excel returns FALSE and the value listed C3 is displayed. cell.

    Give the formulas:

    1. Give the formula to find the smallest number in cells A10 through A40.
    2. Give the formula to find the largest number in cells A10 through A20
    3. Give the formula to find the sum of the value in cells B25 through A40 and in the cells C10 through C14
    4. Give the formula to find the average of cells A5 through A15
    5. Give the formula determine if A3 is greater than or equal to 25. If it is, multiply A3 by 5, otherwise multiply A3 by 15.
    6. You want to test cell A12 to see if it less than 1000. If it is, add 10 to cell A12, otherwise subtract 10. Give the formula.
    7. If the subtotal for an order (cell A4) is greater than $100, a 10% discount will be given else print a message stating NO discount. Give the formula.
    8. You are dividing cell A3 by cell A2, give the formula to check that A2 is not zero.
    9. Give the formula to test that two text strings, one in cell C4, the other C5 are equal.
    10. Enter the current date in a cell.
    11. Count the number of values in the cells A5 though B10
    12. Count the number of Ys in the range A4 through A22
    13. Count the number of values in the cells B30 though C40 that are between 5 and 10.
    14. Count the number of blanks in the cells B30 though C40.
    15. Count the number of nonblank cells in the cells B30 though C40.

    Using the following spreadsheet determine the value:



    Assume the following spreadsheet configuration
     ABCDE
    110 0 2
    2-10308033
    3209.2A44
    4-5.1B55
    5 .05C55
    6upUPDOWNDowndown

    Formulas:

    1. =A1+A2
    2. =A2*B3
    3. =if(A4,A2/A4,A2)
    4. =A1+$A$2
    5. =if(A2 >10,$A$2*B3,A2*B3)
    6. =A2/$A$4
    7. =A3+B$1
    8. =D1/D2
    9. =AVERAGE(10+20,A2+A3)
    10. =SUM(A1:A5)
    11. =SUM($A$1:A5)
    12. =SUM(A1,A5)
    13. =SUM(A1,A3:A5)
    14. =MIN(A1:A5)
    15. =MAX(A1:A5)
    16. =SUM(C1:C5)
    17. =MIN(C1:C5)
    18. =MAX(C1:C5)
    19. =AVGERAGE(B1:B5)
    20. =AVGERAGE(B1:B5,B4)
    21. =A1+B2*D4
    22. =IF(ISBLANK(B1),"BLANK", "NOT BLANK")
    23. =IF(ISBLANK(B2),"BLANK", "NOT BLANK")
    24. =IF(A2<0,IF(A4<0,"both negative","A2 negative"),"A2 positive")
    25. =IF(C2>0,C2*$B$4,B3)
    26. =IF(C3="a","EQUAL","NOT EQUAL")
    27. =IF(C3="A","EQUAL","NOT EQUAL")
    28. =IF(SUM(A1:A5)>SUM(E1:E5),"GREATER", 0)
    29. =IF(C3>C4,"greater than","not greater than")
    30. =LOWER(C6)
    31. =PROPER(D6)
    32. =IF(A6=B6,"EQUAL","not equal")
    33. =IF(A1>10,IF(B2>10,B2+A1,B2*A1),B2-A1)
    34. =IF(SUM(A1:E1)>SUM(B2:E2),SUM(A1:E1),SUM(B2:E2))
    35. =COUNT(A1:E6)
    36. =COUNTA(A1:E6)
    37. =COUNTBLANK(A1:E6)
    38. =COUNTIF(A1:E6,0)
    39. =COUNTIF(A1:E6,"DOWN")
    40. =COUNTIF(A1:E6,"<=0")