Chapter 2

Creating advanced formulas

Excel is a versatile program with many uses, from acting as a checkbook to a flat-file database-management system, to an equation solver, to a glorified calculator. For most business users, however, Excel’s forte is building models that enable quantification of particular aspects of the business. The skeleton of the business model is made up of the chunks of data entered, imported, or copied into the worksheets. But the lifeblood of the model and the animating force behind it is the collection of formulas for summarizing data, answering questions, and making predictions.

You saw in Chapter 1, “Building basic formulas,” that, armed with the humble equal sign and Excel’s operators and operands, you can cobble together useful, robust formulas. But Excel has many other tricks up its digital sleeve, and these techniques enable you to create muscular formulas that can take your business models to the next level.

This is in contrast with the subject of this section: the array. An array is a group of cells or values that Excel treats as a unit. In a range configured as an array, for example, Excel no longer treats the cells individually. Instead, it works with all the cells at once, which means you can apply a formula to every cell in the range by using just a single operation, for example.

You create arrays either by running a function that returns an array result (such as RANDARRAY() or DOCUMENTS(); see the section “Functions that use or return arrays,” later in this chapter) or by entering an array formula, which is a single formula that either uses an array as an argument or enters its results in multiple cells.

You can replace all three formulas with a single array formula by following these steps:

1. Select the range you want to use for the array formula. In the 2019 BUDGET example, you’d select C13:E13.

2. Enter the formula and, in the places where you would normally enter a cell reference, specify a range reference that includes the cells you want to use. Do not—I repeat, do not—press Enter when you’re done. In the example, you’d enter =C11:E11*$C$3.

3. To enter the formula as an array, select Ctrl+Shift+Enter.

The 2019 BUDGET cells (C13, D13, and E13) now contain the same formula:

{=C11:E11*$C$3}

In other words, you were able to enter a formula into three different cells by using just a single operation. This can save you significant amounts of time when you would otherwise have to enter the same formula into many different cells.

Notice that the formula is surrounded by braces ({ }). This identifies the formula as an array formula. (When you enter array formulas, you never need to enter these braces yourself; Excel adds them automatically when you press Ctrl+Shift+Enter.)

• You must select the array formula range in advance. That’s fine for just three cells, as in the previous section’s example, but it’s a pain in the neck if you’re dealing with dozens or even hundreds of cells.

• When it’s time to accept the formula, you must remember to select Ctrl+Shift+Enter instead of just Enter.

• The resulting formula is surrounded by braces ({ }), which can confuse things because people new to arrays often think they need to enter those braces manually.

These conundrums might be why Microsoft decided to change how array formulas work in Excel 2019. In particular, although the steps in the previous section still work, as an alterative you can now use the following steps to build an array formula:

1. Select the first cell in the range you want to use for the array formula. In the 2019 BUDGET example, you’d select C13.

2. Make sure the other cells in the range you want to use for the array formula are empty. In the 2019 BUDGET example, delete the existing formulas from cells D13 and E13.

3. Enter the formula and, in the places where you would normally enter a cell reference, specify a range reference that includes the cells you want to use. In the example, you’d enter =C11:E11*$C$3.

4. Select Enter.

In these steps, you see that Excel 2019 solves the problems I outlined at the beginning of this section as follows:

• You have to select only the first cell in the array formula’s results range. Excel automatically fills in—or spills—the results to the rest of the range based on the parameters in your formula, which is why this new type of array is called a dynamic array. It’s also why you need to clear out the rest of the array results range; a dynamic array will only spill into blank cells.

  Note

  If the dynamic array formula’s results range isn’t blank, Excel generates a #SPILL! error. Delete or move the data that’s blocking the spill, and Excel automatically displays the correct results.

• You can now accept the array formula by selecting Enter instead of Ctrl+Shift+Enter.

• The resulting dynamic array formula is no longer surrounded by braces. In the example, the 2019 BUDGET cells (C13, D13, and E13) now contain this formula:

  =C11:E11*$C$3

  Caution

  Dynamic array formulas and array spilling are welcome new modifications to arrays, but they only work with Excel 2019 or later. If your workbook will be viewed or used by someone using an earlier version of Excel, then you need to enter your array formula the old way.

Array formulas can be confusing, but keeping these correspondences in mind can help you figure out what’s going on.

You can replace all these formulas by making the following entry as an array formula into the range F12:F16:

=B12:B16*E12:E16

Again, you’ve created the array formula by replacing each cell reference with the corresponding range (and by pressing Ctrl+Shift+Enter).

If you want to use an Excel 2019 dynamic array, instead, delete the formulas from the range F13:F16, and then enter the following formula in cell F12 and select Enter:

=B12:B16*E12:E16

=SUM(B12:B16*E12:E16)

To enter an array constant in a formula, observe the following guidelines while entering the values right in the formula:

• Enclose the values in braces ({}).

• If you want Excel to treat the values as a row, enter a comma after each value (except the last value).

• If you want Excel to treat the values as a column, enter a semicolon after each value (except the last value).

For example, the following array constant is the equivalent of entering the individual values in a column on your worksheet:

{1;2;3;4}

Similarly, the following array constant is equivalent to entering the values in a worksheet range of three columns and two rows:

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

As a practical example, Figure 2-4 shows two different array formulas. The one on the left (used in the range E4:E7) calculates various loan payments, given the different interest rates in the range C5:C8. The array formula on the right (used in the range F4:F7) does the same thing, but the interest rate values are entered as an array constant directly in the formula.

Excel 2019 also includes several dynamic array functions that you include as part of a formula in a single cell and that produce results that spill into multiple cells. Table 2-2 lists the dynamic array functions in Excel 2019.

Arrays become truly powerful weapons in your Excel arsenal when you combine them with worksheet functions such as IF() and SUM(). I’ll provide you with many examples of array formulas as I introduce you to Excel’s worksheet functions throughout Part II.

Click here to view code image

Net Profit = Gross Profit - Profit Sharing Contribution

This is called a circular reference formula because there are terms on the left and right sides of the equal sign that depend on each other. Specifically, Profit Sharing Contribution is derived with the following formula:

Click here to view code image

Profit Sharing Contribution = (Net Profit) * 0.1

One way to solve such a formula is to guess at an answer and see how close you come. For example, because profit sharing should be 10% of net profits, a good first guess might be 10% of gross profits, or $10,000. If you plug this number into the formula, you end up with a net profit of $90,000. However, this isn’t right because 10% of $90,000 is $9,000. Therefore, the profit-sharing guess is off by $1,000.

So, you can try again. This time, use $9,000 as the profit-sharing number. Plugging this new value into the formula gives a net profit of $91,000. This number translates into a profit-sharing contribution of $9,100—which is off by only $100.

If you continue this process, your profit-sharing guesses will get closer to (that is, it will converge on) the calculated value. When the guesses are close enough (for example, within $1), you can stop and pat yourself on the back for finding the solution. This technique is called iteration.

Of course, you didn’t spend your (or your company’s) hard-earned money on a computer so that you could do this sort of thing by hand. Excel makes iterative calculations a breeze, as you see in the following procedure:

1. Set up your worksheet and enter your circular reference formula. Figure 2-5 shows a worksheet for the profit-sharing example just discussed. If Excel displays a dialog box telling you that it can’t resolve circular references, select OK and then select Formulas > Remove Arrows (see Chapter 3, “Troubleshooting formulas”).

   

2. Select File > Options > Formulas to display Excel formula options.

3. Select the Enable Iterative Calculation check box.

4. Use the Maximum Iterations spin box to enter the number of iterations you need. In most cases, the default figure of 100 is more than enough.

5. Use the Maximum Change text box to enter how accurate you want your results to be. The smaller the number, the longer the iteration takes and the more accurate the calculation will be. Again, the default value of 0.001 is a reasonable compromise in most situations.

6. Select OK. Excel begins the iteration and stops when it has found a solution (see Figure 2-6).

   

   Tip

   If you want to watch the progress of the iteration, select the Manual option button in the Calculation Options section of the Formulas tab and enter 1 in the Maximum Iterations spin box. When you return to your worksheet, each time you press F9, Excel performs a single pass of the iteration.

Creating these worksheets, distributing them, and filling them in are all straightforward operations. However, the tricky part comes when the sheets are returned to the originating department, and all the new data must be combined into a summary report showing companywide totals. This task is called consolidating the data, and it’s often no picnic, especially for large worksheets. However, as you’ll soon see, Excel has some powerful features that can take the drudgery out of consolidation.

Excel can consolidate your data using one of the following two methods:

• Consolidating by position: With this method, Excel consolidates the data from several worksheets, using the same range coordinates on each sheet. You can use this method if the worksheets you’re consolidating have an identical layout.

• Consolidating by category: This method tells Excel to consolidate the data by looking for identical row and column labels in each sheet. For example, if one worksheet lists monthly Gizmo sales in row 1 and another lists monthly Gizmo sales in row 5, you can consolidate this information as long as both sheets have a “Gizmo” label at the beginning of these rows.

In both cases, you specify one or more source ranges (the ranges that contain the data you want to consolidate) and a destination range (the range where the consolidated data will appear). The next couple of sections take you through the details for both consolidation methods.

Begin by creating a new worksheet that has the same layout as the sheets you’re consolidating. Figure 2-8 shows a new Consolidate By Position worksheet that I’ll use to consolidate the three budget sheets. (The zeroes you see in this worksheet are the results of the sheet’s formulas, which will update automatically when the consolidation is complete.)

Let’s look at how to go about consolidating the sales data in the three budget worksheets shown in Figure 2-7. We’re dealing with three source ranges:

'[Division I'!B4:M6
'[Division II'!B4:M6
'[Division III'!B4:M6

With the consolidation sheet active, follow these steps to consolidate by position:

1. Select the upper-left corner of the destination range. In the Consolidate By Position worksheet, the cell to select is B4.

2. Select Data > Consolidate. Excel displays the Consolidate dialog box.

3. In the Function drop-down list, select the operation to use during the consolidation. You’ll use Sum most of the time, but Excel has 10 other operations to choose from, including Count, Average, Max, and Min.

4. In the Reference text box, enter a reference for one of the source ranges. Use one of the following methods:

   • Enter the range coordinates by hand. If the source range is in another workbook, be sure to include the workbook name enclosed in square brackets. If the workbook is in a different drive or folder, include the full path to the workbook as well.

   • If the sheet is open, select it and then select the range.

   • If the workbook isn’t open, select Browse, select the file in the Browse dialog box, and then select OK. Excel adds the workbook path to the Reference box. Enter the sheet name and the range coordinates.

5. Select Add. Excel adds the range to the All References box (see Figure 2-9).

   

6. Repeat steps 4 and 5 to add all the source ranges.

7. If you want the consolidated data to change whenever you make changes to the source data, leave the Create Links to Source Data check box selected.

8. Select OK. Excel gathers the data, consolidates it, and then adds it to the destination range (see Figure 2-10).

   

If you chose not to create links to the source data in step 7, Excel just fills the destination range with the consolidation totals. However, if you did create links, Excel does three things:

• Adds link formulas to the destination range for each cell in the source ranges you selected.

• Consolidates the data by adding SUM() functions (or whichever operation you selected in the Function list) that total the results of the link formulas.

• Outlines the consolidation worksheet and hides the link formulas, as you can see in Figure 2-10.

If you display the Level 1 data, you’ll see the linked formulas. For example, Figure 2-11 shows the detail for the consolidated sales number for Books in January (cell B7). Cells B4, B5, and B6 contain formulas that link to the corresponding cells in the three budget worksheets (for example, ‘Division I’!$B$4).

As you can see, Division C sells books, software, games, and DVDs; Division B sells books and DVDs; and Division A sells software, books, and games. Here’s how you go about consolidating these numbers. (Note that I’m skipping over some of the details given in the preceding section.)

1. Create or select a new worksheet for the consolidation and select the upper-left corner of the destination range. It isn’t necessary to enter labels for the consolidated data because Excel does it for you automatically. However, if you want to see the labels in a particular order, it’s okay to enter them yourself.

   Caution

   If you enter the labels yourself, make sure that you spell the labels exactly as they’re spelled in the source worksheets.

2. Select Data > Consolidate to display the Consolidate dialog box.

3. In the Function drop-down menu, select the operation to use during the consolidation.

4. In the Reference text box, enter a reference for one of the source ranges. In this case, make sure you include in each range the row and column labels for the data.

5. Select Add to add the range to the All References box.

6. Repeat steps 4 and 5 to add all the source ranges.

7. If you want the consolidated data to change whenever you make changes to the source data, leave the Create Links To Source Data check box selected.

8. If you want Excel to use the data labels in the top row of the selected ranges, select the Top Row check box. If you want Excel to use the data labels in the left column of the source ranges, select the Left Column check box.

9. Select OK. Excel gathers the data according to the row and column labels, consolidates it, and then adds it to the destination range (see Figure 2-13).

   

• Data that is the wrong type: For example, typing a text string in a cell that requires a number

• Data that falls outside of an allowable range: For example, typing 200 in a cell that requires a number between 1 and 100

You can prevent these kinds of improper entries, to a certain extent, by adding comments that provide details on what is allowable inside a particular cell. However, this requires other people to both read and act on the comment text. Another solution is to use custom numeric formatting to “format” a cell with an error message if the wrong type of data is entered. This is useful, but it works only for certain kinds of input errors.

The best solution for preventing data entry errors is to use Excel’s data-validation feature. With data validation, you create rules that specify exactly what kind of data can be entered and in what range that data can fall. You can also specify pop-up input messages that appear when a cell is selected, as well as error messages that appear when data is entered improperly.

You can also ask Excel to “circle” any cells that contain data-validation errors (which is handy when you import data into a list that contains data-validation rules). You do this by choosing Data > Data Validation > Circle Invalid Data.

Follow these steps to define the settings for a data-validation rule:

1. Select the cell or range to which you want to apply the data-validation rule.

2. Select Data > Data Validation. Excel displays the Data Validation dialog box.

3. In the Settings tab, use the Allow list to select one of the following validation types:

   • Any Value: Allows any value in the range. (That is, it removes any previously applied validation rule. If you’re removing an existing rule, be sure to also clear the input message if you created one, as shown later in step 7.)

   • Whole Number: Allows only whole numbers (integers). Use the Data list to select a comparison operator (between, equal to, less than, and so on), and then enter the specific criteria. (For example, if you select the Between option, you must enter Minimum and Maximum values, as shown in Figure 2-14.)

     

   • Decimal: Allows decimal numbers or whole numbers. Use the Data list to select a comparison operator, and then enter the specific numeric criteria.

   • List: Allows only values specified in a list. Use the Source box to specify either a range on the same sheet or a range name on any sheet that contains the list of allowable values. (Precede the range or range name with an equal sign.) Alternatively, you can enter the allowable values directly into the Source box (separated by commas). If you want the user to be able to select from the allowable values using a drop-down list, leave the In-Cell Drop-Down check box selected.

   • Date: Allows only dates. (If the user includes a time value, the entry is invalid.) Use the Data list to select a comparison operator, and then enter the specific date criteria (such as a Start Date and an End Date).

   • Time: Allows only times. (If the user includes a date value, the entry is invalid.) Use the Data list to select a comparison operator, and then enter the specific time criteria (such as a Start Time and an End Time).

   • Text Length: Allows only alphanumeric strings of a specified length. Use the Data list to select a comparison operator, and then enter the specific length criteria (such as Minimum and Maximum lengths).

   • Custom: Use this option to enter a formula that specifies the validation criteria. You can either enter the formula directly into the Formula box (be sure to precede the formula with an equal sign), or enter a reference to a cell that contains the formula. For example, if you’re restricting cell A2 and you want to be sure the entered value is not the same as what’s in cell A1, enter the formula =A2<>A1.

4. To allow blank entries, either in the cell itself or in other cells specified as part of the validation settings, leave the Ignore Blank check box selected. If you deselect this check box, Excel treats blank entries as zero and applies the validation rule accordingly.

5. If the range had an existing validation rule that also applied to other cells, you can apply the new rule to those other cells by selecting the Apply These Changes To All Other Cells With The Same Settings check box.

6. Select the Input Message tab.

7. If you want a pop-up box to appear when the user selects the restricted cell or any cell within the restricted range, leave the Show Input Message When Cell Is Selected check box selected. Use the Title and Input Message boxes to specify the message that appears. For example, you could use the message to give the user information on the type and range of allowable values.

8. Select the Error Alert tab.

9. If you want a dialog box to appear when the user enters invalid data, leave the Show Error Alert After Invalid Data Is Entered check box selected. In the Style list, select the error style you want: Stop, Warning, or Information. Use the Title and Error Message boxes to specify the message that appears.

   Caution

   Only the Stop style can prevent the user from ignoring the error and entering the invalid data anyway.

10. Click OK to apply the data-validation rule.

One of Excel’s slickest features is that it enables you to extend this idea and place not only lists but also other dialog box controls, such as spinners and check boxes, directly on a worksheet. You can then link the values returned by these controls to a cell to create an elegant method for entering data.

1. Right-click any part of the ribbon and then select Customize The Ribbon. The Excel Options dialog box appears, with the Customize Ribbon tab displayed.

2. In the Customize The Ribbon list, select the Developer check box.

3. Select OK.

1. Select Developer > Insert and then select the form control you want to create. The mouse pointer changes to a crosshair.

2. Move the pointer onto the worksheet at the location where you want the control to appear.

3. Drag to create the control.

Excel assigns a default caption to each group box, check box, and option button. To edit this caption, you have two ways to get started:

• Right-click the control and select Edit Text.

• Hold down Ctrl and click the control to select it. Then select inside the control.

Edit the text accordingly; when you’re done, select outside the control. To reselect a control, hold down Ctrl and click the control.

1. Select the control you want to work with. (Again, remember to hold down the Ctrl key before you select the control.)

2. Right-click the control and then select Format Control (or select Ctrl+1) to display the Format Control dialog box.

3. Select the Control tab and then use the Cell Link box to enter the cell’s reference.

4. Select OK to return to the worksheet.

1. Select Developer > Insert > Group Box in the Form Controls list.

2. Drag to draw the group box on the worksheet.

3. Select Developer > Insert > Option Button in the Form Controls list.

4. Drag within the group box to create an option button.

5. Repeat steps 3 and 4 as many times as needed to create the other option buttons.

Remember, it’s important that you create the group box first and then draw option buttons within the group box.

By default, Excel draws each option button in the unselected state. Therefore, you should specify in advance which of the option buttons is selected by default:

1. Hold down Ctrl and select the option button you want to display as selected.

2. Right-click the control and then select Format Control (or press Ctrl+1) to display the Format Control dialog box.

3. In the Control tab, select the Checked option.

4. Select OK.

On the worksheet, selecting an option button changes the value stored in the linked cell. The value stored depends on the option button, where the first button added to the group box has the value 1, the second button has the value 2, and so on. The advantage of this is that it enables you to translate a text option into a numeric value. For example, Figure 2-16 shows a worksheet in which the option buttons give the user three freight choices: Surface Mail, Air Mail, and Courier. The value of the chosen option is stored in the linked cell, which is E4. For example, if Air Mail is selected, the value 2 is stored in E4. In a production model, for example, the worksheet would use this value to look up the corresponding freight charges and adjust an invoice accordingly. (To learn how to look up values in a worksheet, see Chapter 7, “Working with lookup functions.”)

On the worksheet, a selected check box stores the value TRUE in its linked cell; if the check box is deselected, it stores the value FALSE (see Figure 2-17). This is handy because it enables you to add a bit of logic to your formulas. That is, you can test whether a check box is selected and adjust a formula accordingly. Figure 2-17 shows a couple of examples:

• Use End-Of-Period Payments: This check box could be used to specify whether a formula that determines the monthly payments on a loan assumes that those payments are made at the end of each period (TRUE) or at the beginning of each period (FALSE).

• Include Extra Monthly Payments: This check box could be used to determine whether a model that builds a loan amortization schedule formula includes an extra principal repayment each month.

In both cases, and in most formulas that take into account check box results, you would use the IF() worksheet function to read the current value of the linked cell and branch accordingly. To learn how to use the IF() worksheet function, see Chapter 6, “Working with logical and information functions.”

List boxes and combo boxes are different from other controls because you also have to specify a range that contains the items to appear in the list. The following steps show you how it’s done:

1. Enter the list items in a range. (The items must be listed in a single row or a single column.)

2. Add the list box control to the sheet (if you haven’t done so already), and then select it.

3. Right-click the control and then select Format Control (or press Ctrl+1) to display the Format Control dialog box.

4. Select the Control tab and then use the Input Range box to enter a reference to the range of items.

5. Select OK to return to the worksheet.

Figure 2-18 shows a worksheet with a list box and a drop-down list.

The list used by both controls in this example is in the range A3:A10. Notice that the linked cells display the number of the list selection, not the selection itself. To get the selected list item, you can use the INDEX() function with the following syntax:

Click here to view code image

INDEX(list_range, list_selection)

For example, to find the item that’s currently selected in the combo box in Figure 2-18, you use the following formula (as shown in cell E12):

=INDEX(A3:A10,E10)

To learn more about the INDEX() function, see Chapter 7.

In the Format Control dialog box for a scroll bar, the Control tab includes the following options:

• Current Value: The initial value of the scroll bar.

• Minimum Value: The value of the scroll bar when the scroll box is at its leftmost position (for a horizontal scroll bar) or its topmost position (for a vertical scroll bar).

• Maximum Value: The value of the scroll bar when the scroll box is at its rightmost position (for a horizontal scroll bar) or its bottommost position (for a vertical scroll bar).

• Incremental Change: The amount that the scroll bar’s value changes when the user selects a scroll arrow.

• Page Change: The amount that the scroll bar’s value changes when the user selects between the scroll box and a scroll arrow.

The Spin Box tool creates a control that is similar to a scroll bar; that is, you can use a spin box to select a number between a maximum value and a minimum value by selecting the arrows. The number is returned to the linked cell. Spin box options are identical to those of scroll bars, except that you can’t set a Page Change value.

Figure 2-19 shows an example of a scroll bar and an example of a spin box. Note that the numbers above the scroll bar giving the minimum and maximum values are extra labels I added by hand. Doing this is usually a good idea because it gives the user the numeric limits of the control.