Worksheet Formulas and Functions
Worksheet formulas are tools used in cells to calculate results.
|How Formulas Calculate Values||A formula is an equation that analyzes data on
a worksheet. Formulas perform operations such as addition, multiplication, and comparison
on worksheet values; they can also combine values.
Formulas can refer to other cells on the same worksheet, cells on other sheets in the same workbook, or cells on sheets in other workbooks.
|The following example adds the value of cell
B4 and 25 and then divides the result by the sum of cells D5, E5, and F5.
= denotes a formula
B4 is a cell reference
+ is the Additional Operator
/ is a Division Operator
SUM(D5:F5) is a Worksheet Function
D5:F5 is a Range Reference
|Formula Syntax||Formulas calculate values in a specific order
that is known as the syntax. The syntax of the formula describes the process of the
A formula in Microsoft Excel begins with an equal sign (=), followed by what the formula calculates. For example, the following formula subtracts 1 from 5. The result of the formula is then displayed in the cell.
|Formula Cell References||A formula can refer to a cell. If you want one
cell to contain the same value as another cell, enter an equal sign followed by the
reference to the cell.
The cell that contains the formula is known as a dependent cell -- its value depends upon the value in another cell. Whenever the cell that the formula refers to changes, the cell that contains the formula also changes.
The following formula multiplies the value in cell B15 by 5. The formula will recalculate whenever the value in cell B15 changes.
Formulas can refer to cells or ranges of cells, or to names or labels that represent cells or ranges
|Worksheet Function||Microsoft Excel contains many predefined, or built-in, formulas known as functions. Functions can be used to perform simple or complex calculations. The most common function in worksheets is the SUM function, which is used to add ranges of cells. Although you can create a formula to calculate the total value of a few cells that contain values, the SUM worksheet function calculates several ranges of cells.|
|Frequently Used Formulas||The following are examples of some commonly
used formulas in Microsoft Excel.
To calculate the current balance for the first transaction (cell F7):
Calculates the running balance in a checkbook register. In this example, assume that cell D7 contains the current transaction's deposit, cell E7 contains any withdrawal amount, and cell F6 contains the previous balance. As you enter new transactions, copy this formula to the cell that contains the current balance for the new transaction.
To display the full name in the format
To display the full name in the format
Joins a first name stored in one cell with a last name stored in another cell. In this example, assume that cell D5 contains the first name, and cell E5 contains the last name.
Increases a numeric value stored in one cell by a percentage, such as 5 percent.
In this example, assume that cell F5 contains the original value.
If the percentage amount is stored in a cell (for example, cell F2):
The reference to cell F2 is an absolute cell reference so that the formula can be copied to other cells without changing the reference to F2.
Creates a total value for one range based on a value in another range.
For example, for every cell in the range B5:B25 that contains the value "Northwind", you want to calculate the total for the corresponding cells in the range F5:F25.
Creates a total value for one range based on two conditions.
For example, you want to calculate the total value of the cells in F5:F25 where B5:B25 contains "Northwind" and the range C5:C25 contains the region name "Western". Note This is an array formula and must be entered by pressing CTRL+SHIFT+ENTER.
Counts the number of occurrences of a value in a range of cells.
For example, the number of cells in the range B5:B25 that contain the text "Northwind".
Counts the number of occurrences of a value in a range of cells, based on a value in another range
For example, the number of rows in the range B5:B25 that contain the text "Northwind" and the text "Western" in the range C5:C25. Note This is an array formula and must be entered by pressing CTRL+SHIFT+ENTER.
|Calculation Operators in Formulas||Operators specify the type of calculation that you want to perform on the elements of a formula. Microsoft Excel includes four different types of calculation operators: arithmetic, comparison, text, and reference.|
|Arithmetic Operators||Arithmetic operators perform basic
mathematical operations such as addition, subtraction, or multiplication; combine numbers;
and produce numeric results.
operator Meaning Example
+ (plus sign) Addition 3+3
(minus sign) Subtraction 31
* (asterisk) Multiplication 3*3
/ (forward slash) Division 3/3
% (percent sign) Percent 20%
^ (caret) Exponentiation 3^2
(the same as 3*3)
|Comparison Operators||Comparison operators compare two values and
then produce the logical value TRUE or FALSE.
operator Meaning Example
= (equal sign) Equal to A1=B1
> (greater than sign) Greater than A1>B1
< (less than sign) Less than A1<B1
>= (greater than or equal to sign)
Greater than or equal to A1>=B1
<= (less than or equal to sign)
Less than or equal to A1<=B1
<> (not equal to sign) Not equal to A1<>B1
|Text Operator||The text operator "&" combines
one or more text values to produce a single piece of text.
Text operator & (ampersand)
Meaning Connects, or concatenates, two
values to produce one
continuous text value
Example "North" & "wind" produce
|Reference Operators||Reference operators combine ranges of cells
Operator Meaning Example
: (colon) Range operator, which produces one reference to all the cells between two references, including the two references B5:B15
, (comma) Union operator, which combines multiple references into one reference SUM(B5:B15,D5:D15)
(single space) Intersection operator, which produces one reference to cells common to two references SUM(B5:B15 A7:D7)
In this example, cell B7 is common to both ranges.
|Using Cell and Range References||A reference identifies a cell or a range of
cells on a worksheet and tells Microsoft Excel where to look for the values or data you
want to use in a formula.
With references, you can use data contained in different parts of a worksheet in one formula or use the value from one cell in several formulas.
You can also refer to cells on other sheets in the same workbook, to other workbooks, and to data in other programs. References to cells in other workbooks are called external references. References to data in other programs are called remote references.
By default, Microsoft Excel uses the A1 reference style, which labels columns with letters (A through IV, for a total of 256 columns) and labels rows with numbers (1 through 65536).
To refer to a cell, enter the column letter followed by the row number. For example, D50 refers to the cell at the intersection of column D and row 50. To refer to a range of cells, enter the reference for the cell in the upper-left corner of the range, a colon (:), and then the reference to the cell in the lower-right corner of the range. The following are examples of references.
To refer to Use
The cell in column A and row 10 A10
The range of cells in column A and
rows 10 through 20 A10:A20
The range of cells in row 15 and
columns B through E B15:E15
All cells in row 5 5:5
All cells in rows 5 through 10 5:10
All cells in column H H:H
All cells in columns H through J H:J
Depending on the task you want to perform in Microsoft Excel, you can use either relative cell references, which are references to cells relative to the position of the formula (D11), or absolute references ($D$11), which are cell references that always refer to cells in a specific location.
You can use the labels of columns and rows on a worksheet to refer to the cells within those columns and rows, or you can create descriptive names to represent cells, ranges of cells, formulas, or constant values.
If you want to analyze data in the same cell or range of cells on multiple worksheets within the workbook, use a 3-D reference. A 3-D reference includes the cell or range reference, preceded by a range of worksheet names. Microsoft Excel uses any worksheets stored between the starting and ending names of the reference.
|Using the Formula Palette||When you create a formula that contains a
function, the Formula Palette helps you enter worksheet functions.
As you enter a function into the formula, the Formula Palette displays the name of the function, each of its arguments, a description of the function and each argument, the current result of the function, and the current result of the entire formula. To display the Formula Palette, click the = (equal sign) Edit Formula in the formula bar.
You can use the Formula Palette to edit functions in formulas. Select a cell that contains a formula, and then click Edit Formula (=) to display the Formula Palette. The first function in the formula and each of its arguments are displayed in the palette. You can edit the first function or edit another function in the same formula by clicking in the formula bar anywhere within the function.
|Using Functions to Calculate Values||Functions are predefined formulas that perform
calculations by using specific values, called arguments, in a particular order, called the
For example, the SUM function adds values or ranges of cells, and the PMT function calculates the loan payments based on an interest rate, the length of the loan, and the principal amount of the loan.
Arguments can be numbers, text, logical values such as TRUE or FALSE, arrays, error values such as #N/A, or cell references. The argument you designate must produce a valid value for that argument. Arguments can also be constants, formulas, or other functions.
The syntax of a function begins with the function name, followed by an opening parenthesis, the arguments for the function separated by commas, and a closing parenthesis. If the function starts a formula, type an equal sign (=) before the function name.
As you create a formula that contains a function, the Formula Palette will assist you .
|Using the Formula Palette to Enter and Edit Formulas||When you create a formula that contains a
function, the Formula Palette helps you enter worksheet functions. As you enter a function
into the formula, the Formula Palette displays the name of the function, each of its
arguments, a description of the function and each argument, the current result of the
function, and the current result of the entire formula. To display the Formula Palette,
click = in the formula bar. An alternative way to start the Formula Palette, from the
Insert Menu, choose Function.
You can use the Formula Palette to edit functions in formulas. Select a cell that contains a formula, and then click = to display the Formula Palette. The first function in the formula and each of its arguments are displayed in the palette. You can edit the first function or edit another function in the same formula by clicking in the formula bar anywhere within the function. Worksheet functions are calculation tools that can be used on worksheets to perform decision-making, action-taking, and value-returning operations automatically.
|The functions are listed by category, such as "Financial", "Math & Trig", or "Statistical". When you select a function from the list box, the definition of the function and of its arguments will automatically appear for you, as well as the correct placement of commas and parentheses.|
|Types of Arguments||Arguments are the information that a function
uses to produce a new value or perform an action. Arguments are always located to the
right of the function name and are enclosed in parentheses. Most arguments are expected to
be of a certain data type.
Arguments to a function can be any of the following:
Workbook Functions Listed by Category
|When you need to analyze whether values in a
list meet a specific condition, or criteria, you can use a database worksheet function.
For example, in a list that contains sales information, you can count all the rows or
records in which the sales are greater than 1,000 but less than 2,500.
Some database and list management worksheet functions have names that begin with the letter "D." These functions, also known as Dfunctions, have three arguments database, field, criteria.
|With date and time functions, you can analyze and work with date and time values in formulas. For example, if you need to use the current date in a formula, use the TODAY worksheet function, which returns the current date based on your computer's system clock.|
|The engineering worksheet functions perform
engineering analysis. Most of these functions are of three types:
Note The engineering functions are provided by the Analysis ToolPak. If an engineering worksheet function is not available, run the Setup program to install the Analysis ToolPak. After you install the Analysis ToolPak, you must enable it by using the Add-Ins command on the Tools menu.
|Financial functions perform common business
calculations, such as determining the payment for a loan, the future value or net present
value of an investment, and the values of bonds or coupons.
Common arguments for the financial functions include:
|You can use the logical functions either to
see whether a condition is true or false or to check for multiple conditions.
For example, you can use the IF function to determine whether a condition is true or false: One value is returned if the condition is true, and a different value is returned if the condition is false.
|When you need to find values in lists or
tables or when you need to find the reference of a cell, you can use the lookup and
reference worksheet functions.
For example, to find a value in a table by matching a value in the first column of a table, use the VLOOKUP worksheet function. To determine the position of a value in a list, use the MATCH worksheet function.
|With math and trigonometry functions, you can perform simple and complex mathematical calculations, such as calculating the total value for a range of cells or the total value for a range of cells that meet a condition in another range of cells, or round numbers.|
|Statistical worksheet functions perform
statistical analysis on ranges of data.
For example, a statistical worksheet function can provide statistical information about a straight line plotted through a group of values, such as the slope of the line and the y-intercept, or about the actual points that make up the straight line.
|With text functions, you can manipulate text
strings in formulas.
For example, you can change the case or determine the length of a text string. You can also join, or concatenate, a date to a text string. The following formula is an example of how you can use the TODAY function with the TEXT function to create a message that contains the current date and formats the date in the "dd-mmm-yy" number format.
="Budget report as of "&TEXT(TODAY(),"dd-mmm-yy")
Back to Intro to Excel 97 Table of Contents
Direct questions regarding this site to the University of North Texas Computing Center Helpdesk. We can be reached by e-mail at firstname.lastname@example.org , or by phone at 940.565.2324. Hours: Sunday 1pm-midnight, Monday-Thursday 8am-midnight, Friday 8am-8pm, Saturday 9am-5pm.
UNT Computing Center - UNT Box 305398 - Denton, TX 76203