Formulas and Functions

Excel Formuals and Functions Topics Covered

Three Rules for Successful Formulas

Formulas always start with an "=" sign
Never use numbers in a formula; use cell references
Remember PEMIDAS (Order of Operations)

Cell References:

Ranges of cells or a cell within a worksheet are useful to recognize data for use in formulas to calculate results based on the data. The cell reference is identified by viewing the intersection of the row and column headings.
Example: (image to right) The cell reference is B3

A range of cells on a worksheet is represented by: B2:B4

Graphic of six different bars using a mnemonic to remember the steps of PEMDAS. The first bar is dark red and says Please (P) and connects to a lighter red bar that says parenthese. Below that is a darker green bar that says Excuse (E) and is connected to a lighter green bar that says Exponent. Below that, is a darker purple bar that says My (M) connected to a lighter purple bar that says Multiply. Below that is a dark blue bar that says Dear (D) and is connected to a lighter blue bar that says Divide. Below that is a darker orange bar that says Aunt (A) and is connected to a lighter orange that says Add. Below that is a dark red bar that says Sally (S) and is connected to a lighter red bar that says Subtract.

Screenshot of an Excel workbook. Across the top, there is a gray bar with three columns. The columns are A, B, and C. Down the left side, there is a vertical bar that has rows 1,2,3,4, and 5. The first row says Monthly totals (A1) Workorders (B1) Calls (C1). The next row is 1/11/2011 (A1), 65 (B2), 50 (C2). The next row is 2/11/2011 (A3), 145 (B3), 35 (C3). The next row is 3/11/2011 (A4), 35 (B4), 20 (C4). The nxt row is Total (A5), 245 (B5), and 105 (C5).

Absolute Cell Reference

Categorized by a $, absolute cell reference points to an exact location on a worksheet, no matter where the formula is moved.

Absolute cell referencing involves nothing more than placing a dollar symbol ($) before each reference value (letter and number).
- In your formula insert a dollar symbol before each letter and number to insure absolute (lock) cell reference.
- Example: =A1+A2 for absolute cell reference should be written =$A$1 + $A$2

Absolute Reference Shortcut = F4

Formula Bar

Screenshot of the formula options within Excel. There is a gray bar and a white box. In the gray bar, there is a black "x" icon, a black checkmark icon, and a lowercase "f" with a small "x" icon. In the white box, there is text that reads =enter formula here. Above, there is text that reads Cancel formula with a red line with an arrow that points to the black "x". To the right, there is text that says Enter formula with a red line and arrow that points to the black checkmark icon. Below that is text that reads Insert Function button with a red line and arrow that points to the "f and x" icon.

Inserting a Formula

Select the cell that will contain the formula results.
Enter the formula using the Formula Bar or the cell and press Enter. Remember all formulas and functions begin with an equals sign.

Screenshot of an Excel Workbook. Across the top is a white dropdown bar with A4 in it. To the right is a gray bar with a black "f" and a small "x". To the right of that is a white bar with the text, =(A1+A2+A3). To the right is text that says Formula and points to that text. Below that are headers: A, B, C, D, and E. Down the left side, there is text that has 1,2,3,4, and 5 down the side. The first column has the following numbers in each cell: 65, 145, 35, 245. There is text below the table that says Formual Results and points to the last number.

Functions

Pre-defined formulas simplify the process of adding common and/or complex calculations to worksheets.

Frequently Used Functions:

AVERAGE- calculates average =AVERAGE(A1:A20)
COUNTA – counts # of cells containing data =COUNTA(A1:A85)
SUM- calculates sum total =SUM(A1:A20)
MAX-returns the highest value =MAX(B11:G11)
MIN-returns the lowest value =MIN(B11:G11)

Insert a Function

With the cell selected that will contain the function result, from the formula bar, click on the Insert Function icon, select a function from the Insert Function dialog box and click OK.

Or

From the Formulas tab browse through the categories drop-down menu.

Screenshot of Excel menu options. Across the top is a transparent blue bar. There are headers on top of the banner: File (has a green background), Home, Insert, Page Layout, Formulas, Data, Review, View. Formulas is selected. Within the Formulas tab, there are a series of options. The first option is Insert Function (with a black lower case "f" and a small lower case "x"), There is a line to the right of this icon and the next. The second icon is the summation icon with text that reads AutoSum and a black triangle pointed down. To the right is Recently Used (with a blue book and a yellow star on it). To the right is a green book with a dollar and yellow coins on it with text that says Financial and has a black triangle pointed down. To the right of that is a purple book with a dark purple question mark on it with text that says Logical and a black triangle pointed down. To the right of that is a yellow book with a dark blue "A" on it. The text below it says Text and it has a black triangle pointed down. To the right of that is an orange book with a square calendar icon on it. The text below it says Date & Time and it has a black triangle pointed down. To the right is a blue book with a magnifying glass on it. The text below says Lookup & Reference with a black triangle pointed down. To the right of that there is a green book with an "O" shape icon on it. The text below says Math & Trig and it has a black triangle pointed down. To the right of that are two orange books and the text below says More Functions. It has a black triangle pointed down. Below all of the icons, there is text that reads Function Library

Troubleshooting Formulas & Functions

If the worksheet contains an error from the Formulas tab, within the Formula Auditing group, click on the Error Checking command and the Error Checking dialog box will open.

Using the Next and Previous buttons to view errors or options, editing within the Formula Bar to make changes to the broken function or formula.

TechRepublic - 10 tips for troubleshooting Excel formulas and functions

When using educational technology tools, be FERPA aware. To learn more, visit the Registrar's FERPA Guidelines site.