Background Information
An Excel formula performs calculations or otherwise manipulates values within a spreadsheet. Among other things, formulas can be used to total numbers, find averages, count cells, and combine cells of information. This job aide outlines how to create some of the most common formulas in Excel.
Writing a Formula
- Select a blank cell where you want the answer to display.
- Type an equal sign.
- Type the function name followed by an open parenthesis.
- Type or select the range of cells to be included in the function.
- Type a closing parenthesis.
- Press Enter on the keyboard.
- The starting cells displays the return value of the formula.
What is a Range?
A “range” is a term used in Excel that references continuous cells in rows and columns. There are two ways to select a range for a formula reference:
- Left-click and drag your mouse along the range’s top row and last column.
- Type the upper-left cell’s name (B2), followed by colon (:), then the cell name of the lower right cell (D5).
- Review the example of this in the attached pdf.
Examples of Formulas
|
Function
|
Example
|
Purpose
|
|
SUM
|
=SUM(A3:H24)
|
Totals all of the values listed in the range A3:H24
|
|
AVERAGE
|
=AVERAGE(G3:G24)
|
Displays the average value in the range G3:G24
|
|
MAX
|
=MAX(G3:G24)
|
Displays the highest value in the range G3:G24
|
|
MIN
|
=MIN(F3:F24)
|
Displays the lowest value in the range F3:F24
|
|
COUNTIF
|
=COUNTIF(D3:D24,”Security”)
|
Displays the total number of cells that contain the word Security in the range D3 through D24
|
|
CONCATENATE
|
=CONCATENATE(A3, “ “, B3)
|
Combines the values in cell A3, a space (“ “), and cell B3
(often used to combine First and Last names)
|
|
PROPER
|
=PROPER(A1)
|
Converts the word in cell A1 into “sentence-casing”.
(e.g., Jones instead of JONES)
|
|
TODAY
|
=TODAY()
|
Displays Today’s date the spreadsheet.
|
|
AND
|
=AND(G3>100,F5=”Security”)
|
Displays the word TRUE if the value of cell G3 is greater than 100 and the word Security is written in cell F5. Otherwise, the word FALSE will be displayed.
|
Sample Exercises
Open the pdf attachment. Using the spreadsheet under "Sample Exercises," fill in the blanks below to create a formula that answers the questions in the table.
|
Question
|
Example
|
Result
|
|
Find the sum of all the tips of all the employees (H3:H20)
|
=SUM(H3: ________ )
|
$601
|
|
Find the average 2007 pay for all employees (G3:G20)
|
= ________ (G3:G20)
|
$12.36
|
|
Display the highest 2007 pay for all employees (G3:G20)
|
=MAX( ________ )
|
$18.50
|
|
Display the Start Date of the employee who has been employed the longest (E3:E20)
|
=____ (_____:_____)
|
5/5/2001
|
|
Display the total number of cells that contain the word Security (C3:C20)
|
=COUNTIF(______,”________”)
|
5
|
|
Combine cells A3, a space “ “, and cell B3
|
=CONCATENATE(___ , “ “,____ )
|
Phil Jackson
|
To review images or save/download/print a copy of this Job Aide, please select the pdf attachment.