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.

=(B4+25)/SUM(D5:F5)

= denotes a formula

B4 is a cell reference

+ is the Additional Operator

  1. Is a Numeric Constant

/ 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 calculation.

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.

=5-1

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.

=B15*5

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):

=SUM(F6,D7,–E7)

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

"first_name last_name":

=D5&" "&E5

To display the full name in the format

"last_name, first_name":

=E5&", "&D5

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.

=F5*(1+5%)

In this example, assume that cell F5 contains the original value.

If the percentage amount is stored in a cell (for example, cell F2):

=F5*(1+$F$2)

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.

=SUMIF(B5:B25,"Northwind",F5:F25)

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.

=SUM(IF(B5:B25="Northwind",

IF(C5:C25="Western",F5:F25)))

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.

=COUNTIF(B5:B25,"Northwind")

 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 

=SUM(IF(B5:B25="Northwind",

IF(C5:C25="Western",1,0)))

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.

Arithmetic

operator Meaning Example

+ (plus sign) Addition 3+3

– (minus sign) Subtraction 3–1

Negation –1

* (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.

Comparison

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

"Northwind"

Reference Operators Reference operators combine ranges of cells for calculations.

Reference

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 syntax.

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.

  =SUM(A10,B5:B10,50,37)

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:

  • Numbers - examples are 5.003, 0, 150,266, and -30.05. Numbers without decimals are called integers. Numbers are accurate to 15 digits.
  • Text - Text values used in formulas must be enclosed in double quotation marks. Text values can be a maximum of 255 characters long including quotation marks. A text constant that contains no characters is written as "" and is called "empty text."
  • Logical values - The logical values are TRUE and FALSE. Logical arguments can also be statements that evaluate to TRUE or FALSE.
  • Error values - Examples of error values are #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, and #VALUE!.
  • References - References can refer to single cells, ranges, or multiple selections, and can be relative, absolute, or mixed. Examples are $A$10, A10, A$10, R1C2, or R[10]C[-10].
  • Arrays - Arrays allow you to customize how arguments and functions are entered in cells.

Workbook Functions Listed by Category

 

Database Functions

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.

  • The database argument is the range that contains your list. You must include the row that contains the column labels in the range.
  • The field argument is the label for the column you want to summarize.
  • The criteria argument is the range that contains a condition you specify.

 

Date and Time Functions

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.

 

Engineering Functions

The engineering worksheet functions perform engineering analysis. Most of these functions are of three types:
  • Functions for working with complex numbers
  • Functions for converting values between different numbering systems, such as decimal, hexadecimal, octal, and binary systems
  • Functions for converting values between different systems of measurement

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

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:

  • Future value (fv) – the value of the investment or loan after all payments have been made.
  • Number of periods (nper) – the total number of payments or periods of an investment.
  • Payment (pmt) – the amount paid periodically to an investment or loan.
  • Present value (pv) – the value of an investment or loan at the beginning of the investment period. For example, the present value of a loan is the principal amount that is borrowed.
  • Rate (rate) – the interest rate or discount rate for a loan or investment.
  • Type (type) – the interval at which payments are made during the payment period, such as at the beginning of a month or the end of the month.

Information Functions

Logical Functions

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.

 

Lookup and Reference Functions

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.

 

Math and Trigonometry Functions

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 Functions

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.

 

Text Functions

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 helpdesk@unt.edu , 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