Excel 2016 All-in-One For Dummies (2016)

Book III

Formulas and Functions

Find out about date formats in the article “Setting Regional Dates in Excel 2016” online at www.dummies.com/extras/excel2016aio.

Contents at a Glance

1. Chapter 1: Building Basic Formulas

1. Formulas 101

2. Copying Formulas

3. Adding Array Formulas

4. Range Names in Formulas

5. Adding Linking Formulas

6. Controlling Formula Recalculation

7. Circular References

2. Chapter 2: Logical Functions and Error Trapping

1. Understanding Error Values

2. Using Logical Functions

3. Error-Trapping Formulas

4. Whiting-Out Errors with Conditional Formatting

5. Formula Auditing

6. Removing Errors from the Printout

3. Chapter 3: Date and Time Formulas

1. Understanding Dates and Times

2. Using Date Functions

3. Using Time Functions

4. Chapter 4: Financial Formulas

1. Financial Functions 101

2. The PV, NPV, and FV Functions

3. The PMT Function

4. Depreciation Functions

5. Analysis ToolPak Financial Functions

5. Chapter 5: Math and Statistical Formulas

1. Math & Trig Functions

2. Statistical Functions

6. Chapter 6: Lookup, Information, and Text Formulas

1. Lookup and Reference

2. Information, Please …

3. Much Ado about Text

Chapter 1

Building Basic Formulas

In This Chapter

Summing data ranges with AutoSum

Creating simple formulas with operators

Understanding the operators and their priority in the formula

Using the Insert Function button on the Formula bar

Copying formulas and changing the type of cell references

Building array formulas

Using range names in formulas

Creating linking formulas that bring values forward

Controlling formula recalculation

Dealing with circular references in formulas

Formulas, to put it mildly, are the very “bread and butter” of the worksheet. Without formulas, the electronic spreadsheet would be little better than its green-sheet paper equivalent. Fortunately, Excel gives you the ability to do all your calculations right within the cells of the worksheet without any need for a separate calculator.

The formulas that you build in a spreadsheet can run the gamut from very simple to extremely complex. Formulas can rely totally upon the use of simple operators or the use of built-in functions, both of which describe the type of operation or calculation to perform and the order in which to perform it. Or they can blend the use of operators and functions together. When you use Excel functions in your formulas, you need to learn the particular type of information that a particular function uses in performing its calculations. The information that you supply a function and that it uses in its computation is referred to as the argument(s) of the function.

Formulas 101

From the simple addition formula to the most complex ANOVA statistical variation, all formulas in Excel have one thing in common: They all begin with the equal sign (=). This doesn’t mean that you always have to type in the equal sign — although if you do, Excel expects that a formula of some type is to follow. When building a formula that uses a built-in function, oftentimes you use the Insert Function button on the Formula bar to select and insert the function, in which case, Excel adds the opening equal sign for you.

If you’re an old Lotus 1-2-3 user and you type @@ to start a function, Excel automatically converts the @@ sign into the equal sign the moment that you complete the formula entry. It does mean, however, that each and every completed formula that appears on the Formula bar starts with the equal sign.

When building your formulas, you can use constants that actually contain the number that you want used in the calculation (such as “4.5%,” “$25.00,” or “-78.35”), or you can use cell addresses between the operators or as the arguments of functions. When you create a formula that uses cell addresses, Excel then uses the values that you’ve input in those cells in calculating the formula. Unlike when using constants in formulas, when you use cell addresses, Excel automatically updates the results calculated by a formula whenever you edit the values in the cells to which it refers.

Formula building methods

When building formulas manually, you can either type in the cell addresses or you can point to them in the worksheet. Using the Pointing method to supply the cell addresses for formulas is often easier and is always a much more foolproof method of formula building; when you type in a cell address, you are less apt to notice that you’ve just designated the wrong cell than when pointing directly to it. For this reason, stick to pointing when building original formulas and restrict typing cell addresses to the odd occasion when you need to edit a cell address in a formula and pointing to it is either not practical or just too much trouble.

When you use the Pointing method to build a simple formula that defines a sequence of operations, you stop and click the cell or drag through the cell range after typing each operator in the formula. When using the method to build a formula that uses a built-in function, you click the cell or drag through the cell range that you want used when defining the function’s arguments in the Function Arguments dialog box.

As with the other types of cell entries, you must take some action to complete a formula and enter it into the current cell (such as clicking the Enter button on the Formula bar, pressing the Enter key, or pressing an arrow key). Unlike when entering numeric or text entries, however, you will want to stay clear of clicking another cell to complete the data entry. This is because, when you click a cell when building or editing a formula on the Formula bar, more often than not, you end up not only selecting the new cell, but also adding its address to the otherwise complete formula.

Formulas and formatting

When defining a formula that uses operators or functions, Excel picks up the number formatting of the cells that are referenced in the formula. For example, if you add cell A2 to B3, as in =A2+B3, and cell B3 is formatted with the Currency Style format, the result will inherit this format and be displayed in its cell using the Currency Style.

As soon as you complete a formula entry, Excel calculates the result, which is then displayed inside the cell within the worksheet. (The contents of the formula, however, continue to be visible on the Formula bar anytime the cell is active.) If you make an error in the formula that prevents Excel from being able to calculate the formula at all, Excel displays an Alert dialog box suggesting how to fix the problem. If, however, you make an error that prevents Excel from being able to display a proper result when it calculates the formula, the program displays an Error value rather than the expected computed value. (See Book III, Chapter 2 for details on dealing with both of these types of errors in formulas.)

Editing formulas

As with numeric and text entries, you can edit the contents of formulas either in their cells or on the Formula bar. To edit a formula in its cell, double-click the cell or press F2 to position the insertion pointer in that cell. (Double-clicking the cell positions the insertion pointer in the middle of the formula, whereas pressing F2 positions it at the end of the formula — you can also double-click at the beginning or end of the cell to position the insertion pointer there.) To edit a formula on the Formula bar, use the I-beam mouse to position the insertion point at the place in the formula that needs editing first.

As soon as you put the Excel program into Edit mode, Excel displays each of the cell references in the formula within the cell in a different color and uses this color to outline the cell or cell range in the worksheet itself. This coloration enables you to quickly identify the cells and their values that are referred to in your formula and, if necessary, modify them as well. You can use any of the four sizing handles that appear around the cell or cell range to modify the cell selection in the worksheet and consequently update the cell references in the formula.

Using Excel like a handheld calculator

Sometimes, you may need to actually calculate the number that you need to input in a cell as a constant. Instead of reaching for your pocket calculator to compute the needed value and then manually entering it into a cell of your spreadsheet, you can set up a formula in the cell that returns the number that you need to input and then convert the formula into a constant value. You convert the formula into a constant by pressing F2 to edit the cell, immediately pressing F9 to recalculate the formula and display the result on the Formula bar, and then selecting the Enter button on the Formula bar or pressing the Enter key to input the calculated result into the cell (as though you had manually input the result in the cell).

When you AutoSum numbers in a spreadsheet

The easiest and often the most used formula that you will create is the one that totals rows and columns of numbers in your spreadsheet. Usually, to total a row or column of numbers, you can click the Sum command button (the one with the Σ on it) in the Editing group of the Home tab of the Ribbon. When you click this button, Excel inserts the built-in SUM function into the active cell and simultaneously selects what the program thinks is the most likely range of numbers that you want summed.

Instead of taking the time to click the Sum button on the Home tab, it’s often faster and easier to simply press Alt+= (equal sign) to insert the SUM function in the current cell and have Excel select the range of cells most likely to be totaled.

Figure 1-1 demonstrates how this works. For this figure, I positioned the cell cursor in cell B7, which is the first cell where I need to build a formula that totals the various parts produced in April. I then clicked the Sum button on the Home tab of the Ribbon.

Figure 1-1: Using the AutoSum feature to create a SUM formula that totals a column of numbers.

As Figure 1-1 shows, Excel then inserted an equal sign followed by the SUM function and correctly suggested the cell range B3:B6 as the argument to this function (that is, the range to be summed). Because Excel correctly selected the range to be summed (leaving out the date value in cell B2), all I have to do is click the Enter button on the Formula bar to have the April total calculated.

Figure 1-2 shows another example of AutoSum to instantly build a SUM formula, this time to total the monthly production numbers for Part 100 in cell K3. Again, all I did to create the formula shown in Figure 1-2 was to select cell K3 and then click the Sum button on the Home tab. Again, Excel correctly selected B3:J3 as the range to be summed (rightly ignoring cell A3 with the row title) and input this range as the argument of the SUM function. All that remains to be done is to click the Enter button on the Formula bar to compute the monthly totals for Part 100.

Figure 1-2: Using the AutoSum feature to create a SUM formula that totals a row of numbers.

If for some reason AutoSum doesn’t select the entire or correct range that you want summed, you can adjust the range by dragging the cell cursor through the cell range or by clicking the marquee around the cell range, which turns the marching ants into a solid colored outline. Then position the mouse pointer on one of the sizing handles at the four corners. When it turns into a thick white arrowhead pointing to the center of a pair of black double-crossed arrows, drag the outline until it includes all the cells you want included in the total.

When AutoSum doesn’t sum

Although the Sum button’s primary function is to build formulas with the SUM function that totals ranges of numbers, that’s not its only function (pun intended). Indeed, you can have the AutoSum feature build formulas that compute the average value, count the number of values, or return the highest or lowest value in a range — all you have to do is click the drop-down button that’s attached to the Sum command button on the Home tab and then choose Average, Count Numbers, Max, or Min from its drop-down menu.

Also, don’t forget about the Average, Count, and Sum indicator on the status bar. This indicator automatically shows you the average value, the count of the numbers, and the total of all numbers in the current cell selection. You can use this feature to preview the total that’s to be returned by the SUM formula that you create with the AutoSum button by selecting the cell range that contains the numbers to be summed.

Keep in mind that all Excel functions enclose their argument(s) in a closed pair of parentheses, as shown in the examples with the SUM function. Even those rare functions that don’t require any arguments at all still require the use of a closed pair of parentheses (even when you don’t put anything inside of them).

Totals and sums with the Quick Analysis tool

Instead of resorting to the Sum button and AutoFill to create totals for a worksheet table, you can use the Totals feature on the Quick Analysis tool to get the job done. The Quick Analysis tool offers a bevy of features for doing anything from adding conditional formatting, charts, pivot tables, and sparklines to your worksheet tables. And it turns out Quick Analysis is also a whiz at adding running totals and sums to the rows and columns of your new worksheet tables.

To use the Quick Analysis tool, all you have to do is select the worksheet table’s cells and then click the Quick Analysis tool that automatically appears in the lower-right corner of the last selected cell. When you do this, a palette of options (from Formatting to Sparklines) appears right beneath the Quick Analysis tool. (See Figure 1-3.)

Figure 1-3: Adding a row of totals with the Quick Analysis tool’s Sum button.

To add totals to your selected table data, you first click the Totals tab. You can then use your mouse or Touch pointer to have Live Preview show you totals in a new row at the bottom by highlighting the Sum or Running Total button (with a spreadsheet containing a Sigma) or in a new column on the right by highlighting the second Sum button (with the spreadsheet with the last column highlighted). To actually add the SUM formulas with the totals to a new row or column, you simply click the Sum or Running Total button of choice.

If you have trouble selecting the Quick Analysis tool to open its palette for any reason, simply right-click the cell selection and choose the Quick Analysis item on its shortcut menu.

Building formulas with operators

Many of the simpler formulas that you build require the sole use of Excel’s operators, which are the symbols that indicate the type of computation that is to take place between the cells and/or constants interspersed between them. Excel uses four different types of operators: arithmetic, comparison, text, and reference. Table 1-1 shows all these operators arranged by type and accompanied by an example.

Table 1-1 The Different Types of Operators in Excel

“Smooth operator”

Most of the time, you’ll rely on the arithmetic operators when building formulas in your spreadsheets that don’t require functions because these operators actually perform computations between the numbers in the various cell references and produce new mathematical results.

The comparison operators, on the other hand, produce only the logical value TRUE or the logical value FALSE, depending on whether the comparison is accurate. For example, say that you enter the following formula in cell A10:

=B10<>C10

If B10 contains the number 15 and C10 contains the number 20, the formula in A10 returns the logical value TRUE. If, however, both cell B10 and C10 contain the value 12, the formula returns the logical value FALSE.

The single text operator (the so-called ampersand) is used in formulas to join together two or more text entries (an operation with the highfalutin’ name concatenation). For example, suppose that you enter the following formula in cell C2:

=A2&B2

If cell A2 contains John and cell B2 contains Smith, the formula returns the new (squashed together) text entry, JohnSmith. To have the formula insert a space between the first and last names, you have to include the space as part of the concatenation as follows:

=A2&" "&B2

You most often use the comparison operators with the IF function when building more complex formulas that perform one type of operation when the IF condition is TRUE and another when it is FALSE. You use the concatenating operator (&) when you need to join text entries that come to you entered in separate cells but that need to be entered in single cells (like the first and last names in separate columns). See Book III, Chapter 2 for more on logical formulas, and Book III, Chapter 6 for more on text formulas.

Order of operator precedence

When you build a formula that combines different operators, Excel follows the set order of operator precedence, as shown in Table 1-2. When you use operators that share the same level of precedence, Excel evaluates each element in the equation by using a strictly left-to-right order.

Table 1-2 Natural Order of Operator Precedence in Formulas

Suppose that you enter the following formula in cell A4:

=B4+C4/D4

Because division (like multiplication) has a higher level of precedence than addition (4 versus 5), Excel evaluates the division between cells C4 and D4 and then adds that result to the value in cell B4. If, for example, cell B4 contains 2, C4 contains 9, and D4 contains 3, Excel would essentially be evaluating this equation in cell A4:

=2+9/3

In this example, the calculated result displayed in cell A4 is 5 because the program first performs the division (9/3) that returns the result 3 and then adds it to the 2 to get the final result of 5.

If you had wanted Excel to evaluate this formula in a strictly left-to-right manner, you could get it to do so by enclosing the leftmost operation (the addition between B4 and C4) in a closed pair of parentheses. Parentheses alter the natural order of precedence so that any operation enclosed within a pair is performed before the other operations in the formula, regardless of level in the order. (After that, the natural order is once again used.)

To have Excel perform the addition between the first two terms (B4 and C4) and then divide the result by the third term (cell D4), you modify the original formula by enclosing the addition operation in parentheses as follows:

=(B4+C4)/D4

Assuming that cells B4, C4, and D4 still contain the same numbers (2, 9, and 3, respectively), the formula now calculates the result as 3.666667 and returns it to cell A4 (2+9=11 and 11/3=3.66667).

If necessary, you can nest parentheses in your formulas by putting one set of parentheses within another (within another, within another, and so on). When you nest parentheses, Excel performs the calculation in the innermost pair of parentheses first before anything else and then starts performing the operations in the outer parentheses.

Consider the following sample formula:

=B5+(C5-D5)/E5

In this formula, the parentheses around the subtraction (C5-D5) ensure that it is the first operation performed. After that, however, the natural order of precedence takes over. So the result of the subtraction is then divided by the value in E5, and that result is then added to the value in B5. If you want the addition to be performed before the division, you need to nest the first set of parentheses within another set as follows:

=(B5+(C5-D5))/E5

In this revised formula, Excel performs the subtraction between the values in C5 and D5, adds the result to the value in cell B5, and then divides that result by the value in cell E5.

Of course, the biggest problem with parentheses is that you have to remember to enter them in pairs. If you forget to balance each set of nested parentheses by having a right parenthesis for every left parenthesis, Excel displays an Alert dialog box, informing you that it has located an error in the formula. It will also suggest a correction that would balance the parentheses used in the formula. Although the suggested correction corrects the imbalance in the formula, it unfortunately doesn’t give you the calculation order that you wanted — and if accepted, the suggested correction would give you what you consider an incorrect result. For this reason, be very careful before you click the Yes button in this kind of Alert dialog box. Do so only when you’re certain that the corrected parentheses give you the calculation order that you want. Otherwise, click No and balance the parentheses in the formula by adding the missing parenthesis or parentheses yourself.

Using the Insert Function button

Excel supports a wide variety of built-in functions that you can use when building formulas. Of course, the most popular built-in function is by far the SUM function, which is automatically inserted when you click the Sum command button on the Home tab of the Ribbon. (Keep in mind that you can also use this drop-down button attached to the Sum button to insert the AVERAGE, COUNT, MAX, and MIN functions — see the “When you AutoSum numbers in a spreadsheet” section previously in this chapter for details.) To use other Excel functions, you can use the Insert Function button on the Formula bar (the one with the fx).

When you click the Insert Function button, Excel displays the Insert Function dialog box, similar to the one shown in Figure 1-4. You can then use its options to find and select the function that you want to use and to define the argument or arguments that the function requires in order to perform its calculation.

Figure 1-4: Selecting a function to use in the Insert Function dialog box.

To select the function that you want to use, you can use any of the following methods:

· Click the function name if it’s one that you’ve used lately and is therefore already listed in the Select a Function list box.

· Select the name of the category of the function that you want to use from the Or Select a Category drop-down list box (Most Recently Used is the default category) and then select the function that you want to use in that category from the Select a Function list box.

· Replace the text “Type a brief description of what you want to do and then click Go” in the Search for a Function text box with keywords or a phrase about the type of calculation that you want to do (such as “return on investment”). Click the Go button or press Enter and click the function that you want to use in the Recommended category displayed in the Select a Function list box.

When selecting the function to use in the Select a Function list box, click the function name to have Excel give you a short description of what the function does, displayed underneath the name of the function with its argument(s) shown in parentheses (referred to as the function’s syntax). To get help on using the function, click the Help on This Function link displayed in the lower-left corner of the Insert Function dialog box to open the Help window in its own pane on the right. When you finish reading and/or printing this help topic, click the Close button to close the Help window and return to the Insert Function dialog box.

You can select the most commonly used types of Excel functions and enter them simply by choosing their names from the drop-down menus attached to their command buttons in the Function Library group of the Formulas tab of the Ribbon. These command buttons include Financial, Logical, Text, Date & Time, Lookup & Reference, and Math & Trig. In addition, you can select functions in the Statistical, Engineering, Cube, Information, Compatibility, and Web categories from continuation menus that appear when you click the More Functions command button on the Formulas tab. And if you find you need to insert a function in the worksheet that you recently entered into the worksheet, chances are good that when you click the Recently Used command button, that function will be listed on its drop-down menu for you to select.

When you click OK after selecting the function that you want to use in the current cell, Excel inserts the function name followed by a closed set of parentheses on the Formula bar. At the same time, the program closes the Insert Function dialog box and then opens the Function Arguments dialog box, similar to the one shown in Figure 1-5. You then use the argument text box or boxes displayed in the Function Arguments dialog box to specify what numbers and other information are to be used when the function calculates its result.

Figure 1-5: Selecting the arguments for a function in the Function Arguments dialog box.

All functions — even those that don’t take any arguments, such as the TODAY function — follow the function name by a closed set of parentheses, as in =TODAY( ). If the function requires arguments (and almost all require at least one), these arguments must appear within the parentheses following the function name. When a function requires multiple arguments, such as the DATE function, the various arguments are entered in the required order (as in year, month, day for the DATE function) within the parentheses separated by commas, as in DATE(33,7,23).

When you use the text boxes in the Function Arguments dialog box to input the arguments for a function, you can select the cell or cell range in the worksheet that contains the entries that you want used. Click the text box for the argument that you want to define and then either start dragging the cell cursor through the cells or, if the Function Arguments dialog box is obscuring the first cell in the range that you want to select, click the Collapse Dialog Box button located to the immediate right of the text box. Dragging or clicking this button reduces the Function Arguments dialog box to just the currently selected argument text box, thus enabling you to drag through the rest of the cells in the range.

If you started dragging without first clicking the Collapse Dialog Box button, Excel automatically expands the Function Arguments dialog box as soon as you release the mouse button. If you clicked the Collapse Dialog Box button, you have to click the Expand Dialog Box button (which replaces the Collapse Dialog Box button located to the right of the argument text box) in order to restore the Function Arguments dialog box to its original size.

As you define arguments for a function in the Function Arguments dialog box, Excel shows you the calculated result following the heading, “Formula result =” near the bottom of the Function Arguments dialog box. When you finish entering the required argument(s) for your function (and any optional arguments that may pertain to your particular calculation), click OK to have Excel close the Function Arguments dialog box and replace the formula in the current cell display with the calculated result.

You can also type the name of the function instead of selecting it from the Insert Function dialog box. When you begin typing a function name after typing an equal sign (=), Excel’s AutoComplete feature kicks in by displaying a drop-down menu with the names of all the functions that begin with the character(s) you type. You can then enter the name of the function you want to use by double-clicking its name on this drop-down menu. Excel then enters the function name along with the open parenthesis as in =DATE( so that you can then begin selecting the cell range(s) for the first argument.

For details on how to use different types of built-in functions for your spreadsheets, refer to the following chapters in Book III that discuss the use of various categories: Refer to Chapter 2 for information on Logical functions; Chapter 3 for Date and Time functions; Chapter 4for Financial functions; Chapter 5 for Math and Statistical functions; and Chapter 6 for Lookup, Information, and Text functions.

Copying Formulas

Copying formulas is one of the most common tasks that you do in a typical spreadsheet that relies primarily on formulas. When a formula uses cell references rather than constant values (as most should), Excel makes the task of copying an original formula to every place that requires a similar location a piece of cake. The program does this by automatically adjusting the cell references in the original formula to suit the position of the copies that you make. It does this through a system known as relative cell addresses, whereby the column references in the cell address in the formula change to suit their new column position and the row references change to suit their new row position.

Figures 1-6 and 1-7 illustrate how this works. For Figure 1-6, I used the AutoSum button in cell B7 to build the original formula that uses the SUM function that totals the April sales. The formula in cell B7 reads

=SUM(B3:B6)

Figure 1-6: An original formula copied with the Fill handle across the last row of the spreadsheet table.

Figure 1-7: An original formula copied with the Fill handle down the last column of the data table.

I then used the AutoFill feature to copy this formula by dragging the Fill handle to include the cell range B7:J7. (Copying the formula with the cut-and-paste method would work just as well, although it’s a little more work.) Note in the cell range C7:J7 that Excel did not copy the original formula to the other cells verbatim. (Otherwise, each of the copied formulas would return the same result, 1915, as the original in cell B7.) If you look at the Formula bar in Figure 1-5, you see that the copy of the original formula in cell C7 reads

=SUM(C3:C6)

In this copy, Excel adjusted the column reference of the range being summed from B to C to suit the new position of the copy. Figure 1-7 shows how this works when copying an original formula in the other direction, this time down a column. For this figure, I used the AutoSum button to create a SUM formula that totals all the monthly sales for Part 100 in row 3. The formula in cell K3 reads

=SUM(B3:J3)

You can then use the Fill handle to copy this formula down the last column of the table to include the cell range by positioning the cell cursor in K3 and then dragging the Fill handle down to select K3:K7. If you were to then position the cell cursor in cell K4, you would see on the Formula bar that when Excel copied the original formula in cell K3 down to cell K4, it automatically adjusted the row reference to suit its new position so that the formula in cell K4 reads

=SUM(B4:J4)

Although at first glance it appears that Excel isn’t making exact copies of the original formula when it uses the relative cell addressing, that isn’t technically true. Although the cell column references in the first example in Figure 1-6 and the row references in the second example in Figure 1-7 appear to be adjusted to suit the new column and row position when you view the worksheet by using the R1C1 cell notation system, you’d actually see that, in R1C1 notation (unlike the default A1 system), each and every copy of the original formula is exactly the same.

For example, the original formula that I input into cell B7 (known as cell R7C2 in the R1C1 system) to sum the April sales for all the different part numbers reads as follows when you switch to R1C1 notation:

=SUM(R[-4]C:R[-1]C)

In this notation, the SUM formula is more difficult to decipher, so I will explain and then translate it for you. In R1C1 notation, the cell range in the SUM argument is expressed in terms completely relative to the position of the cell containing the formula. The row portion of the cell range expresses how many rows above or below the one with the formula the rows are. (Negative integers indicate rows above, whereas positive integers indicate rows below.) The column portion of the cell range in the SUM argument expresses how many columns to the left or right of the one with the formula the columns are. (Positive integers indicate columns to the right, and negative integers indicate columns to the left.) When a column or row in the cell range is not followed by an integer in square brackets, this means that there is no change in the column or row.

Armed with this information, my translation R1C1 form of this formula may just make sense; it says, “sum the values in the range of the cells that is four rows (R[-4]) above the current cell in the same column (C) down through the cell that is just one row (R[-1]) above the current cell in the same column (C).” When this original formula is copied over to the columns in the rest of the table, it doesn’t need to be changed because each copy of the formula performs this exact calculation (when expressed in such relative terms).

The original formula in Figure 1-7 that I entered into cell K3 and copied down to cell K6 appears as follows when you switch over to the R1C1 notation:

=SUM(RC[-9]:RC[-1])

It says, “sum the range of values in the cell nine columns to the left (C[-9]) in the same row through the cell that is one column to the left (C[-1]) in the same row.” This is exactly what all the copies of this formula in the rows below it do, so that when Excel copies this formula it doesn’t change.

You can use the R1C1 notation to check that you’ve copied all the formulas in a spreadsheet table correctly. Just switch to the R1C1 system by selecting the R1C1 Reference Style check box in the Working with Formulas section on the Formulas tab of the Excel Options dialog box (File  ⇒  Options  ⇒  Formulas). Then move the cell cursor through all the cells with copied formulas in the table. When R1C1 notation is in effect, all copies of an original formula across an entire row or down an entire column of the table should be identical when displayed on the Formula bar as you make their cells current.

Absolute references

Most of the time, relative cell references are exactly what you need in the formulas that you build, thus allowing Excel to adjust the row and/or column references as required in the copies that you make. You will encounter some circumstances, however, where Excel should not adjust one or more parts of the cell reference in the copied formula. This occurs, for example, whenever you want to use a cell value as a constant in all the copies that you make of a formula.

Figure 1-8 illustrates just such a situation. In this situation, you want to build a formula in cell B9 that calculates what percentage April’s part production total (B7) is of the total nine-month production (cell K7). Normally, you would create the following formula in cell B9 with all its relative cell references:

=B7/K7

Figure 1-8: Using an absolute address in the formula to calculate monthly percentage of the total.

However, because you want to copy this formula across to the range C9:J9 to calculate the percentages for the eight months (May through December), you need to alter the relative cell references in the last part of the formula in cell K7 so that this cell reference with the nine-month production total remains unchanged in all your copies.

You can start to understand the problem caused by adjusting a relative cell reference that should remain unchanged by just thinking about copying the original formula from cell B9 to C9 to calculate the percentage for May. In this cell, you want the following formula that divides the May production total in cell C7 by the nine-month total in cell K7:

=C7/K7

However, if you don’t indicate otherwise, Excel adjusts both parts of the formula in the copies, so that C9 incorrectly contains the following formula:

=C7/L7

Because cell L7 is currently blank and blank cells have the equivalent of the value 0, this formula returns the #DIV/0! formula error as the result, thus indicating that Excel can’t properly perform this arithmetic operation. (See Book III, Chapter 2 for details on this error message.)

To indicate that you don’t want a particular cell reference (such as cell K7 in the example) to be adjusted in the copies that you make of a formula, you change the cell reference from a relative cell reference to an absolute cell reference. In the A1 system of cell references, an absolute cell reference contains dollar signs before the column letter and the row number, as in $K$7. In the R1C1 notation, you simply list the actual row and column number in the cell reference, as in R7C11, without placing the row and column numbers in square brackets.

If you realize that you need to convert a relative cell reference to an absolute reference as you’re building the original formula, you can convert the relative reference to absolute by selecting the cell and then pressing F4. To get an idea of how this works, follow along with these steps for creating the correct formula =B7/$K$7 in cell B9:

1. Click cell B9 to make it active.

2. Type = to start the formula; then click cell B7 and type / (the sign for division).

The Formula bar now reads =B7/.

3. Click K7 to select this cell and add it to the formula.

The Formula bar now reads =B7/K7.

4. Press F4 once to change the cell reference from relative (K7) to absolute ($K$7).

The Formula bar now reads =B7/$K$7. You’re now ready to enter the formula and then make the copies.

5. Click the Enter button on the Formula bar and then drag the Fill handle to cell J9 before you release the mouse button.

Like it or not, you won’t always anticipate the need for an absolute value until after you’ve built the formula and copied it to a range. When this happens, you have to edit the original formula, change the relative reference to absolute, and then make the copies again.

When editing the cell reference in the formula, you can change its reference by positioning the insertion point anywhere in its address and then pressing F4. You can also do this by inserting dollar signs in front of the column letter(s) and row number when editing the formula, although doing that isn’t nearly as easy as pressing F4.

You can make an exact copy of a formula in another cell without using absolute references. To do this, make the cell with the formula that you want to copy the active one, use the I-beam pointer to select the entire formula in the Formula bar by dragging through it, and then click the Copy command button on the Home tab of the Ribbon (or press Ctrl+C). Next, click the Cancel button to deactivate the Formula bar, select the cell where you want the exact copy to appear, and then click the Paste command button on the Home tab (or press Ctrl+V). Excel then pastes an exact duplicate of the original formula into the active cell without adjusting any of its cell references (even if they are all relative cell references).

Keep in mind when using the sum options on the Totals tab of the Quick Analysis tool’s palette (see “Totals and sums with the Quick Analysis tool” earlier in this chapter for details) that all the cell references in the total and sum formulas that Excel creates are relative references.

If you’re building the formula that requires an absolute or some sort of mixed cell reference (see the following section) on a touchscreen device and using the Touch keyboard with no access to function keys, you need to add the required dollar sign(s) into the formula on the Formula bar by using the dollar sign ($) key on the Touch keyboard. To access the dollar sign key, tap the Numeric key (&123) to switch the Touch keyboard out of the QWERTY letter arrangement.

A mixed bag of references

Some formulas don’t require you to change the entire cell reference from relative to absolute in order to copy them correctly. In some situations, you need to indicate only that the column letter or the row number remains unchanged in all copies of the original formula. A cell reference that is part relative and part absolute is called a mixed cell reference.

In the A1 notation, a mixed cell reference has a dollar sign just in front of the column letter or row number that should not be adjusted in the copies. For example, $C10 adjusts row 10 in copies down the rows but leaves column C unchanged in all copies across columns to its right. Another example is C$10, which adjusts column C in copies to columns to the right but leaves row 10 unchanged in all copies down the rows. (For an example of using mixed cell references in a master formula, refer to the information on using the PMT Function in Book III, Chapter 4.)

To change the cell reference that you select in a formula (by clicking the flashing insertion point somewhere in its column letter and row number) from relative to mixed, continue to press F4 until the type of mixed reference appears on the Formula bar. When the Formula bar is active and the insertion point is somewhere in the cell reference (either when building or editing the formula), pressing F4 cycles through each cell-reference possibility in the following order:

· The first time you press F4, Excel changes the relative cell reference to absolute (C10 to $C$10).

· The second time you press F4, Excel changes the absolute reference to a mixed reference where the column is relative and the row is absolute ($C$10 to C$10).

· The third time you select the Reference command, Excel changes the mixed reference where the column is relative and the row is absolute to a mixed reference where the row is relative and the column is absolute (C$10 to $C10).

· The fourth time you press F4, Excel changes the mixed reference where the row is relative and the column is absolute back to a relative reference ($C10 to C10).

If you bypass the type of cell reference that you want to use, you can return to it by continuing to press F4 until you cycle through the variations again to reach the one that you need.

Adding Array Formulas

As noted previously in this chapter, many spreadsheet tables use an original formula that you copy to adjacent cells by using relative cell references (sometimes referred to as a one-to-many copy). In some cases, you can build the original formula so that Excel performs the desired calculation not only in the active cell, but also in all the other cells to which you would normally copy the formula. You do this by creating an array formula. An array formula is a special formula that operates on a range of values. If a cell range supplies this range (as is often the case), it is referred to as an array range. If this range is supplied by a list of numerical values, they are known as an array constant.

Although the array concept may seem foreign at first, you are really quite familiar with arrays because the column-and-row structure of the Excel worksheet grid naturally organizes your data ranges into one-dimensional and two-dimensional arrays. (1-D arrays take up a single row or column, whereas 2-D arrays take up multiple rows and columns.)

Figure 1-9 illustrates a couple of two-dimensional arrays with numerical entries of two different sizes. The first array is a 3 x 2 array in the cell range B2:C4. This array is a 3 x 2 array because it occupies three rows and two columns. The second array is a 2 x 3 array in the cell range F2:H3. This array is a 2 x 3 array because it uses two rows and three columns.

Figure 1-9: Worksheet with two different sizes of arrays.

If you were to list the values in the first 3 x 2 array as an array constant in a formula, they would appear as follows:

{1,4;2,5;3,6}

Several things in this list are noteworthy. First, the array constant is enclosed in a pair of braces ({}). Second, columns within each row are separated by commas (,) and rows within the array are separated by semicolons (;). Third, the constants in the array are listed across each row and then down each column and not down each column and across each row.

The second 2 x 3 array expressed as an array constant appears as follows:

{7,8,9;10,11,12}

Note again that you list the values across each row and then down each column, separating the values in different columns with commas and the values in different rows with a semicolon.

The use of array formulas can significantly reduce the amount of formula copying that you have to do in a worksheet by producing multiple results throughout the array range in a single operation. In addition, array formulas use less computer memory than standard formulas copied in a range. This can be important when creating a large worksheet with many tables because it may mean the difference between fitting all your calculations on one worksheet and having to split your model into several worksheet files.

Building an array formula

To get an idea of how you build and use array formulas in a worksheet, consider the sample worksheet shown in Figure 1-10. This worksheet is designed to compute the biweekly wages for each employee. It will do this by multiplying each employee’s hourly rate by the number of hours worked in each pay period. Instead of creating the following formula in cell R10 that you copy down the cells R11 through R13:

=A4*R4

Figure 1-10: Building an array formula to calculate hourly wages for the first pay period.

You can create the following array formula in the array range:

={A4:A7*R4:R7}

This array formula multiplies each of the hourly rates in the 4 x 1 array in the range A4:A7 with each of the hours worked in the 4 x 1 array in the range R4:R7. This same formula is entered into all cells of the array range (R10:R13) as soon as you complete the formula in the active cell R10. To see how this is done, follow along with the steps required to build this array formula:

1. Make cell R10 the current cell, and then select the array range R10:R13 and type = (equal sign) to start the array formula.

You always start an array formula by selecting the cell or cell range where the results are to appear. Note that array formulas, like standard formulas, begin with the equal sign.

2. Select the range A4:A7 that contains the hourly rate for each employee as shown, type an * (asterisk for multiplication), and then select the range R4:R7 that contains the total number of hours worked during the first pay period.

3. Press Ctrl+Shift+Enter to insert an array formula in the array range.

When you press Ctrl+Shift+Enter to complete the formula, Excel inserts braces around the formula and copies the array formula {=A4:A7*R4:R7} into each of the cells in the array range R10:R13.

When entering an array formula, you must remember to press Ctrl+Shift+Enter instead of just the Enter key because this special key combination tells Excel that you are building an array formula, so that the program encloses the formula in braces and copies it to every cell in the array range. Also, don’t try to create an array formula by editing it on the Formula bar and then insert curly braces because this doesn’t cut it. The only way to create an array formula is by pressing Ctrl+Shift+Enter to complete the formula entry.

Figure 1-11 shows you the February wage table after completing all the array formulas in three ranges: R10:R13, AI10:AI13, and AJ10:AJ13. In the second cell range, AI10:AI13, I entered the following array formula to calculate the hourly wages for the second pay period in February:

{=A4:A7*AI4:AI7}

Figure 1-11: The hourly wage spreadsheet after entering all three array formulas.

In the third cell range, AJ10:AJ13, I entered the following array formula to calculate the total wages paid to each employee in February 2016:

{=R10:R13+AI10:AI13}

When you enter an array formula, the formula should produce an array with the same dimensions as the array range that you selected. If the resulting array returned by the formula is smaller than the array range, Excel expands the resulting array to fill the range. If the resulting array is larger than the array range, Excel doesn’t display all the results. When expanding the results in an array range, Excel considers the dimensions of all the arrays used in the arguments of the operation. Each argument must have the same number of rows as the array with the most rows and the same number of columns as the array with the most columns.

Editing an array formula

Editing array formulas differs somewhat from editing normal formulas. In editing an array range, you must treat the range as a single unit and edit it in one operation (corresponding to the way in which the array formula was entered). This means that you can’t edit, clear, move, insert, or delete individual cells in the array range. If you try, Excel will display an Alert dialog box stating “You cannot change part of an array.”

To edit the contents of an array formula, select a cell in the array range and then activate Edit mode by clicking the formula or the Formula bar or pressing F2. When you do this, Excel displays the contents of the array formula without the customary braces. The program also outlines the ranges referred to in the array formula in the cells of the worksheet in different colors that match those assigned to the range addresses in the edited formula on the Formula bar. After you make your changes to the formula contents, you must remember to press Ctrl+Shift+Enter to enter your changes and have Excel enclose the array formula in braces once again.

If you want to convert the results in an array range to their calculated values, select the array range and click the Copy button on the Ribbon’s Home tab or press Ctrl+C. Then, without changing the selection, choose the Paste Values option from the Paste button’s drop-down menu (or press Alt+HVV). As soon as you convert an array range to its calculated values, Excel no longer treats the cell range as an array.

Range Names in Formulas

Thus far, all the example formulas in this chapter have used a combination of numerical constants and cell references (both relative and absolute and using the A1 and R1C1 notation). Although cell references provide a convenient method for pointing out the cell location in the worksheet grid, they are not at all descriptive of their function when used in formulas. Fortunately, Excel makes it easy to assign descriptive names to the cells, cell ranges, constants, and even formulas that make their function in the worksheet much more understandable.

To get an idea of how names can help to document the purpose of a formula, consider the following formula for computing the sale price of an item that uses standard cell references:

=B4*B2

Now consider the following formula that performs the same calculation but, this time, with the use of range names:

=Retail_Price*Discount_Rate

Obviously, the function of the second formula is much more comprehensible, not only to you as the creator of the worksheet but also to anyone else who has to use it.

Range names are extremely useful not only for documenting the function of the formulas in your worksheet, but also for finding and selecting cell ranges quickly and easily. This is especially helpful in a large worksheet that you aren’t very familiar with or only use intermittently. After you assign a name to a cell range, you can locate and select all the cells in that range with the Go To dialog box. Simply choose the Go To option from the Find & Select button’s drop-down menu on the Home tab of the Ribbon (or press Ctrl+G or F5). Then double-click the range name in the Go To list box, or click the range name and click OK or press Enter. Excel then selects the entire range and, if necessary, shifts the worksheet display so that you can see the first cell in that range on the screen.

If you’re using Excel 2016 on a Windows device such as a tablet without the benefit of a keyboard or mouse, you will definitely find it to your advantage to assign range names to often-used cell ranges in your spreadsheets. That way, you can go to and select these ranges simply by tapping the Name box drop-down button followed by the range name. That’s so much faster and easier than manually finding and selecting the range with your finger or stylus.

Defining range names

You can define a name for the selected cell range or nonadjacent selection by typing its range name into the Name box on the Formula bar and then pressing Enter. You can also name a cell, cell range, or nonadjacent selection by clicking the Define Name command button on the Ribbon’s Formulas tab or by pressing Alt+MMD. Excel then opens the New Name dialog box, where you can input the selection’s range name in the Name text box.

If Excel can identify a label in the cell immediately above or to the left of the active one, the program inserts this label as the suggested name in the Name text box. The program also displays the scope of the range name in the Scope drop-down list box and the cell reference of the active cell or the range address of the range or nonadjacent selection that is currently marked (by using absolute references) in the Refers To text box below. You can do the following:

· To change the scope from the entire workbook to a particular worksheet in the workbook so that the range name is only recognized on that sheet, select the sheet’s name from the Scope drop-down list.

· To change the cell range the name refers to, select the cells in the worksheet. (Remember that you can collapse the New Name dialog box to the Refers To text box by clicking its Collapse button.)

· To accept the suggested or edited name, scope, and cell selection, click the OK button, shown in Figure 1-12.

Figure 1-12: Adding a new range name in the New Name dialog box.

When naming a range in the Name text box of the New Name dialog box, you need to follow the same naming conventions as when defining a name in the Name box on the Formula bar. Basically, this means that the name must begin with a letter rather than a number, contain no spaces, and not duplicate any other name in the workbook. (See Book II, Chapter 2 for more on naming ranges.)

If you want to assign the same range name to similar ranges on different worksheets in the workbook, preface the range name with the sheet name followed by an exclamation point and then the descriptive name. For example, if you want to give the name Costs to the cell range A2:A10 on both Sheet1 and Sheet2, you name the range Sheet1!Costs on Sheet1 and Sheet2!Costs on Sheet2. If you have renamed the worksheet to something more descriptive than Sheet1, you need to enclose the name in single quotes if it contains a space when you enter the range. For example, if you rename Sheet1 to Inc. Statement 16, you enter the range name including the worksheet reference for the Costs cell range as follows:

'Inc. Statement 16'!Costs

When you preface a range name with the sheet name as shown in this example, you don’t have to use the sheet name part of the range name in the formulas that you create on the same worksheet. In other words, if you create a SUM formula that totals the values in the ‘Inc. Statement 16’!Costs range somewhere on the Inc. Statement 16 worksheet, you can enter the formulas as follows:

=SUM(Costs)

However, if you were to create this formula on any other worksheet in the workbook, you would have to include the full range name in the formula, as in

=SUM('Inc. Statement 16'!Costs)

Naming constants and formulas

In addition to naming cells in your worksheet, you can also assign range names to the constants and formulas that you use often. For example, if you are creating a spreadsheet table that calculates sales prices, you can assign the discount percentage rate to the range name discount_rate. Then, you can supply this range name as a constant in any formula that calculates the sale discount used in determining the sale price for merchandise.

For example, to assign a constant value of 15% to the range name discount_rate, you open the New Name dialog box and then type discount_rate in the Name text box and =15% as the discount rate in the Refers To text box before selecting OK. After assigning this constant percentage rate to the range name discount_rate in this manner, you can apply it to any formula by typing or pasting in the name (see the “Using names in building formulas” section that follows in this chapter for details).

In addition to naming constants, you can also give a range name to a formula that you use repeatedly. When building a formula in the Refers To text box of the New Name dialog box (Alt+MMD), keep in mind that Excel automatically applies absolute references to any cells that you point to in the worksheet. If you want to create a formula with relative cell references that Excel adjusts when you enter or paste the range name in a new cell, you must press F4 to convert the current cell reference to relative or type in the cell address without dollar signs.

When creating the constant in the New Name dialog box, don’t change the Scope setting from Workbook to a particular sheet in the workbook unless you’re positive that you’ll never need to use that constant in a formula on any other worksheet. If you limit the scope to a particular worksheet, Excel 2016 does not let you use the range name in a formula on any other worksheet (you’ll get the #NAME? error), and Scope is the one aspect you can’t change when editing a range name via the Name Manager. (I discuss managing range names later in this chapter).

Using names in building formulas

After you assign a name to a cell or cell range in your worksheet, you can then choose the range name from the Use in Formula button’s drop-down menu on the Ribbon’s Formulas tab to paste it into the formulas that you build (Alt+MS).

For example, in the sample Autumn 2016 Furniture Sale table shown in Figure 1-13, after assigning the discount rate of 15% to the range name, discount_rate, you can create the formulas that calculate the amount of the sale discount. To do this, you multiply the retail price of each item by the discount_rate constant using the Use in Formula command button by following these steps:

1. Make cell D3 active.

2. Type = (equal sign) to start the formula.

3. Click cell C3 to select the retail price for the first item and then type * (asterisk).

The formula on the Formula bar now reads, =C3*.

4. Click the Use in Formula button on the FORMULAS tab or press Alt+MS.

This action opens the drop-down menu on the Use in Formula button on which you can select the discount_rate range name.

5. Choose the name discount_rate from the Use in Formula button’s drop-down menu.

The formula now reads =C3*discount_rate on the Formula bar.

6. Click the Enter button on the Formula bar to input the formula in cell D3.

Now, all that remains is to copy the original formula down column D.

7. Drag the Fill handle in cell D3 down to cell D7 and release the mouse button to copy the formula and calculate the discount for the entire table.

Figure 1-13: Pasting the range name for the discount_rate constant into a formula.

Creating names from column and row headings

You can use the Create from Selection command button on the Formulas tab of the Ribbon to assign existing column and row headings in a table of data to the cells in that table. When using this command button, you can have Excel assign the labels used as column headings in the top or bottom row of the table, the labels used as row headings in the leftmost or rightmost column, or even a combination of these headings.

For example, the sample worksheet in Figure 1-14 illustrates a typical table layout that uses column headings in the top row of the table and row headings in the first column of the table. You can assign these labels to the cells in the table by using the Create from Selection command button as follows:

1. Select the cells in the table, including those with the column and row labels that you want to use as range names.

For the example shown in Figure 1-14, you select the range B2:E7.

2. Click the Create from Selection command button on the Formulas tab or press Alt+MC.

This action opens the Create Names from Selection dialog box that contains four check boxes: Top Row, Left Column, Bottom Row, and Right Column. The program selects the check box or boxes in this dialog box based on the arrangement of the labels in your table. In the example shown in Figure 1-14, Excel selects both the Top Row and Left Column check boxes because the table contains both column headings in the top row and row headings in the left column.

3. After selecting (or deselecting) the appropriate Create Names In check boxes, click the OK button to assign the range names to your table.

Figure 1-14: Creating range names from the row and column headings in a spreadsheet table.

Note that when you select both the Top Row and Left Column check boxes in the Create Names from Selection dialog box, Excel assigns the label in the cell in the upper-left corner of the table to the entire range of values in the table (one row down and one column to the right).

In the example illustrated in Figure 1-14, Excel assigns the name Item_Description (the heading for column B) to the cell range C3:E7. Similarly, the program assigns the column headings to the appropriate data in the table in the rows below, and assigns the row headings to the data in the appropriate columns to the right so that the name Retail_Price is assigned to the cell range C3:C7 and the name China_Hutch is assigned to the cell range C7:E7.

Managing range names

As you assign range names in your workbook, their names appear in the Name Manager dialog box. (See Figure 1-15.) You open this dialog box by clicking the Name Manager command button on the Formulas tab of the Ribbon or by pressing Alt+MN.

Figure 1-15: The Name Manager lists all range names defined in the workbook.

The Name Manager enables you to do any of the following:

· Create new range names for the worksheet or workbook in the New Name dialog box opened by clicking the New button. (See “Defining range names” earlier in this chapter.)

· Edit existing range names in the Edit Name dialog box by clicking the name in the list box and then clicking the Edit button — you can change both the name and cell selection when editing a range name.

· Delete existing range names by clicking the name and then clicking the Delete button followed by the OK button in the Alert dialog box asking you to confirm its deletion.

· Filter the range names in the list box of the Name Manager by clicking the Filter button and then choosing a filter option (Names Scoped to Worksheet, Names Scoped to Workbook, Names with Errors, Names without Errors, Defined Names, or Table Names) from its drop-down menu.

Be careful that you don’t delete a range name that is already used in formulas in the worksheet. If you do, Excel will return the #NAME? error value to any formula that refers to the name you deleted!

Applying names to existing formulas

Excel doesn’t automatically replace cell references with the descriptive names that you assign to them in the New Name or Create Names from Selection dialog boxes. To replace cell references with their names, you need to choose the Apply Names option from the Define Name button’s drop-down menu or press Alt+MMA.

When you choose this command, Excel opens the Apply Names dialog box, where you select the range names that you want applied in formulas used in your worksheet by selecting the names in the Apply Names list box.

Note that when you first open this dialog box, it contains just two check boxes: Ignore Relative/Absolute and Use Row and Column Names (both of which are selected). You can click the Options button to expand the Apply Names dialog box and display other options that you can use when applying your range names, shown in Figure 1-16. The Apply Names options include the following:

· Ignore Relative/Absolute check box: The program replaces cell references with the names that you’ve selected in the Apply Names list box, regardless of the type of reference used in their formulas. If you want Excel to replace only those cell references that use the same type of references as are used in your names (absolute for absolute, mixed for mixed, and relative for relative), deselect this check box. Most of the time, you’ll want to leave this check box selected because Excel automatically assigns absolute cell references to the names that you define and relative cell references to the formulas that you build.

· Use Row and Column Names check box: The names created from row and column headings with the Create Names command appear in your formulas. Deselect this option if you don’t want these row and column names to appear in the formulas in your worksheet.

· Omit Column Name If Same Column check box: This prevents Excel from repeating the column name when the formula is in the same column. Deselect this check box when you want the program to display the column name even in formulas in the same column as the heading used to create the column name.

· Omit Row Name If Same Row check box: This prevents Excel from repeating the row name when the formula is in the same row. Deselect this check box when you want the program to display the row name even in formulas in the same row as the heading used to create the row name.

· Name Order: Choose the Row Column option button (the default) if you want the row name to precede the column name when both names are displayed in the formulas, or choose the Column Row option button if you want the column name to precede the row name.

Figure 1-16: Using the options in the Apply Names dialog box to assign range names to formulas.

After applying all the range names by using the default Apply Names options (that is, Ignore Relative/Absolute, Use Row and Column Names, Omit Column Name If Same Column, Omit Row Name If Same Row, and Name Order options selected), Excel replaces all the cell references in the formulas in the Sale Price table. In cell E3, for example, in place of the original formula, =C3-D3, the cell now contains the formula

=Retail_Price-Discount

Cell D3, to the immediate left, instead of =C3*discount_rate now contains:

=Retail_Price*discount_rate

Only one problem occurs with applying names by using the default settings. This problem begins to show up as soon as you select cell E4. Although this formula subtracts cell D4 from C4, its contents now also appear as

=Retail_Price-Discount

This is identical in appearance to the contents of cell E3 above (and, in fact, identical in appearance to cells E5, E6, and E7 in the cells below).

The reason that the formulas all appear identical (although they’re really not) is because the Omit Row Name If Same Row check box was selected when I applied the range names to the formulas. With this setting selected, Excel doesn’t bother to repeat the row name when the formula is in the same row.

When you deselect the Omit Row Name if Same Row check box while still selecting the Use Row and Column Name check box in the Apply Names dialog box, the formula in cell E3 would appear as follows:

=Dining_Table Retail_Price-Dining-Table Discount

If you were then to select cell E4 below, the formula would now appear quite differently in this form:

=Side_Chair Retail_Price-Side_Chair Discount

Now Excel displays both the row and column names separated by a space for each cell reference in the formulas in this column. Remember that the space between the row name and column name is called the intersection operator. (Refer to Table 1-1.) You can interpret the formula in E3 as saying, “Take the cell at the intersection of the Table row and Retail_Price column and subtract it from the cell at the intersection of the Table row and Discount column.” The formula in E4 is similar, except that it says, “Take the cell at the intersection of the Side_chair row and Retail_Price column and subtract it from the cell at the intersection of the Side_chair row and Discount column.”

Adding Linking Formulas

Linking formulas are formulas that transfer a constant or other formula to a new place in the same worksheet, same workbook, or even a different workbook without copying it to its new location. When you create a linking formula, it brings forward the constant or original formula to a new location so that the result in the linking formula remains dynamically tied to the original. If you change the original constant or any of the cells referred to in the original formula, the result in the cell containing the linking formula is updated at the same time as the cell containing the original constant or formula.

You can create a linking formula in one of two ways:

· Select the cell where you want the linking formula, type = (equal sign), and then click the cell with the constant (text or number) or the formula that you want to bring forward to that cell. Complete the cell entry by clicking the Enter button on the Formula bar or pressing the Enter key.

· Select the cell with the constant or formula that you want to bring forward to a new location and then click the Copy button in the Clipboard group on the Ribbon’s Home tab or press Ctrl+C. Then click the cell where the linking formula is to appear before you choose the Paste Link option from the Paste button’s drop-down menu.

When you use the first simple formula method to create a link, Excel uses a relative cell reference to refer to the cell containing the original value or formula (as in =A10 when referring to an entry in cell A10). However, when you use the second copy-and-paste link method, Excel uses an absolute cell reference to refer to the original cell (as in =$A$10 when referring to an entry in cell A10).

When you create a linking formula to a cell on a different sheet of the same workbook, Excel inserts the worksheet name (followed by an exclamation point) in front of the cell address. So, if you copy and paste a link to a formula in cell A10 on a different worksheet called Income 16, Excel inserts the following linking formula:

='Income 16'!$A$10

When you create a linking formula to a cell in a different workbook, Excel inserts the workbook filename enclosed in square brackets before the name of the worksheet, which precedes the cell address. So, if you bring forward a formula in cell A10 on a worksheet called Cost Analysis in the Projected Income 17 workbook, Excel inserts this linking formula:

='[Projected Income 17.xls]Cost Analysis'!$A$10

If you ever need to sever a link between the cell containing the original value or formula and the cell to which it’s been brought forward, you can do so by editing the linking formula. Press F2, then immediately recalculate the formula by pressing F9, and then click the Enter button on the Formula bar or press Enter. This replaces the linking formula with the currently calculated result. Because you’ve converted the dynamic formula into a constant, changes to the original cell no longer affect the one to which it was originally brought forward.

Controlling Formula Recalculation

Normally, Excel recalculates your worksheet automatically as soon you change any entries, formulas, or names on which your formulas depend. This system works fine as long as the worksheet is not too large or doesn’t contain tables whose formulas depend on several values.

When Excel does calculate your worksheet, the program recalculates only those cells that are affected by the change that you’ve made. Nevertheless, in a complex worksheet that contains many formulas, recalculation may take several seconds (during which time, the pointer will change to an hourglass, and the word “Recalculation” followed by the number of cells left to be recalculated will appear on the left side of the Formula bar).

Because Excel recalculates dependent formulas in the background, you can always interrupt this process and make a cell entry or choose a command even when the pointer assumes the hourglass shape during the recalculation process. As soon as you stop making entries or selecting commands, Excel resumes recalculating the worksheet.

To control when Excel calculates your worksheet, you click the Calculation Options button on the Formulas tab of the Ribbon and then click the Manual option button or press Alt+MXM. After switching to manual recalculation, when you make a change in a value, formula, or name that would usually cause Excel to recalculate the worksheet, the program displays the message “Calculate” on the status bar.

When you’re ready to have Excel recalculate the worksheet, you then click the Calculate Now (F9) command button (the one with a picture of the handheld calculator) on the Ribbon’s FORMULAS tab or press F9 or Ctrl+=. This tells the program to recalculate all dependent formulas and open charts and makes the Calculate status indicator disappear from the status bar.

After switching to manual recalculation, Excel still automatically recalculates the worksheet whenever you save the file. When you are working with a really large and complex worksheet, recalculating the worksheet each time you want to save your changes can make this process quite time-consuming. If you want to save the worksheet without first updating dependent formulas and charts, you need to deselect the Recalculate Workbook before Saving check box in the Calculation Options section of the Formulas tab of the Excel Options dialog box (File  ⇒  Options  ⇒  Formulas or Alt+FTF).

If your worksheet contains data tables used to perform what-if analyses, switch from Automatic to Automatic except Data Tables recalculation by choosing Automatic Except Data Tables from the Options button’s drop-down menu on the Formulas tab or pressing Alt+MXE. Doing so enables you to change a number of variables in the what-if formulas before having Excel recalculate the data table. (See Book VII, Chapter 1 for more on performing what-if analyses.)

Automatic, Automatic Except Data Tables, and Manual are by no means the only calculation options available in Excel. Table 1-3 explains each of the options that appear in the Calculation Options section of the Formulas tab of the Excel Options dialog box.

Table 1-3 The Calculation Options in Excel 2016

Circular References

A circular reference in a formula is one that depends, directly or indirectly, on its own value. The most common type of circular reference occurs when you mistakenly refer in the formula to the cell in which you’re building the formula itself. For example, suppose that cell B10 is active when you build this formula:

=A10+B10

As soon as you click the Enter button on the Formula bar or press Enter or an arrow key to insert this formula in cell B10 (assuming the program is in Automatic recalculation mode), Excel displays an Alert dialog box, stating that it cannot calculate the formula due to the circular reference.

If you then press Enter or click OK to close this Alert dialog box, an Excel Help window appears containing general information about circular references in two sections: Locate and Remove a Circular Reference and Make a Circular Reference Work by Changing the Number of Times Microsoft Excel Iterates Formulas.

When you close this Excel Help window by clicking its Close button, Excel inserts 0 in the cell with the circular reference, and the Circular Reference status indicator followed by the cell address with the circular reference appears on the status bar.

Some circular references are solvable by increasing the number of times they are recalculated (each recalculation bringing you closer and closer to the desired result), whereas others are not (for no amount of recalculating brings them closer to any resolution) and need to be removed from the spreadsheet.

The formula in cell B10 is an example of a circular reference that Excel is unable to resolve because the formula’s calculation depends directly on the formula’s result. Each time the formula returns a new result, this result is fed into the formula, thus creating a new result to be fed back into the formula. Because this type of circular reference sets up an endless loop that continuously requires recalculating and can never be resolved, you need to fix the formula reference or remove the formula from the spreadsheet.

Figure 1-17 illustrates the classic example of a circular reference, which ultimately can be resolved. Here, you have an income statement that includes bonuses equal to 20 percent of the net earnings entered as an expense in cell B15 with the formula

=-B21*20%

Figure 1-17: Income statement with a resolvable circular reference.

This formula contains a circular reference because it refers to the value in B21, which itself indirectly depends on the amount of bonuses (the bonuses being accounted for as an expense in the very worksheet formulas that determine the amount of net earnings in cell B21).

To resolve the circular reference in cell B15 and calculate the bonuses based on net earnings in B21, you simply need to select the Enable Iterative Calculation check box in the Calculation Options section of the Formulas tab in the Excel Options dialog box (File  ⇒  Options  ⇒  Formulas or Alt+FTF). However, if manual recalculation is selected, you must click the Calculate Now (F9) command button on the Formulas tab of the Ribbon or press F9 or Ctrl+= as well