Using Formula Functions

Use formula functions to calculate common expressions such as Average, Minimum, Maximum, etc. The Function Wizard makes it easy to quickly enter formulas; however we recommend you read the first part of this article as well to better understand cell ranges and the format used for most formula commands. The more you know about commands, the easier they are to troubleshoot.

Cell Ranges  

For most of your formula functions you will need to refer to a sequential range of cells, e.g. to add all data from cell C3 to C200.   Cell ranges are indicated using the following format:

(FirstCell:LastCell), e.g. (C3:C200)

Note that the separator between the two cells is a colon (:) not a semi-column (;).  picky, picky, picky

Entering Formula Functions

You can enter a formula function in one of three ways:

  1. Use a button such as AutoSum. The AutoSum button will automatically insert several common commands such as Sum, Average, Count, Min and Max.


  2. Type the function into the cell where you wish to view the result - see Typing Functions below.


  3. Use the Insert Function button to select the function and insert the required variables (it's actually easier than it sounds) - see Inserting Functions below.

Typing Functions

Even if you prefer to use the Insert Function Wizard to enter your formulas it is a good idea to understand the components of a function and how to type them in yourself - this will make editing your functions easier.

To type a function:

  1. Decide which cells you wish to effect and the type of formula desired, i.e. Sum, Average, Count, etc.


  2. Select the cell where you wish the result to go.


  3. Type the = symbol followed by the name of the function then a left-parenthesis, e.g. =AVERAGE(
  4. The = symbol tells Excel you want to enter a formula.

  5. Enter the range of cells you wish to effect (see cell ranges above). You can also drag your mouse over the cells or select the cells in this step and Excel will insert the range for you. Your formula should look something like this now =AVERAGE(C3:C10)


  6. Press Enter and voila! the result is displayed.

The following is a list of common formula functions you will probably want to include in your worksheets.

=AVERAGE(range)    e.g. =AVERAGE(C3:C200)

displays the average value in a range of cells

=COUNT(range)       e.g. =COUNT(C3:C200)

counts the number of entries in a range of cells

=MAX(range)           e.g. =MAX(C3:C200)

displays largest value in a range of cells

=MIN(range)            e.g. =MIN(C3:C200) 

displays smallest value in a range of cells

=SUM(range)           e.g. =SUM(C3:C200)

displays the total of the values in a range of cells (also inserted by the AutoSum button)

Inserting Functions

An easy way to insert formula functions is to use the Insert Function Wizard. The Wizard allows you to select the formula you want to use then continues by prompting you for the required variables.

  1. Decide which cells you wish to effect and the type of formula desired, i.e. Sum, Average, Count, etc.


  2. Select the cell where you wish the result (answer) to go.


  3. Display the Formulas Ribbon. Notice the buttons at the left of the ribbon:
  4. If you know the category of the function you wish to select, such as Date & Time, click on that button icon within the Function Library area of the Formula ribbon to select a function - or - click on the Insert Function button located at the left of the ribbon. A window similar to the following is displayed:
  5. Select the function you wish to use under Select a function then choose OK. Also note the following:

  6. The resulting window depends on the function selected in step 4. The following window displays the next-step window when the Average function is selected.
  7. Complete the window, as appropriate for the function selected then choose OK.
  8. Most functions require that you enter the range of the numbers you wish to include beside a field entitled Number1. If you wish to include more than one range of cells in the formula, e.g. Sum A1 to A10 and A20 to A21, enter the first range beside the Number1 field then tab to the Number2 field to add the next range. Most formula functions will allow up to 30 separate ranges.

    The easiest way to enter a range into a field is to click in the field then select the cells on your worksheet.

Additional References