Chapter 5. Working with Formulas and Functions
A formula is essentially a series of instructions you give Excel to return a value. These instructions can be mathematical operations, or a call to a built-in Excel function. Excel typically displays the result of a formula in a cell as either numeric or textual data.
Functions are abbreviated formulas that perform specific operations on a group of values. Excel provides more than 250 functions that can help you with tasks ranging from determining loan payments to calculating investment returns. For example, the SUM function automatically adds up the numeric values within a given range.
In this chapter, you will walk through the ins and outs of using Excel formulas and functions. By the end of this chapter, you will be well equipped to start building your own formulas!
Functions and Correcting Errors
Using AutoSum (SUM)
Excel can use formulas to perform calculations for you. Because a formula refers to cells rather than to the values those cells contain, Excel updates the sum whenever you change the values in the cells. You’ll probably use the AutoSum formula a lot—it adds numbers in a range of cells.
Click in the cell in which you want the result of the AutoSum operation to appear (this is called the resultant cell).
Click the AutoSum command on the Home tab.
Excel selects the most obvious range of numbers and puts a dotted line around the cells. Press Enter to accept the range or use the mouse to select alternative cells.
Click the resultant cell to make it the active cell. Notice that the formula is displayed in the Formula bar.
Tip: AutoSum Shortcut Key
Excel provides an easy way to implement the AutoSum via a shortcut key. Using your keyboard, enter the combination of Alt and the Plus symbol (+). You’ll see that Excel performs the AutoSum for you. At that point, just press the Enter key on your keyboard to accept the AutoSum calculation.
Finding a Cell Average (AVERAGE)
A function is one of Excel’s many built-in formulas for performing a specialized calculation on the data in your worksheet. For example, instead of totaling your sales data, you can use Excel’s AVERAGE function to determine the average of each quarter per region.
Click the cell in which you want the result of the AVERAGE function to appear (this is called the resultant cell).
Click the down arrow next to the AutoSum command on the Home tab and choose Average from the list that appears.
Excel selects the most obvious range of numbers and puts a dotted line around the cells. Press Enter to accept the range or use the mouse to select alternative cells.
Click the resultant cell to make it the active cell. Notice that the formula is displayed in the Formula bar.
Note: Selecting Specific Cells for your Calculation
If you don’t want to use the range of cells that Excel selects for you, click on the first cell you want, hold down the Ctrl key, and click on each additional cell you would like to include in the calculation. When you finish selecting the cells you want to calculate, press Enter to see the result.
Finding the Largest Cell Amount (MAX)
As its name suggests, the MAX function returns the largest/maximum number in any given range. You would typically use this function if you needed to dynamically reference the largest number in other formulas. You can use Excel’s MAX function to, for example, determine the quarter in which you had the most sales. Although it’s easy enough to see this information with your eyes, the Quarter with the most sales changes as each quarter passes. Instead of manually keeping up with which quarter is the king of sales, the MAX function does that for you.
Click the cell in which you want the result of the MAX function to appear (this is called the resultant cell).
Click the down arrow next to the AutoSum command on the Home tab and choose Max from the list that appears.
Excel selects the most obvious range of numbers and puts a dotted line around the cells. Press Enter to accept the range or use the mouse to select alternative cells.
Click the resultant cell to make it the active cell. Notice that the formula is displayed in the Formula bar.
Note: Finding the Minimum Cell Amount
In addition to enabling you to find the largest cell amount, you can also find the smallest cell amount. See the next task, “Finding the Smallest Cell Amount (MIN),” for more information.
Finding the Smallest Cell Amount (MIN)
As its name suggests, the MIN function returns the largest/maximum number in any given range. This is the direct opposite of the MAX function, which returns the largest number in a range. You would typically use Excel’s MIN function to determine the lowest performing Region, or Market, or whatever else you’re measuring. This quickly gives you a reference point for the lowest number you’re dealing with in your data.
Click the cell in which you want the result of the function to appear (this is called the resultant cell).
Click the down arrow next to the AutoSum command on the Home tab and choose Min from the list that appears.
Excel selects the most obvious range of numbers and puts a dotted line around the cells. Press Enter to accept the range or use the mouse to select alternative cells.
Click the resultant cell to make it the active cell. Notice that the formula is displayed in the Formula bar.
Note: Finding the Maximum Cell Amount
In addition to enabling you to find the smallest cell amount, you can also find the largest cell amount. Refer to the task “Finding the Largest Cell Amount (MAX)” earlier in this chapter for more information.
Counting the Number of Cells (COUNT)
As you may have guessed, the COUNT function literally counts the number of data points in any given range. Suppose you want to know how many values (data points) are in your data table—you could use Excel’s COUNT function to count the number of cells in a selected range.
Click the cell in which you want the result of the COUNT function to appear (this is called the resultant cell).
Click the down arrow next to the AutoSum command on the Home tab and choose Count Numbers from the list that appears.
Excel selects the most obvious range of numbers and puts a dotted line around the cells. Press Enter to accept the range or use the mouse to select alternative cells.
Click the resultant cell to make it the active cell. Notice that the formula is displayed in the Formula bar.
Tip: Counting Text Values
You’ll notice that the COUNT function only works if the range you are counting is numbers based. To count the data points in a range that contains either numbers or text, you can use the COUNTA function. To use COUNTA, edit your COUNT formula to add an A.
Entering a Formula
Another way to use a formula is to type it directly into the cell. You can include any cells in your formula; they do not have to be next to each other. Also, you can combine mathematic operations—for example, C3+C4–D5.
Click the cell in which you want the result of the formula to appear (this is called the resultant cell).
Type = (the equal sign) followed by the references of the cells containing the data you want to total (for example, F3+F4+F5+F6+F7+F8), and then press Enter.
Click the resultant cell to make it the active cell; the values in the specified cells are added together.
Caution: Order of Operation
Excel first performs calculations within parentheses, and then it performs multiplication or division calculations from left to right. Finally, it performs any addition or subtraction from left to right.
Editing a Formula or Function
After you enter a formula or function, you can change the values in the referenced cells, and Excel automatically recalculates the value based on the changes. You can include any cells in a formula or function; they do not have to be next to each other.
Click the cell you want to edit; the function is displayed in the Formula bar.
Click the Insert Function command on the Formula bar to open the Function Arguments dialog box. (If a formula, the Insert Function dialog box will appear.)
Type the changes to your function. For example, change the cells being summed to B3–B8 instead of B3–B9. Then click OK.
The changes are made and the result appears in the cell.
Tip: Pressing F2
Instead of using the Function Arguments dialog box to edit your formulas, you can press the F2 key and edit your formula just like you would for regular text or data on the Formula bar.
Copying a Formula
When you build your worksheet, you might want to use the same data and formulas in more than one cell. With Excel’s Copy command, you can create the initial data or formula once and then place copies in the appropriate cells. For example, suppose you want to find the average sales per quarter in other sales regions. To do so, create the formula for the first region, and copy it to cells for the other regions. Excel automatically figures out how to change the cell ranges to which the formula should apply.
Click the cell that contains the function you want to copy.
Click the Copy command on the Home tab; a line surrounds the cell you are copying.
Click the cell or cells into which you want to paste the formula.
Press Enter (or Ctrl+V) to paste the formula into each of the selected cells.
Tip: Increasing Cell Width
If you paste a copied formula, you might need to alter the size of your columns to accommodate the new size of the data in the cell. To automatically make an entire column (or multiple columns) fit the width of the widest cell in that column (or columns), move the cursor over the right side of the column header and double-click when the cursor changes to a two-headed arrow.
Assigning Names to a Cell or Range
You can create range names that make it easier to create formulas and move to that range. For example, a formula that refers to a range named Qtr1 is easier to understand than one named B4:B9. Not only is it easier to remember a name than the cell addresses, but Excel also displays the range name in the Name box—next to the Formula bar. You can name a single cell or a selected range in the worksheet.
Select the cell or range you want to name.
Click in the Name Box, type the desired range name, and press Enter.
Excel names the range. When the range is selected, the name appears in the Name box.
Tip: Rules for Naming Ranges
Excel has a few basic rules for the naming of ranges. You must start your range names with a letter or an underscore. You can use numbers anywhere else in your range names, but no spaces. You can also use up to 255 characters, but as a best practice, you should always strive to keep your range names short and pithy. Finally, you cannot use a name that conflicts with a built-in range or cell reference. For example, you can’t use A1 as a range name.
Referencing Names in a Function
One of the reasons you create a name for a cell or group of cells is so that you can easily refer to that cell or range in a function. That way, rather than typing or selecting a range or cell, you can type the name or select it from the Paste Name dialog box.
Click in the cell in which you want the result of the formula to appear (this is called the resultant cell).
Type the function in the cell using a named cell or range—for example, =AVERAGE(_1stQtr). As you type the name, you should see a list of named ranges pop up below the cell.
You can click on one to select it.
Press the Enter key.
Note: Quickly Retrieve a Range Name
If you forget the name of a range while you are typing a formula, go to the Formula tab and click the Use in Formula dropdown. This gives you a list of the available ranges. Click the one you are looking for and the range name is automatically placed in the formula.
Note: Deleting a Range Name
To delete a range name, go to the Formulas tab and click on the Name Manager. There, you can click on the target range name and click the Delete command. Click OK to confirm the deletion.
Using Functions Across Worksheets
You can use cell references from other worksheets in your calculations. For example, suppose you have two worksheets that contain the calculations for the total sales by region for a particular year. In a third worksheet, you want to calculate the total sales by region for the last two years. You can reference the cells in the first two worksheets that contain the totals and perform calculations on them in the third worksheet.
Click in the cell in which you want the result of the formula to appear (this is called the resultant cell).
In this example, we are going to add the sum of this operation to last year’s data in another worksheet (2008), so we start our formula referencing the 2009 total, then we enter a + (plus sign).
Click the tab of the worksheet that contains the cell you want to reference in the calculation.
Tip: Using Worksheet Name References
Instead of switching back and forth between worksheets, you can manually type the reference to the worksheet name directly in your formulas. Select the location of the cell in a particular worksheet (column letter and row number) in addition to the sheet name—for example, Sheet1!A1. If your sheet name contains spaces, you will need to wrap your sheet name with a single quote – for example, ‘My Second Sheet’!A1.
Click the cell that you want to use in your calculation; it appears next to the worksheet name in the Formula bar.
Press the Enter key. Excel performs the calculation and returns you to the original worksheet.
Click the resultant cell to make it the active cell. Notice that the function is in the Formula bar.
Caution: Be Aware of Worksheet Name References
Each cell you reference from another worksheet must be prefaced with its worksheet name. For example, if you have the formula =SUM(Sheet1!A1+B1) in cell C3 of Sheet2, this references cell A1 from Sheet1 and B1 from Sheet2. If you need to reference both A1 and B1 from Sheet 1, you must include the worksheet name with both cell references, like so: =SUM(Sheet1!A1+Sheet1!B1).
Using Auto-Calculate
Suppose you want to see a function performed on some of your data—in this example, to determine the lowest quarterly sales goal of any region in 2009—but you don’t want to add the function directly into the worksheet. Excel’s Auto-Calculate feature can help.
Select the cells that you want to Auto-Calculate.
The Status Bar now shows you the Average, Count, and Sum.
Right-click the Status Bar and click on each option you want to enable or disable.
Observe your new Auto Calculation(s).
Note: Turning Off Auto-Calculate
You can turn off the Auto-Calculate feature by right-clicking on the Status Bar and unselecting each option that is selected.
Finding Functions
In the old days, you had to know the name of the function you wanted to use. Now, however, Excel makes it easy to find the function you need—all you have to know is what you want the function to do.
Click the Insert Function (fx) command next to the Formula bar.
Type a description of the function you are looking for in the Search For a Function text box and press Enter (or click the Go command).
Scroll through the list in the Select a Function box and select a function to read a description of it.
When you find the function you are looking for, click OK.
Excel walks you through the process of entering the function’s arguments in the Function Arguments dialog box. Click OK when you are done entering its arguments.
Note: Function Arguments Help
If you need help while you are entering your function arguments, click the Help On This Function link in the bottom-left corner of the Function Arguments dialog box.
Calculating a Loan Payment (PMT)
Using Excel, you can determine a monthly loan payment based on a constant interest rate, a specific number of pay periods, and the current loan amount.
Click in the cell in which you want the result of the function to appear (this is called the resultant cell).
Click the Insert Function (fx) command next to the Formula bar.
The Insert Function dialog box opens. Click the down arrow next to the Or Select a Category field and choose Financial from the list that appears.
A list of financial-related functions appears in the Select a Function list. Scroll through the list to locate the PMT function and double-click it.
Note: Function Arguments Help
If you need help while entering function arguments, click the Help On This Function link in the bottom-left corner of the Function Arguments dialog box.
Note: Negative Payment
Your resulting payment is a negative number because payments are considered a debit.
In the Rate field, type the interest rate per period. For example, type 6%/12 for monthly payments on a 6% annual percentage rate (APR).
In the Nper field, type the total number of loan payments. For example, type 360 if you’ll be making 12 payments per year on a 30-year loan.
In the Pv field, type the present value of the loan—for example, 150,000—and click OK.
Excel calculates the payment and inserts it in the resultant cell.
Note: Type Argument
If you don’t enter a number in the Type argument, it defaults to 0, which means that the last payment will pay off the mortgage loan (this is because mortgages are paid in arrears—at the end of the payment period). If you are calculating a car payment, you might put a 1 in the Type argument because you make your payments at the beginning of the payment period. A 1 versus a 0 makes a slight difference in the calculation because of the interest accrued.
Performing a Logical Test Function (IF)
Using Excel, you can perform a logical test function—for example, to indicate whether scores equal a passing or failing grade based on an established set of criteria.
Click in the cell in which you want the result of the function to appear (this is called the resultant cell).
Click the Insert Function (fx) command next to the Formula bar.
The Insert Function dialog box opens. Click the down arrow next to the Or Select a Category field and choose Logical from the list that appears.
A list of logical functions appears in the Select a Function list. Scroll through the list to locate the IF function and double-click it.
Note: Embedded IFs
You can set up embedded IF statements to use in your logical test. For example, suppose scores between 90–100 are an A, 80–89 are a B, 70–79 are a C, 60–69 are a D, and scores below 59 are an F. Your formula might look like this: =IF(B3>89, “A”, IF(B3>79, “B”, IF(B3>69, “C”, IF(B3>59, “D”, “F”).
In the Logical_test field, type the condition to determine whether a grade is above 70. The logical test is whether the cell is greater than 70; for example, B3>70.
In the Value_if_true field, type the value you want to use if the grade is above 70 (that is, a passing grade)—for example, “Pass”.
In the Value_if_false field, type the value you want to use if the grade is below 70 (that is, a failing grade)—for example, “Fail”.
Click OK.
Excel performs the logical test and inserts the result in the resultant cell.
Tip: Quick Help with Functions
You can press the F1 key on your keyboard any time you’re typing up an Excel function to automatically be brought to that Function’s help screen.
Conditionally Summing a Range (SUMIF)
Using Excel, you can add the data in a range, given certain criteria. This might be useful if, for instance, you need to total the current monthly sales for all sales reps who match a specific criterion—for example, who are all in the same sales region.
Click in the cell in which you want the result of the function to appear (this is called the resultant cell).
Click the Insert Function (fx) command next to the Formula bar.
The Insert Function dialog box opens. Click the down arrow next to the Or Select a Category field and choose Math & Trig from the list that appears.
A list of math and trig functions appears in the Select a Function list. Scroll through the list to locate the SUMIF function and double-click it.
Note: Function Arguments Help
If you need help while you are entering your function arguments, click the Help On This Function link in the bottom-left corner of the Function Arguments dialog box.
In the Range field, type the range of cells whose contents you want to review—for example, C2:C79. (You can also click directly in the worksheet to select the cells.)
In the Criteria field, type the criterion you want to check in the range—in this case, type “1” because you want to add sales data from Region 1 only.
In the Sum_range field, type the range of cells that match your criterion—in this case, F2:F79, which contains the current month sales data.
Click OK.
Excel adds the range given your criteria and inserts the result in the resultant cell.
Note: Range Selection
In addition to typing specific Range and Sum_range arguments, you can click in the worksheet and select each range of cells using the mouse.
Finding the Future Value of an Investment (FV)
If you open a 3% interest-bearing money market account with $100 in January and make deposits of $100 each month, how much money will you have at the end of the year? Excel can help you calculate the future value of an amount of money, based on a constant interest rate, over a specific number of periods in which you make a constant payment.
Click in the cell in which you want the result of the function to appear (this is called the resultant cell).
Click the Insert Function (fx) command next to the Formula bar.
The Insert Function dialog box opens. Click the down arrow next to the Or Select a Category field and choose Financial from the list that appears.
A list of financial-related functions appears in the Select a Function list. Scroll through the list to locate the FV function and double-click it.
Note: Function arguments help
If you need help while you are inputting your function arguments, click the Help on this function link in the bottom-left corner of the Function Arguments dialog box.
In the Rate field, type the interest rate per period. For example, type 3%/12 for monthly accrual on a 3% interest-bearing account.
In the Nper field, type the total number of payments for the investment (in this example, 12 deposits).
In the Pmt field, type the amount to be paid in each deposit—in this case, -100.
Click OK.
Excel calculates the future investment value and inserts it in the resultant cell.
Note: Negative Payments
Because you are making a payment with each deposit, you need to make sure the Pmt argument is a negative value.
Fixing the #### Error
Excel notifies you when there are errors in your data by displaying different error descriptions in the cell that contains the error. For example, when a cell contains the value ####, it means that the column that contains that value is not wide enough to display the actual data. Simply widen the column to see the cell’s contents.
If one of the cells in your worksheet contains the #### error, click on the cell’s column border and drag it to increase the column width.
When you let go of the dragged border, the error disappears and the cell’s actual data is displayed.
Note: Beginning with Larger Columns
It is a good idea to start out with columns that are larger than you need. Then you can decrease their size while you are formatting the worksheet.
Note: AutoFitting Column Widths
To automatically make an entire column (or multiple columns) fit the width of the widest cell in that column (or columns), move the cursor over the right side of the column header and double-click when the cursor changes to a two-headed arrow.
Fixing the #DIV/0! Error
Excel notifies you when there are errors in your data by displaying different error descriptions in the cell that contains the error. For example, when a cell contains the #DIV/0! error, it means that the formula is trying to divide a number by 0, or by an empty cell.
In the cell you want to use as the resultant cell, type a formula to obtain an average. For example, type =SUM(B10/B9) in cell B12 and press Enter.
If one of the cells in your worksheet now contains the #DIV/0! error, locate the empty cell referenced by the formula (in this case, cell B9).
Click on the resultant cell (here, B12), press F2 on the keyboard, and retype the formula to omit the empty cell—in this case, =(B10/B11)—and press Enter.
The error disappears because it is no longer trying to divide a number by an empty cell.
Note: Pressing the Delete Key
In this example, you could also press the Delete key in cell B9 to remove the formula.
Fixing the #NAME? Error
Excel notifies you when there are errors in your data by displaying different error descriptions in the cell that contains the error. For example, when a cell contains a #NAME? error, it means the formula contains an incorrectly spelled cell or function name.
In the cell you want to use as the resultant cell, type the formula you want to use in your calculation. For example, type =CNT(B3:B8) in cell B11 and press Enter.
In this case, you get the #NAME? error because CNT is not the correct spelling for the referenced function (it is COUNT).
Click on the resultant cell (here, B11), press F2 on the keyboard, and retype the formula—in this case, =COUNT(B3:B8)—and press Enter.
The error disappears because the function is spelled correctly.
Note: Using the Paste Function Dialog Box
The Paste Function dialog box offers many functions. Practice using different functions and see the results you get from your calculations. As you experiment, you can move the Paste Function dialog box around or collapse it to see your cells.
Fixing the #VALUE! Error
Excel notifies you when there are errors in your data by displaying different error descriptions in the cell that contains the error. For example, when a cell contains a #VALUE! value, it means the formula contains nonnumeric data or cell or function names that cannot be used in the calculation.
In the cell you want to use as the resultant cell, type the formula you want to use in your calculation. For example, type =SUM(B2+B3+B4+B5+B6+B7+B8) in cell B10, and press Enter.
In this case, you get the #VALUE! error because the value in cell B2 is textual, not numeric. You won’t get the error if you either remove B2 or you replace the formula with =SUM(B2:B5) instead of the plus signs.
Click on the resultant cell (here, B10), press F2 on the keyboard, and retype the formula—in this case, =SUM(B3+B4+B5+B6+B7+B8)—and press Enter.
The error disappears because all cells are now numeric.
Note: Overwriting Cells
See the task “Overwriting and Deleting Data” in Chapter 3, “Entering and Managing Data,” to make sure you are correctly overwriting data in cells.
Recognizing the #REF! Error
Excel notifies you when there are errors in your data by displaying different error descriptions in the cell that contains the error. For example, when a cell contains a #REF! error, it means the formula contains a reference to a cell that isn’t valid. Frequently, this means you deleted a referenced cell. The best solution is to undo your action and review the cells involved in the formula.
In the cell you want to use as the resultant cell, type the formula you want to use in your calculation. For example, type =SUM(B2+C2+D2+E2) and press Enter.
Right-click one of the columns that contains a cell referenced in the formula you just typed (here, column D), and choose Delete from the shortcut menu that appears.
In this case, the #REF! error appears because the values in cells referenced in column D are no longer available in the formula.
Click the Undo command to correct it.
The error disappears because the formula values are restored.
Recognizing Circular References
Excel notifies you when there are errors in your data by displaying different error descriptions in the cell that contains the error. For example, you receive a circular reference error message when one of the cells you are referencing in your calculation is the cell in which you want the calculation to appear.
Type the formula you want to use in your calculation. For example, type =SUM(B2+C2+D2+E2+G2) and press Enter.
Excel displays an error message, notifying you that the formula contains a circular reference. Click OK.
Click on the resultant cell, press F2 on the keyboard, and retype your formula—in this case, =SUM(B2+C2+D2+E2)—and press Enter.
The error is fixed because the result cell no longer is in the calculation.
Note: Circular Reference Dialog
If you didn’t intend to create a circular reference and you chose OK in the message box, the Circular Reference dialog and Help will appear to assist you in correcting your actions.
Checking for Formula References (Precedents)
One way to check a formula to see whether it is referencing the correct cells is to select that formula and then trace all cells that are referenced in that formula. Cells that are referenced are called precedents.
Click on the Formulas tab.
Click the cell you want to trace (this cell must contain a formula) and click the Trace Precedents command.
Excel draws tracer arrows to the appropriate cells.
Click the Trace Precedents command again to see whether there are any precedents for these calculations.
If additional precedents are present, Excel draws additional tracer arrows to the cells.
Note: Removing Formula Auditing Lines References
Click the Remove Precedents Arrows command on the Formula tab to remove the arrows.
Checking for Cell References (Dependents)
When you trace a dependent, you start with a cell that is referenced in a formula and then trace all cells that reference this cell. This is another way to check whether your formulas are correct. (If the cell is not referenced in a formula, you get an error message saying so.)
Click on the Formulas tab.
Click the cell you want to trace. This cell must not contain a formula.
Click the Trace Dependents command until the tracer arrows stop adding on to the appropriate cells.
Click the Remove Dependent Arrows command enough times to remove all the arrows (or click the Remove All Arrows command).