CHAPTER 11
Working with Formulas and Functions
Understanding Formulas
You can use formulas, which you build using mathematical operators, values, and cell references, to perform all kinds of calculations on your Excel data. For example, you can add the contents of a column of monthly sales totals to determine the cumulative sales total. If you are new to writing formulas, this section explains the basics of building your own formulas in Excel. You learn about the correct way to structure formulas in Excel, how to reference cell data in your formulas, which mathematical operators are available for your use, and more.
Formula Structure
Ordinarily, when you write a mathematical formula, you write the values and the operators, followed by an equal sign, such as 2 + 2=. In Excel, formula structure works a bit differently. All Excel formulas begin with an equal sign (=), such as =2 + 2. The equal sign tells Excel to recognize any subsequent characters you enter as a formula rather than as a regular cell entry.
Reference a Cell
Every cell in a worksheet has a unique address, composed of the cell’s column letter and row number, and that address appears in the Name box to the left of the Formula bar. Cell B3, for example, identifies the third cell down in column B. Although you can enter specific values in your Excel formulas, you can make your formulas more versatile if you include — that is, reference — a cell address rather than the value in that cell. Then, if the data in the cell changes but the formula remains the same, Excel automatically updates the result of the formula.
Cell Ranges
A group of related cells in a worksheet is called a range. You specify a range using the cells in the upper-left and lower-right corners of the range, separated by a colon. For example, range A1:B3 includes cells A1, A2, A3, B1, B2, and B3. You can also assign names to ranges to make it easier to identify their contents. Range names must start with a letter, underscore, or backslash, and can include uppercase and lowercase letters. Spaces are not allowed.
Mathematical Operators
You use mathematical operators in Excel to build formulas. Basic operators include the following:
Operator | Operation |
+ | Addition |
- | Subtraction |
* | Multiplication |
/ | Division |
% | Percentage |
^ | Exponentiation |
= | Equal to |
< | Less than |
<= | Less than or equal to |
> | Greater than |
>= | Greater than or equal to |
<> | Not equal to |
Operator Precedence
Excel performs operations in a formula from left to right, but gives some operators precedence over others, following the rules you learned in high school math:
Order | Operation |
First | All operations enclosed in parentheses |
Second | Exponential operations |
Third | Multiplication and division |
Fourth | Addition and subtraction |
When you are creating equations, the order of operations determines the results. For example, suppose you want to determine the average of values in cells A2, B2, and C2. If you enter the equation =A2 + B2 + C2/3, Excel first divides the value in cell C2 by 3 and then adds that result to A2 + B2 — producing the wrong answer. The correct way to write the formula is =(A2 + B2 + C2)/3. By enclosing the values in parentheses, you are telling Excel to perform the addition operations in the parentheses before dividing the sum by 3.
Reference Operators
You can use Excel’s reference operators to control how a formula groups cells and ranges to perform calculations. For example, if your formula needs to include the cell range D2:D10 and cell E10, you can instruct Excel to evaluate all the data contained in these cells using a reference operator. Your formula might look like this: =SUM(D2:D10,E10).
Operator | Example | Operation |
: | =SUM(D3:E12) | Range operator. Evaluates the reference as a single reference, including all the cells in the range from both corners of the reference. |
, | =SUM(D3:E12,F3) | Union operator. Evaluates the two references as a single reference. |
[space] | =SUM(D3:D20 D10:E15) | Intersect operator. Evaluates the cells common to both references. |
[space] | =SUM(Totals Sales) | Intersect operator. Evaluates the intersecting cell or cells of the column labeled Totals and the row labeled Sales. |
Create a Formula
You can write a formula to perform a calculation on data in your worksheet. In Excel, all formulas begin with an equal sign (=) and contain the values or references to the cells that contain the relevant values. For example, the formula for multiplying the contents of cells D2 and E2 together is =D2*E2. Formulas appear in the Formula bar; formula results appear in the cell to which you assign a formula.
Note that, in addition to referring to cells in the current worksheet, you can build formulas that refer to cells in other worksheets.
Create a Formula
Click the cell where you want to place a formula.
Type =.
Excel displays the formula in the Formula bar and in the active cell.
Click the first cell that you want to include in the formula.
Excel inserts the cell reference into the formula.
Type an operator for the formula.
Click the next cell that you want to include in the formula.
Excel inserts the cell reference into the formula.
Repeat steps 4 and 5 until all the necessary cells and operators have been added.
Press .
You can also click the Enter button () on the Formula bar to accept the formula.
You can click the Cancel button () to cancel the formula.
The result of the formula appears in the cell.
The formula appears in the Formula bar; you can view it by clicking the cell containing the formula.
Note: If you change a value in a cell referenced in your formula, Excel automatically updates the formula result to reflect the change.
Apply Absolute and Relative Cell References
By default, Excel uses relative cell referencing. If you copy a formula containing a relative cell reference to a new location, Excel adjusts the cell addresses in that formula to refer to the cells at the formula’s new location. In cell B8, if you enter the formula =B5 + B6 and then copy that formula to cell C8, Excel adjusts the formula to =C5 + C6.
When a formula must always refer to the value in a particular cell, use an absolute cell reference. Absolute references are preceded with dollar signs. If your formula must always refer to the value in cell D2, enter $D$2 in the formula.
Apply Absolute and Relative Cell References
Copy Relative References
Click the cell containing the formula you want to copy.
A In the Formula bar, the formula appears with a relative cell reference.
Click Home.
Click Copy ().
Select the cells where you want the formula to appear.
Click Paste ().
Excel copies the formula to the selected cells.
The adjusted formula appears in the Formula bar and in the selected cells.
Note: You can press to stop copying.
Copy Absolute References
Enter the formula, including dollar signs ($) for absolute addresses as needed.
Click the cell containing the formula you want to copy.
In the Formula bar, the formula appears with an absolute cell reference.
Click Home.
Click Copy ().
Select the cells where you want the formula to appear.
Click Paste ().
Excel copies the formula to the selected cells.
The formula in the selected cells adjusts only relative cell references; absolute cell references remain unchanged.
Note: To stop copying, press .
Understanding Functions
If you are looking for a speedier way to enter formulas, you can use any one of a wide variety of functions. Functions are ready-made formulas that perform a series of operations on a specified set of values. Excel offers more than 400 functions, grouped into 13 categories, that you can use to perform various types of calculations.
Functions use arguments to identify the cells that contain the data you want to use in your calculations. Function arguments can refer to individual cells or to ranges of cells. This section explains the basics of working with functions.
Using Functions
Functions are distinct in that each one has a unique name. For example, the function that adds values is called SUM, and the function for averaging values is called AVERAGE. You use functions as part of your worksheet formulas. You can insert a function by typing the function name and arguments directly into your formula; alternatively, you can use the Insert Function dialog box to select and apply functions to your data.
Construct an Argument
Functions use arguments to indicate which values you want to calculate. Arguments can be numbers, cell or range references, range names, or even other functions. Arguments are enclosed in parentheses after the function name. For functions that require multiple arguments, you use a comma to separate the values, as in =AVERAGE(A5,C5,F5). If your range has a name, you can insert the name, as in =AVERAGE(Sales).
Types of Functions
Excel groups functions into 13 categories, not including functions installed with Excel add-in programs:
Category | Description |
Financial | Includes functions for calculating loans, principal, interest, yield, and depreciation. |
Date & Time | Includes functions for calculating dates, times, and minutes. |
Math & Trig | Includes a wide variety of functions for calculations of all types. |
Statistical | Includes functions for calculating averages, probabilities, rankings, trends, and more. |
Lookup & Reference | Includes functions that enable you to locate references or specific values in your worksheets. |
Database | Includes functions for counting, adding, and filtering database items. |
Text | Includes text-based functions to search and replace data and other text tasks. |
Logical | Includes functions for logical conjectures, such as if-then statements. |
Information | Includes functions for testing your data. |
Engineering | Offers many kinds of functions for engineering calculations. |
Cube | Enables Excel to fetch data from SQL Server Analysis Services, such as members, sets, aggregated values, properties, and key performance indicators (KPIs). |
Compatibility | Use these functions to keep your workbook compatible with earlier versions of Excel. |
Web | Use these functions when you work with web pages, services, or XML content. |
Common Functions
The following table lists some of the more popular Excel functions that you might use with your own spreadsheet work.
Function | Category | Description | Syntax |
SUM | Math & Trig | Adds values | =SUM(number1, number2, …) |
ROUND | Math & Trig | Rounds a number to a specified number of digits | =ROUND(number, number_digits) |
ROUNDDOWN | Math & Trig | Rounds a number down | =ROUNDDOWN(number, number_digits) |
INT | Math & Trig | Rounds down to the nearest integer | =INT(number) |
COUNT | Statistical | Counts the number of cells in a range that contain data | =COUNT(value1, value2, …) |
AVERAGE | Statistical | Averages a series of arguments | =AVERAGE(number1, number2, …) |
MIN | Statistical | Returns the smallest value in a series | =MIN(number1, number2, …) |
MAX | Statistical | Returns the largest value in a series | =MAX(number1, number2, …) |
MEDIAN | Statistical | Returns the middle value in a series | =MEDIAN(number1, number2, …) |
PMT | Financial | Finds the periodic payment for a fixed loan | =PMT(interest_rate, number_of_periods, present_value, future_value, type) |
RATE | Financial | Returns an interest rate | =RATE(number_of_periods, payment, present_value, future_value, type, guess) |
TODAY | Date & Time | Returns the current date | =TODAY() |
IF | Logical | Returns one of two results that you specify based on whether the value is true or false | =IF(logical_text, value_if_true, value_if_false) |
AND | Logical | Returns true if all the arguments are true, and false if any argument is false | =AND(logical1, logical2, …) |
OR | Logical | Returns true if any argument is true, and false if all arguments are false | =OR(logical1, logical2, …) |
Insert a Function
Although you can insert functions directly into your formulas, it is often easier and more accurate to use the Insert Function feature. This feature enables you to look for a particular function from among Excel’s 400-plus available functions and to guide you through successfully entering the function. After you select your function, the Function Arguments dialog box opens to help you build the formula by describing the arguments you need for the function you chose. Functions use arguments to identify the cells that contain the data you want to use in your calculation.
Insert a Function
Enter your formula up to the point where you want to insert the function.
Note: If you are using the function by itself, then you only need to enter the equal sign (=).
Click Formulas.
Click Insert Function.
Alternatively, click Insert Function () on the Formula bar.
Excel displays the Insert Function dialog box.
Type a description of the function you need here.
Click Go.
A list of suggested functions appears.
Click the function that you want to insert.
A description of the selected function appears here.
Click OK.
The Function Arguments dialog box appears.
In the worksheet, select the cell or range for the first argument required by the function.
Excel adds the cell or range address as the argument to the function.
Repeat step 8 as needed.
When you have specified all the required arguments, Excel displays the function result here.
When you finish specifying the arguments, click OK.
Excel displays the function results in the cell.
The function appears in the Formula bar.
Total Cells with AutoSum
One of the most popular Excel functions is AutoSum. AutoSum automatically totals the contents of selected cells. For example, you can quickly total a column of sales figures. One way to use AutoSum is to select a cell and let the function guess which surrounding cells you want to total. Alternatively, you can specify exactly which cells to sum.
In addition to using AutoSum to total cells, you can select a series of cells in your worksheet; Excel displays the total of the cells’ contents in the status bar, along with the number of cells you selected and an average of their values.
Total Cells with AutoSum
Using AutoSum to Total Cells
Click the cell in which you want to store a total.
Click Formulas.
Click AutoSum.
If you click the AutoSum , you can select other common functions, such as Average or Max.
You can also click the AutoSum button () on the Home tab.
AutoSum generates a formula to total the adjacent cells.
Press or click the Enter button ().
Excel displays the result in the cell.
You can click the cell to see the function in the Formula bar.
Total Cells Without Applying a Function
Select a range of cells whose values you want to total.
Note: To sum noncontiguous cells, click the first cell; then press and hold while clicking the other cells.
Excel sums the contents of the cells, displaying the total in the status bar.
Excel also counts the number of cells you have selected.
Excel also displays an average of the values in the selected cells.