Excel - Functions

Background Information

A function in Excel is an equation that performs calculations of values.  Functions can be used to perform simple arithmetic (addition, subtraction, etc.) or advanced statistical calculations.  The steps for creating a function are not difficult, but they must be followed precisely in order to receive the proper end result.

Function Syntax

Creating a Function:

  1. Select a blank cell where you want the answer to display
  2. Type an equal sign
  3. Type or select the cell address in the formula
  4. Insert math operator symbols as necessary
  5. Type or select additional cell address (if applicable)
  6. Press enter.
    • Review an example of creating a function within the pdf attachment.

Order of Operations:

Whenever a function or formula contains more than one mathematical operation, Excel uses the Order of Operations to determine the correct order to make the calculation. An acronym for the Order of Operations is: PEMDAS. Multiplication and D​​​​​ivision have the same level of order. Addition and Subtraction have the same level of order.

Operation

Symbols

Example

Parenthesis

( )

()

(3+4) * 2 = 14

Exponents

X2

^

3^2 = 9

Multiplication

X

*

3 * 4 = 12

Division

÷

/

10 / 2 = 5

Addition

+

+

3 + 4 = 7

Subtraction

-

-

8 – 6 = 2

 

Cell Addresses

A cell is identified within Excel based on its Column and Row (e.g., A1, D14, C80).  When Excel is making a calculation it needs to know where to find all the cells that are being used.  Excel uses two methods to locate cells, relative and absolute cell addresses.

Relative Addresses: 

A relative address shows Excel how to find a cell relative to another cell.  An analogy is giving someone directions:

  • North 3 blocks and west 8 blocks

  • Straight one mile, then take a right at the lights

Relative addressing is the default setting and is the most common way to copy functions and formulas.

Absolute Addresses:

An absolute address tells Excel the exact locations of a cell, regardless of your location.  Using the analogy of giving directions, this is like giving someone an exact intersection:

  • The corner of 101 & McKellips

  • The intersection of 7th St and Thomas

Review the attached pdf for examples of relative and absolute addresses.

Autofill: Copying and Pasting Functions and Formulas

Many times, a function or formula needs to be repeated. Instead of re-typing the function or formula, you can copy by using the autofill feature.

Copying a Function or Formula:

  1. Move your mouse over the bottom-right corner of the cell to be copied. The cursor will change into a plus sign.
  2. Click and drag over the cell range where the function is to be copied
  3. Release the mouse button. The function will be copied
  4. If applicable, adjust the Auto-Fill options (Copy Cells, Fill Formatting Only, Fill Without Formatting, etc.) by using the drop-down in the lower-right corner of the copied cells.
    • Review an example of copying a function or formula within the pdf attachment.

 

To review images or save/download/print a copy of this Job Aide, please select the pdf attachment.

Print Article