Chapter 2: Formula Tricks and Techniques

In This Chapter

• Getting an overview of Excel formulas

• Differentiating between absolute and relative references in formulas

• Understanding and using names

• Introducing array formulas

• Counting and summing cells

• Working with dates and times

• Creating megaformulas

About Formulas

Virtually every successful spreadsheet application uses formulas. In fact, constructing formulas can certainly be construed as a type of programming. This chapter covers some of the common (and not so common) types of Excel formulas.

For a much more comprehensive treatment of Excel formulas and functions, refer to my Excel 2013 Formulas (Wiley).

Formulas, of course, are what make a spreadsheet a spreadsheet. If it weren't for formulas, your worksheet would be just a static document — something that a word processor that has great support for tables could produce.

Excel has a huge assortment of built-in functions, has excellent support for names, and even supports array formulas (a special type of formula that can perform otherwise impossible calculations).

A formula entered into a cell can consist of any of the following elements:

• Operators such as + (for addition) and * (for multiplication)

• Cell references (including named cells and ranges)

• Numbers or text strings

• Worksheet functions (such as SUM or AVERAGE)

A formula can consist of up to 8,192 characters. After you enter a formula into a cell, the cell displays the result of the formula. The formula itself appears in the formula bar when the cell is activated. For a better view of a lengthy formula, click and drag the border of the formula bar to expand it vertically. Or click the arrow on the right side of the formula bar.

Calculating Formulas

You've probably noticed that the formulas in your worksheet get calculated immediately. If you change a cell that a formula uses, the formula displays a new result with no effort on your part. This is what happens when the Excel calculation mode is set to Automatic. In this mode (which is the default mode), Excel uses the following rules when calculating your worksheet:

• When you make a change — enter or edit data or formulas, for example — Excel immediately calculates those formulas that depend on the new or edited data.

• If Excel is in the middle of a lengthy calculation, it temporarily suspends calculation when you need to perform other worksheet tasks; it resumes when you're finished.

• Formulas are evaluated in a natural sequence. In other words, if a formula in cell D12 depends on the result of a formula in cell D11, cell D11 is calculated before D12.

Sometimes, however, you might want to control when Excel calculates formulas. For example, if you create a worksheet with thousands of complex formulas, calculation might slow things down. In such a case, you should set Excel's calculation mode to Manual. Use the Calculation Options control in the Formulas⇒Calculation group.

When you're working in Manual calculation mode, Excel displays Calculate in the status bar when you have any uncalculated formulas. You can press the following shortcut keys to recalculate the formulas:

• F9 calculates the formulas in all open workbooks.

• Shift+F9 calculates the formulas in the active worksheet only. Other worksheets in the same workbook won't be calculated.

• Ctrl+Alt+F9 forces a recalculation of everything in all workbooks. Use it if Excel (for some reason) doesn't seem to be calculating correctly, or if you want to force a recalculation of formulas that use custom functions created with Visual Basic for Applications (VBA).

• Ctrl+Alt+Shift+F9 analyzes all formulas and completely rebuilds (and recalculates) the dependency tree.

Excel's calculation mode isn't specific to a particular workbook. When you change Excel's calculation mode, it affects all open workbooks, not just the active workbook.

Cell and Range References

Most formulas refer to one or more cells. You can make cell references by using the cell or range address or name (if it has one). Cell references come in four styles:

• Relative: The reference is fully relative. When the formula is copied, the cell reference adjusts to its new location. Example: A1.

• Absolute: The reference is fully absolute. When the formula is copied, the cell reference doesn't change. Example: $A$1.

• Row Absolute: The reference is partially absolute. When the formula is copied, the column part adjusts, but the row part doesn't change. Example: A$1.

• Column Absolute: The reference is partially absolute. When the formula is copied, the row part adjusts, but the column part doesn't change. Example: $A1.

By default, all cell and range references are relative. To change a reference, you must manually add the dollar signs. Or, when editing a cell in the formula bar, move the cursor to a cell address and press F4 repeatedly to cycle through all four types of cell referencing.

Why use references that aren't relative?

If you think about it, you'll realize that the only reason why you would ever need to change a reference is if you plan to copy the formula. Figure 2-1 demonstrates why this is so. The formula in cell C3 is

=$B3*C$2

This formula calculates the area for various lengths (listed in column B) and widths (listed in row 2). After the formula is entered, you can then copy it down to C9 and across to column I. Because the formula uses absolute references to row 2 and column B and relative references for other rows and columns, each copied formula produces the correct result. If the formula used only relative references, copying the formula would cause all the references to adjust and thus produce incorrect results.

Figure 2-1: An example of using nonrelative references in a formula.

About R1C1 notation

Normally, Excel uses what's known as A1 notation: Each cell address consists of a column letter and a row number. However, Excel also supports R1C1 notation. In this system, cell A1 is referred to as cell R1C1, cell A2 as R2C1, and so on.

To change to R1C1 notation, access the Formulas tab of the Excel Options dialog box. Place a check mark next to R1C1 Reference Style. After you do so, you'll notice that the column letters all change to numbers. All the cell and range references in your formulas are also adjusted.

Table 2-1 presents some examples of formulas that use standard notation and R1C1 notation. The formula is assumed to be in cell B1 (also known as R1C2).

Table 2-1: Comparing Simple Formulas in Two Notations

Standard	R1C1
=A1+1	=RC[–1]+1
=$A$1+1	=R1C1+1
=$A1+1	=RC1+1
=A$1+1	=R1C[–1]+1
=SUM(A1:A10)	=SUM(RC[–1]:R[9]C[–1])
=SUM($A$1:$A$10)	=SUM(R1C1:R10C1)

If you find R1C1 notation confusing, you're not alone. R1C1 notation isn't too bad when you're dealing with absolute references. But when relative references are involved, the brackets can be confusing.

The numbers in brackets refer to the relative position of the references. For example, R[–5]C[–3] specifies the cell that's five rows above and three columns to the left. On the other hand, R[5]C[3] references the cell that's five rows below and three columns to the right. If the brackets are omitted, the notation specifies the same row or column. For example, R[5]C refers to the cell five rows below in the same column.

You probably won't use R1C1 notation as your standard system. However, if you write VBA code to create worksheet formulas, you might find it easier to create the formulas by using R1C1 notation.

Referencing other sheets or workbooks

When a formula refers to other cells, the references don't need to be on the same sheet as the formula. To refer to a cell in a different worksheet, precede the cell reference with the sheet name followed by an exclamation point. Here's an example of a formula that uses a cell reference in a different worksheet (Sheet2):

=Sheet2!A1+1

You can also create link formulas that refer to a cell in a different workbook. To do so, precede the cell reference with the workbook name (in square brackets), the worksheet name, and an exclamation point. Here's an example:

=[Budget.xlsx]Sheet1!A1

If the workbook name in the reference includes one or more spaces, you must enclose it (and the sheet name) in single quotation marks. For example:

='[Budget For 2013.xlsx]Sheet1'!A1

If the linked workbook is closed, you must add the complete path to the workbook reference. Here's an example:

='C:BudgetingExcel Files[Budget For 2013.xlsx]Sheet1'!A1

Although you can enter link formulas directly, you can also create the reference by using normal pointing methods, but the source file must be open. When you do so, Excel creates absolute cell references. If you plan to copy the formula to other cells, make the references relative.

Working with links can be tricky. For example, if you choose the File⇒Save As command to make a backup copy of the source workbook, you automatically change the link formulas to refer to the new file (not usually what you want to do). Another way to mess up your links is to rename the source workbook when the dependent workbook is not open.

Using Names

One of the most useful features in Excel is its capability to provide meaningful names for various items. For example, you can name cells, ranges, rows, columns, charts, and other objects. You can even name values or formulas that don't appear in cells in your worksheet. (See the “Naming constants” section, later in this chapter.)

Naming cells and ranges

Excel provides several ways to name a cell or range:

• Choose Formulas⇒Defined Names⇒Define Name to display the New Name dialog box.

• Use the Name Manager dialog box (Formulas⇒Defined Names⇒Name Manager or press Ctrl+F3). This method isn't the most efficient because it requires clicking the New button in the Name Manager dialog box, which displays the New Name dialog box.

• Select the cell or range and then type a name in the Name box and press Enter. The Name box is the drop-down control displayed to the left of the formula bar.

• If your worksheet contains text that you'd like to use for names of adjacent cells or ranges, select the text and the cells to be named and choose Formulas⇒Defined Names⇒Create from Selection. In Figure 2-2, for example, B3:E3 is named North, B4:E4 is named South, and so on. Vertically, B3:B6 is named Qtr_1, C3:C6 is named Qtr_2, and so on. Note that Excel changes the names to make them valid. (A hyphen isn't a valid character in a name.)

Figure 2-2: Excel makes it easy to create names that use descriptive text in your worksheet.

Using names is especially important if you write VBA code that uses cell or range references. The reason? VBA does not automatically update its references if you move a cell or range that's referred to in a VBA statement. For example, if your VBA code writes a value to Range(“C4”), the data will be written to the wrong cell if the user inserts a new row above or a new column to the left of cell C4. Using a reference to a named cell, such as Range(“InterestRate”), avoids these potential problems.

Applying names to existing references

When you create a name for a cell or a range, Excel doesn't automatically use the name in place of existing references in your formulas. For example, assume that you have the following formula in cell F10:

=A1–A2

If you define the names Income for A1 and Expenses for A2, Excel doesn't automatically change your formula to

=Income-Expenses

However, replacing cell or range references with their corresponding names is fairly easy. Start by selecting the range that contains the formulas that you want to modify. Then choose Formulas⇒Defined Names⇒Define Name⇒Apply Names. In the Apply Names dialog box, select the names that you want to apply and then click OK. Excel replaces the range references with the names in the selected cells.

Unfortunately, you can't automatically unapply names. In other words, if a formula uses a name, you can't convert the name to an actual cell or range reference. Even worse, if you delete a name that a formula uses, the formula doesn't revert to the cell or range address — it simply returns a #NAME? error.

My Power Utility Pak add-in (available by using the coupon in the back of the book) includes a utility that scans all formulas in a selection and automatically replaces names with their cell addresses.

Intersecting names

Excel has a special operator called the intersection operator that comes into play when you're dealing with ranges. This operator is a space character. Using names with the intersection operator makes creating meaningful formulas very easy. For this example, refer to Figure 2-2. If you enter the following formula into a cell

=Qtr_2 South

the result is 9,186 — the value at the intersection of the Qtr_2 range and the South range.

Naming columns and rows

Excel lets you name complete rows and columns. In Figure 2-2, the name Qtr_1 is assigned to the range B3:B6. Alternatively, Qtr_1 could be assigned to all of column B, Qtr_2 to column C, and so on. You also can do the same horizontally so that North refers to row 3, South to row 4, and so on.

The intersection operator works exactly as before, but now you can add more regions or quarters without having to change the existing names.

When naming columns and rows, make sure that you don't store any extraneous information in named rows or columns. For example, remember that if you insert a value in cell C7, it is included in the Qtr_1 range.

Scoping names

A named cell or range normally has a workbook-level scope. In other words, you can use the name in any worksheet in the workbook.

Another option is to create names that have a worksheet-level scope. To create a worksheet-level name, define the name by preceding it with the worksheet name followed by an exclamation point: for example, Sheet1!Sales. If the name is used on the sheet in which it is designed, you can omit the sheet qualifier when you reference the name. You can, however, reference a worksheet-level name on a different sheet if you precede the name with the sheet qualifier.

The Name Manager dialog box (Formulas⇒Defined Names⇒Name Manager) makes identifying names by their scope easy (see Figure 2-3). Note that the dialog box is resizable, and you can adjust the column widths. You can also sort and filter the information in this dialog box.

Figure 2-3: The Name Manager displays the scope for each defined name.

Naming constants

Virtually every experienced Excel user knows how to create cell and range names (although not all Excel users actually do so). But most Excel users don't know that you can use names to refer to values that don't appear in your worksheet — that is, constants.

Suppose that many formulas in your worksheet need to use a particular interest rate value. One approach is to type the interest rate into a cell and give that cell a name, such as InterestRate. After doing so, you can use that name in your formulas, like this:

=InterestRate*A3

An alternative is to call up the New Name dialog box (Formulas⇒Defined Names⇒Define Name) and enter the interest rate directly into the Refers To box (see Figure 2-4). Then you can use the name in your formulas just as if the value were stored in a cell. If the interest rate changes, just change the definition for InterestRate, and Excel updates all the cells that contain this name.

Figure 2-4: Excel lets you name constants that don't appear in worksheet cells.

This technique also works for text. For example, you can define the name IWC to stand for International Widget Corporation. Then you can enter =IWC into a cell, and the cell displays the full name.

Naming formulas

In addition to naming cells, ranges, and constants, you can also create named formulas. A named formula, as described here, exists only in memory — it does not exist in a cell. To create a named formula, enter a formula directly in the Refers To field in the New Name dialog box.

This point is very important: The formula that you enter uses cell references relative to the active cell at the time that you create the named formula.

Figure 2-5 shows a formula (=A1^B1) entered directly in the Refers To box in the New Name dialog box. In this case, the active cell is C1, so the formula refers to the two cells to its left. (Notice that the cell references are relative.) After this name is defined, entering =Power in a cell raises the value two cells to the left to the power represented by the cell directly to the left. For example, if B10 contains 3 and C10 contains 4, entering the following formula in cell D10 returns a value of 81 (3 to the 4th power).

=Power

Figure 2-5: You can name a formula that doesn't appear in any worksheet cell.

When you display Name Manager after creating the named formula, the Refers To column displays a formula that is relative to the current active cell. For example, if cell D32 is the active cell, the Refers To column displays

=Sheet1!B32^Sheet1!C32

Note that Excel qualifies the cell references by adding the worksheet name to the cell references used in your formula. The worksheet name is the sheet that was active when you created the name formula. This, of course, will cause the named formula to produce incorrect results if you use it in a worksheet other than the one in which it was defined. If you'd like to use this named formula in a sheet other than Sheet1, you need to remove the sheet references from the formula (but keep the exclamation points). For example:

=!A1^!B1

After you understand the concept, you might discover some new uses for named formulas. One distinct advantage is apparent if you need to modify the formula. You can just change the formula one time rather than edit each occurrence of the formula.

This book's website contains a workbook with several examples of named formulas. The workbook is called named formulas.xlsx.

When you're working in the New Name dialog box, the Refers To field is normally in point mode, which makes it easy to enter a range reference by clicking in the worksheet. Press F2 to toggle between point mode and normal editing mode, which allows you to use the arrow keys to edit the formula.

Naming objects

In addition to providing names for cells and ranges, you can give more meaningful names to objects such as pivot tables and shapes. Using meaningful names can make referring to such objects easier, especially when you refer to them in your VBA code.

To change the name of a nonrange object, use the Name box, which is located to the left of the formula bar. Just select the object, type the new name in the Name box, and then press Enter.

If you simply click elsewhere in your workbook after typing the name in the Name box, the name won't stick. You must press Enter.

Object names are different than name ranges and are not included in the Name Manager dialog box.

Formula Errors

Entering a formula and receiving an error in return isn't uncommon. One possibility is that the formula you entered is the cause of the error. Another possibility is that the formula refers to a cell that has an error value. The latter scenario is known as the ripple effect — a single error value can make its way to lots of other cells that contain formulas that depend on the cell. The tools in the Formulas⇒Formula Auditing group can help you trace the source of formula errors.

Table 2-2 lists the types of error values that may appear in a cell that has a formula.

Table 2-2: Excel Formula Error Values

Error Value	Explanation
#DIV/0!	The formula is trying to divide by 0 (zero), an operation that's not allowed on this planet. This error also occurs when the formula attempts to divide by a cell that is empty.
#N/A	The formula is referring (directly or indirectly) to a cell that uses the NA worksheet function to signal the fact that data isn't available. A lookup function that can't locate a value also returns #N/A.
#NAME?	The formula uses a name that Excel doesn't recognize. This can happen if you delete a name that's used in the formula, if you have unmatched quotes when using text, if you omit parentheses for a function that uses no arguments, or if you misspell a function or range name. A formula will also display this error if it uses a function defined in an add-in and that add-in isn't installed.
#NULL!	The formula uses an intersection of two ranges that don't intersect. (This concept is described in the section “Intersecting names,” earlier in the chapter.)
#NUM!	A function argument has a problem; for example, the SQRT function is attempting to calculate the square root of a negative number. This error also appears if a calculated value is too large or too small. Excel doesn't support nonzero values less than 1E–307 or greater than 1E+308 in absolute value.
#REF!	The formula refers to a cell that isn't valid. This can happen if a cell used in a formula has been deleted from the worksheet.
#VALUE!	The formula includes an argument or operand of the wrong type. An operand is a value or cell reference that a formula uses to calculate a result. This error also occurs if your formula uses a custom VBA worksheet function that contains an error.
#####	A cell displays a series of hash marks under two conditions: The column isn't wide enough to display the result, or the formula returns a negative date or time value.

Excel Auditing Tools

Excel includes a number of tools that can help you track down formula errors. This section describes the auditing tools built in to Excel.

Identifying cells of a particular type

The Go to Special dialog box (shown in Figure 2-6) is a handy tool that enables you to locate cells of a particular type. To display this dialog box, choose Home⇒Editing ⇒Find & Select⇒Go to Special.

If you select a multicell range before displaying the Go to Special dialog box, the command operates only within the selected cells. If a single cell is selected, the command operates on the entire worksheet.

Figure 2-6: The Go to Special dialog box.

You can use the Go to Special dialog box to select cells of a certain type, which can often help you identify errors. For example, if you choose the Formulas option, Excel selects all the cells that contain a formula. If you zoom the worksheet out to a small size, you can get a good idea of the worksheet's organization (see Figure 2-7). To zoom a worksheet, use the zoom controls on the right side of the status bar or press Ctrl while you move the scroll wheel on your mouse.

Figure 2-7: Zooming out and selecting all formula cells can give you a good overview of the worksheet's design.

Selecting the formula cells may also help you spot a common error: namely, a formula that has been replaced accidentally with a value. If you find a cell that's not selected amid a group of selected formula cells, chances are good that the cell previously contained a formula that has been replaced by a value.

Viewing formulas

You can become familiar with an unfamiliar workbook by displaying the formulas rather than the results of the formulas. To toggle the display of formulas, choose Formulas⇒Formula Auditing⇒Show Formulas. You may want to create a second window for the workbook before issuing this command. This way, you can see the formulas in one window and the results of the formula in the other window. Choose View⇒Window⇒New Window to open a new window.

You can also press Ctrl+` (the accent grave key, typically located above the Tab key) to toggle between Formula view and Normal view.

Figure 2-8 shows an example of a worksheet displayed in two windows. The window on the top shows Normal view (formula results), and the window on the bottom displays the formulas. Choosing View⇒Window⇒View Side by Side, which allows synchronized scrolling, is also useful for viewing two windows.

Figure 2-8: Displaying formulas (bottom window) and their results (top window).

Tracing cell relationships

To understand how to trace cell relationships, you need to familiarize yourself with the following two concepts:

• Cell precedents: Applicable only to cells that contain a formula, a formula cell's precedents are all the cells that contribute to the formula's result. A direct precedent is a cell that you use directly in the formula. An indirect precedent is a cell that isn't used directly in the formula but is used by a cell that you refer to in the formula.

• Cell dependents: These formula cells depend on a particular cell. A cell's dependents consist of all formula cells that use the cell. Again, the formula cell can be a direct dependent or an indirect dependent.

For example, consider this simple formula entered into cell A4:

=SUM(A1:A3)

Cell A4 has three precedent cells (A1, A2, and A3), which are all direct precedents. Cells A1, A2, and A3 each have at least one dependent cell (cell A4), and they're all direct dependents.

Identifying cell precedents for a formula cell often sheds light on why the formula isn't working correctly. Conversely, knowing which formula cells depend on a particular cell is also helpful. For example, if you're about to delete a formula, you may want to check whether it has any dependents.

Identifying precedents

You can identify cells used by a formula in the active cell in a number of ways:

• Press F2. The cells that are used directly by the formula are outlined in color, and the color corresponds to the cell reference in the formula. This technique is limited to identifying cells in the same sheet as the formula.

• Display the Go to Special dialog box. (Choose Home⇒Editing⇒Find & Select⇒Go to Special.) Select the Precedents option and then select either Direct Only (for direct precedents only) or All Levels (for direct and indirect precedents). Click OK, and Excel selects the precedent cells for the formula. This technique is limited to identifying cells in the same sheet as the formula.

• Press Ctrl+[. This keystroke selects all direct precedent cells on the active sheet.

• Press Ctrl+Shift+{. This keystroke selects all precedent cells (direct and indirect) on the active sheet.

• Choose Formulas⇒Formula Auditing⇒Trace Precedents. Excel will draw arrows to indicate the cell's precedents. Click the Trace Precedents button multiple times to see additional levels of precedents. Choose Formulas⇒Formula Auditing⇒Remove Arrows to hide the arrows. Figure 2-9 shows a worksheet with precedent arrows drawn to indicate the precedents for the formula in cell C13.

Figure 2-9: This worksheet displays arrows that indicate cell precedents for the formula in cell C13.

Identifying dependents

You can identify formula cells that use a particular cell in a number of ways:

• Display the Go to Special dialog box. Select the Dependents option and then select either Direct Only (for direct dependents only) or All Levels (for direct and indirect dependents). Click OK. Excel selects the cells that depend on the active cell. This technique is limited to identifying cells in the active sheet only.

• Press Ctrl+]. This keystroke selects all direct dependent cells on the active sheet.

• Press Ctrl+Shift+}. This keystroke selects all dependent cells (direct and indirect) on the active sheet.

• Choose Formulas⇒Formula Auditing⇒Trace Dependents. Excel will draw arrows to indicate the cell's dependents. Click the Trace Dependents button multiple times to see additional levels of dependents. Choose Formulas⇒Formula Auditing⇒Remove Arrows to hide the arrows.

Tracing error values

If a formula displays an error value, Excel can help you identify the cell that is causing that error value. An error in one cell is often the result of an error in a precedent cell. Activate a cell that contains an error value and then choose Formulas⇒Formula Auditing⇒Error Checking⇒Trace Error. Excel draws arrows to indicate the error source.

Fixing circular reference errors

If you accidentally create a circular reference formula, Excel displays a warning message — Circular Reference — with the cell address, in the status bar, and also draws arrows on the worksheet to help you identify the problem. If you can't figure out the source of the problem, choose Formulas⇒Formula Auditing⇒Error Checking⇒Circular References. This command displays a list of all cells that are involved in the circular references. Start by selecting the first cell listed and then work your way down the list until you figure out the problem.

Using the background error-checking feature

Some people may find it helpful to take advantage of the Excel automatic error-checking feature. This feature is enabled or disabled by using the Enable Background Error Checking check box, found on the Formulas tab of the Excel Options dialog box (shown in Figure 2-10). In addition, you can use the check boxes in the Error Checking Rules section to specify which types of errors to check.

Figure 2-10: Excel can check your formulas for potential errors.

When error checking is turned on, Excel continually evaluates the formulas in your worksheet. If a potential error is identified, Excel places a small triangle in the upper-left corner of the cell. When the cell is activated, a drop-down control appears. Clicking this control provides you with options that vary depending on the type of error. Figure 2-11, for example, shows the options that appear when you click the control in a cell that contains a #DIV/0! error.

Figure 2-11: When you select a cell that contains an error (or a potential error), a drop-down control gives you a list of options.

In many cases, you will choose to ignore an error by selecting the Ignore Error option. Selecting this option eliminates the cell from subsequent error checks. However, all previously ignored errors can be reset so that they appear again. (Use the Reset Ignored Errors button in the Formulas tab of the Excel Options dialog box.)

You can choose Formulas⇒Formula Auditing⇒Error Checking to display a dialog box that describes each potential error cell in sequence, much like using a spell-checking command. This command is available even if you disable background error checking. Figure 2-12 shows the Error Checking dialog box. This dialog box is modeless: that is, you can still access your worksheet when the Error Checking dialog box is displayed.

The error-checking feature isn't perfect. In fact, it's not even close to perfect. In other words, you can't assume that you have an error-free worksheet simply because Excel doesn't identify any potential errors! Also, be aware that the error-checking feature won't catch the common error of overwriting a formula cell with a value.

Figure 2-12: Use the Error Checking dialog box to cycle through potential errors identified by Excel.

Using Excel's Formula Evaluator

Formula Evaluator lets you see the various parts of a nested formula evaluated in the order that the formula is calculated. To use Formula Evaluator, select the cell that contains the formula and then choose Formula⇒Formula Auditing⇒Evaluate Formula to display the Evaluate Formula dialog box (see Figure 2-13).

Figure 2-13: The Evaluate Formula dialog box shows a formula being calculated one step at a time.

Click the Evaluate button to show the result of calculating the expressions within the formula. Each button click performs another calculation. This feature may seem a bit complicated at first, but you'll understand how it works and see its value if you spend some time working with it.

Excel provides another way to evaluate a part of a formula:

1. Select the cell that contains the formula.

2. Press F2 to switch to cell edit mode.

3. Highlight the portion of the formula you want to evaluate.

To highlight, use your mouse or press Shift and use the navigation keys.

4. Press F9.

The highlighted portion of the formula displays the calculated result. You can evaluate other parts of the formula or press Esc to cancel and return your formula to its previous state.

Be careful when using this technique because if you press Enter (rather than Esc), the formula will be modified to use the calculated values.

Array Formulas

In Excel terminology, an array is a collection of cells or values that is operated on as a group. An array formula is a special type of formula that works with arrays. An array formula can produce a single result, or it can produce multiple results — with each result displayed in a separate cell.

For example, when you multiply a 1 x 5 array by another 1 x 5 array, the result is a third 1 x 5 array. In other words, the result of this kind of operation occupies five cells; each element in the first array is multiplied by each corresponding element in the second array to create five new values, each getting its own cell. The array formula that follows multiplies the values in A1:A5 by the corresponding values in B1:B5. This array formula is entered into five cells simultaneously:

{=A1:A5*B1:B5}

You enter an array formula by pressing Ctrl+Shift+Enter (not just Enter). To remind you that a formula is an array formula, Excel surrounds it with curly braces in the formula bar. When I present an array formula in this book, I enclose it in curly braces to distinguish it from a normal formula. Don't enter the braces yourself.

An array formula example

An array formula enables you to perform individual operations on each cell in a range in much the same way that a programming language's looping feature enables you to work with elements of an array. If you've never used array formulas before, this section will get your feet wet with a hands-on example.

Figure 2-14 shows a worksheet with text in A1:A5. The goal of this exercise is to create a single formula that returns the sum of the total number of characters in the range. Without the single formula requirement, you'd write a formula with the LEN function, copy it down the column, and then use the SUM function to add the results of the intermediate formulas.

Figure 2-14: Cell B1 contains an array formula that returns the total number of characters contained in range A1:A5. Note the brackets in the formula bar.

To demonstrate how an array formula can occupy more than one cell, create the worksheet shown in the figure and then try these steps:

1. Select the range B1:B5.

2. Type the following formula:

=LEN(A1:A5)

3. Press Ctrl+Shift+Enter.

The preceding steps enter a single array formula into five cells. Enter a SUM formula that adds the values in B1:B5, and you'll see that the total number of characters in A1:A5 is 29.

Here's the key point: It's not necessary to actually display those five array elements. Rather, Excel can store the array in memory. Knowing this, you can type the following single array formula in any blank cell (remember: don't type the curly brackets and make sure that you enter the array formula by pressing Ctrl+Shift+Enter):

{=SUM(LEN(A1:A5))}

This formula essentially creates a five-element array (in memory) that consists of the length of each string in A1:A5. The SUM function uses this array as its argument, and the formula returns 29.

An array formula calendar

Figure 2-15 shows a worksheet set up to display a calendar for any month. (Change the month, and the calendar is updated.) Believe it or not, the calendar is created with a single array formula that occupies 42 cells.

Figure 2-15: A single multicell array formula is all it takes to make a calendar for any month in any year.

The array formula, entered in the range B5:H10, is

{=IF(MONTH(DATE(YEAR(B3),MONTH(B3),1))<>MONTH(DATE(YEAR(B3),

MONTH(B3),1)-(WEEKDAY(DATE(YEAR(B3),MONTH(B3),1))-1)

+{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1),””,

DATE(YEAR(B3),MONTH(B3),1)-(WEEKDAY(DATE(YEAR(B3),

MONTH(B3),1))-1)+{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1)}

The formula returns date serial numbers, and you need to format the cells to display the day number only by using a custom number format (“d”).

The book's website contains a workbook with the calendar example, as well as several additional array formula examples. The file is named array formula examples.xlsx. In addition, you'll find a workbook named yearly calendar.xlsx that uses array formulas to display a calendar for a complete year.

Array formula pros and cons

The advantages of using array formulas rather than single-cell formulas include the following:

• They can sometimes use less memory.

• They can make your work much more efficient.

• They can eliminate the need for intermediate formulas.

• They can enable you to do things that would be difficult or impossible otherwise.

A few disadvantages of using array formulas are the following:

• Using many complex array formulas can sometimes slow your spreadsheet recalculation time.

• They can make your worksheet more difficult for others to understand.

• You must remember to enter an array formula with a special key sequence (by pressing Ctrl+Shift+Enter).

Counting and Summing Techniques

A common task in Excel is conditional counting or summing. This section contains a number of formula examples that deal with counting various items on a worksheet, based on single or multiple criteria. You can adapt these formulas to your own needs.

Excel 2007 introduced two new counting and summing functions that aren't available in previous versions (COUNTIFS and SUMIFS). Therefore, I present two versions of some formulas: an Excel 2007 and later version and an array formula that works with all versions of Excel.

Figure 2-16 shows a simple worksheet to demonstrate the formulas that follow. The following range names are defined:

• Month: A2:A10

• Region: B2:B10

• Sales: C2:C10

Figure 2-16: This worksheet demonstrates some useful formulas for counting and summing.

This workbook (including the formula examples) is available on this book's website. The file is named counting and summing examples.xlsx.

Counting formula examples

Table 2-3 contains formulas that demonstrate a variety of counting techniques.

Table 2-3: Counting Formula Examples

Formula	Description
=COUNTIF(Region,”North”)	Counts the number of rows in which Region = “North”
=COUNTIF(Sales,300)	Counts the number of rows in which Sales = 300
=COUNTIF(Sales,”>300”)	Counts the number of rows in which Sales > 300
=COUNTIF(Sales,”<>100”)	Counts the number of rows in which Sales <> 100
=COUNTIF(Region,”?????”)	Counts the number of rows in which Region contains five letters
=COUNTIF(Region,”*h*”)	Counts the number of rows in which Region contains the letter H (not case-sensitive)
=COUNTIFS(Month,”Jan”,Sales,”>200”)	Counts the number of rows in which Month = “Jan” and Sales > 200 (Excel 2007 and later)
{=SUM((Month=”Jan”)*(Sales>200))}	An array formula that counts the number of rows in which Month = “Jan” and Sales > 200
=COUNTIFS(Month,”Jan”,Region,”North”)	Counts the number of rows in which Month = “Jan” and Region = “North” (Excel 2007 and later)
{=SUM((Month=”Jan”)*(Region=”North”))}	An array formula that counts the number of rows in which Month = “Jan” and Region = “North”
=COUNTIFS(Month,”Jan”,Region,”North”)+COUNTIFS(Month,”Jan”,Region,”South”)	Counts the number of rows in which Month = “Jan” and Region = “North” or “South” (Excel 2007 and later)
{=SUM((Month=”Jan”)*((Region=”North”)+(Region=”South”)))}	An array formula that counts the number of rows in which Month = “Jan” and Region = “North” or “South”
=COUNTIFS(Sales,”>=300”,Sales,”<=400”)	Counts the number of rows in which Sales is between 300 and 400 (Excel 2007 and later)
{=SUM((Sales>=300)*(Sales<=400))}	An array formula that counts the number of rows in which Sales is between 300 and 400

Summing formula examples

Table 2-4 shows a number of formula examples that demonstrate a variety of summing techniques.

Table 2-4: Summing Formula Examples

Formula	Description
=SUMIF(Sales,”>200”)	Sum of all Sales over 200
=SUMIF(Month,”Jan”,Sales)	Sum of Sales in which Month = “Jan”
=SUMIF(Month,”Jan”,Sales)+SUMIF(Month,”Feb”,Sales)	Sum of Sales in which Month = “Jan” or “Feb”
{=SUM((Month=”Jan”)*(Region=”North”)*Sales)}	Sum of Sales in which Month = “Jan” and Region = “North”
=SUMIFS(Sales,Month,”Jan”,Region,”North”)	Sum of Sales in which Month = “Jan” and Region = “North” (Excel 2007 and later)
{=SUM((Month=”Jan”)*(Region=”North”)*Sales)}	An array formula that returns the sum of Sales in which Month = “Jan” and Region = “North”
=SUMIFS(Sales,Month,”Jan”,Region,”<>North”)	Sum of Sales in which Month = “Jan” and Region <> “North” (Excel 2007 and later)
{=SUM((Month=”Jan”)*(Region<>”North”)*Sales)}	An array formula that returns the sum of Sales in which Month = “Jan” and Region <> “North”
=SUMIFS(Sales,Month,”Jan”,Sales,”>=200”)	Sum of Sales in which Month = “Jan” and Sales >= 200 (Excel 2007 and later)
{=SUM((Month=”Jan”)*(Sales>=200)*(Sales))}	An array formula that returns the sum of Sales in which Month = “Jan” and Sales >= 200
=SUMIFS(Sales,Sales,”>=300”,Sales,”<=400”)	Sum of Sales between 300 and 400 (Excel 2007 and later)
{=SUM((Sales>=300)*(Sales<=400)*(Sales))}	An array formula that returns the sum of Sales between 300 and 400

Other counting tools

Other ways to count or sum cells that meet certain criteria are

• Filtering (using a table)

• Advanced filtering

• The DCOUNT and DSUM functions

• Pivot tables

For more information, consult the Help system.

Lookup Formulas

A common type of Excel formula looks up a value in a table and returns a corresponding value. A common telephone directory (remember those?) provides a good analogy. If you want to find a person's telephone number, you first locate the name (look it up) and then retrieve the corresponding number.

Several Excel functions are useful when writing formulas to look up information in a table. Table 2-5 lists and describes these functions.

Table 2-5: Functions Used in Lookup Formulas

Function	Description
CHOOSE	Returns a specific value from a list of values supplied as arguments.
HLOOKUP	Horizontal lookup. Searches for a value in the top row of a table and returns a value in the same column from a row you specify in the table.
IF	Returns one value if a condition you specify is TRUE, and returns another value if the condition is FALSE.
IFERROR*	If the first argument returns an error, the second argument is evaluated and returned. If the first argument does not return an error, it is evaluated and returned.
INDEX	Returns a value (or the reference to a value) from a table or range.
LOOKUP	Returns a value either from a one-row or one-column range. Another form of the LOOKUP function works like VLOOKUP but is restricted to returning a value from the last column of a range.
MATCH	Returns the relative position of an item in a range that matches a specified value.
OFFSET	Returns a reference to a range that is a specified number of rows and columns from a cell or range of cells.
VLOOKUP	Vertical lookup. Searches for a value in the first column of a table and returns a value in the same row from a column you specify in the table.

You can use the Excel basic lookup functions to search a column or row for a lookup value to return another value as a result. Excel provides three basic lookup functions: HLOOKUP, VLOOKUP, and LOOKUP. In addition, the MATCH and INDEX functions are often used together to return a cell or relative cell reference for a lookup value.

The VLOOKUP function looks up the value in the first column of the lookup table and returns the corresponding value in a specified table column. The lookup table is arranged vertically (which explains the V in the function's name). The syntax for the VLOOKUP function is

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

The VLOOKUP function's arguments are as follows:

• lookup_value: Required. The value to be looked up in the first column of the lookup table.

• table_array: Required. The range that contains the lookup table.

• col_index_num: Required. The column number within the table from which the matching value is returned.

• range_lookup: Optional. If TRUE or omitted, an approximate match is returned. (If an exact match is not found, the next largest value that is less than lookup_value is returned.) If FALSE, VLOOKUP will search for an exact match. If VLOOKUP can't find an exact match, the function returns #N/A.

If the range_lookup argument is TRUE or omitted, the first column of the lookup table must be in ascending order. If lookup_value is smaller than the smallest value in the first column of table_array, VLOOKUP returns #N/A. If the range_lookup argument is FALSE, the first column of the lookup table need not be in ascending order. If an exact match is not found, the function returns #N/A.

A common use for a lookup formula involves an income tax rate schedule (see Figure 2-17). The tax rate schedule shows the income tax rates for various income levels. The following formula (in cell B3) returns the tax rate for the income in cell B2:

=VLOOKUP(B2,D2:F7,3)

Figure 2-17: Using the VLOOKUP function to look up a tax rate.

The lookup table resides in a range that consists of three columns (D2:F7). Because the last argument for the VLOOKUP function is 3, the formula returns the corresponding value in the third column of the lookup table.

Note that an exact match is not required. If an exact match is not found in the first column of the lookup table, the VLOOKUP function uses the next largest value that is less than the lookup value. In other words, the function uses the row in which the value you want to look up is greater than or equal to the row value but less than the value in the next row. In the case of a tax table, this is exactly what you want to happen.

The example in this section is available on this book's website in a file named basic lookup examples.xlsx. The website contains another workbook with additional examples, specialized lookup examples.xlsx.

Working with Dates and Times

Excel uses a serial number system to store dates. The earliest date that Excel can understand is January 1, 1900. This date has a serial number of 1. January 2, 1900, has a serial number of 2, and so on.

Most of the time, you don't have to be concerned with Excel's serial number date system. You simply enter a date in a familiar date format, and Excel takes care of the details behind the scenes. For example, if you need to enter August 15, 2013, you can simply enter the date by typing August 15, 2013 (or use any of a number of different date formats). Excel interprets your entry and stores the value 41501, which is the serial number for that date.

In this chapter, I assume the U.S. date system. If your computer uses a different date system, you'll need to adjust accordingly. For example, you might need to enter 15 August 2013.

Entering dates and times

When working with times, you simply enter the time in a cell in a recognized format. Excel's system for representing dates as individual values is extended to include decimals that represent portions or fractions of days. In other words, Excel perceives all time with the same system whether that time is a particular day, a certain hour, or a specific second. For example, the date serial number for August 15, 2013, is 41501. Noon (halfway through the day) is represented internally as 41501.5. Again, you normally don't have to be concerned with these fractional serial numbers.

Because dates and times are stored as serial numbers, it stands to reason that you can add and subtract dates and times. For example, you can enter a formula to calculate the number of days between two dates. If cells A1 and A2 both contain dates, the following formula returns the number of intervening days:

=A2-A1

When performing calculations with time, things get a bit trickier. When you enter a time without an associated date, the date is assumed to be January 0, 1900 (date serial number 0). This is not a problem — unless your calculation produces a negative time value. When this happens, Excel displays an error (displayed as #########). The solution? Switch to the 1904 date system. Display the Excel Options dialog box, click the Advanced tab, and then enable the Use 1904 Date System check box. Be aware that switching to the 1904 date system can cause problems with dates already entered in your file or dates in workbooks that are linked to your file.

In some cases, you may need to use time values to represent duration, rather than a point in time. For example, you may need to sum the number of hours worked in a week. When you add time values, you can't display more than 24 hours. For each 24-hour period, Excel simply adds another day to the total. The solution is to change the number formatting to use square brackets around the hour part of the format. The following number format, for example, displays more than 24 hours:

[hh]:mm

Using pre-1900 dates

The world, of course, didn't begin on January 1, 1900. People who work with historical information when using Excel often need to work with dates before January 1, 1900. Unfortunately, the only way to work with pre-1900 dates is to enter the date into a cell as text. For example, you can enter the following into a cell, and Excel won't complain:

July 4, 1776

You can't, however, perform any manipulation on dates that are actually text. For example, you can't change its formatting, you can't determine which day of the week this date occurred on, and you can't calculate the date that occurs seven days later.

VBA, however, supports a much wider range of dates. I created a number of VBA worksheet functions that allow you to work with pre-1900 dates. Figure 2-18 shows these functions used in a worksheet. It's also an excellent example of how VBA can extend the features in Excel.

Figure 2-18: The Extended Date Functions add-in lets you work with pre-1900 dates.

See Chapter 8 for more information about the Extended Date functions.

Creating Megaformulas

Often, a formula requires intermediate formulas to produce a desired result. In other words, a formula may depend on other formulas, which in turn depend on other formulas. After you get all these formulas working correctly, you can often eliminate the intermediate formulas and use what I refer to as a single megaformula instead. The advantages? You use fewer cells (less clutter), the file size is smaller, and recalculation may even be a bit faster. The main disadvantage is that the formula may be impossible to decipher or modify.

Here's an example: Imagine a worksheet that has a column with thousands of people's names. And suppose that you've been asked to remove all the middle names and middle initials from the names — but not all the names have a middle name or initial. Editing the cells manually would take hours, and even Excel's Data⇒Data Tools⇒Text To Columns command isn't much help. So you opt for a formula-based solution. Although this task isn't difficult, it normally involves several intermediate formulas.

The Flash Fill feature in Excel 2013 provides another way to accomplish this task.

Figure 2-19 shows the results of the more conventional solution, which requires six intermediate formulas shown in Table 2-6. The names are in column A; the end result goes in column H. Columns B through G hold the intermediate formulas.

Figure 2-19: Removing the middle names and initials requires intermediate formulas.

Table 2-6: Intermediate Formulas Written in Row 2 in Figure 2-10

Column	Intermediate Formula	What It Does
B	=TRIM(A2)	Removes excess spaces.
C	=FIND(“ “,B2,1)	Locates the first space.
D	=FIND(“ “,B2,C2+1)	Locates the second space. Returns #VALUE! if no second space exists.
E	=IF(ISERROR(D2),C2,D2)	Uses the first space if no second space exists.
F	=LEFT(B2,C2)	Extracts the first name.
G	=RIGHT(B2,LEN(B2)-E2)	Extracts the last name.
H	=F2&G2	Concatenates the two names.

You can eliminate the intermediate formulas by creating a megaformula. You do so by creating all the intermediate formulas and then going back into the final result formula and replacing each cell reference with a copy of the formula in the cell referred to (without the equal sign). Fortunately, you can use the Clipboard to copy and paste. Keep repeating this process until cell H2 contains nothing but references to cell A2. You end up with the following megaformula in one cell:

=LEFT(TRIM(A2),FIND

(“ “,TRIM(A2),1))&RIGHT(TRIM(A2),LEN(TRIM(A2))-

IF(ISERROR(FIND(“ “,TRIM(A2),FIND(“ “,TRIM(A2),1)+1)),

FIND(“ “,TRIM(A2),1),FIND(“ “,TRIM(A2),FIND

(“ “,TRIM(A2),1)+1)))

When you're satisfied that the megaformula is working, you can delete the columns that hold the intermediate formulas because they're no longer used.

The megaformula performs exactly the same tasks as all the intermediate formulas — although it's virtually impossible for anyone to figure out, even the author. If you decide to use megaformulas, make sure that the intermediate formulas are performing correctly before you start building a megaformula. Even better, keep a single copy of the intermediate formulas somewhere in case you discover an error or need to make a change.

Another way to approach this problem is to create a custom worksheet function in VBA. Then you could replace the megaformula with a simple formula, such as

=NOMIDDLE(A1)

In fact, I wrote such a function to compare it with intermediate formulas and megaformulas. The listing follows:

Function NOMIDDLE(n) As String

    Dim FirstName As String, LastName As String

    n = Application.WorksheetFunction.Trim(n)

    FirstName = Left(n, InStr(1, n, “ “))

    LastName = Right(n, Len(n) - InStrRev(n, “ “))

    NOMIDDLE = FirstName & LastName

End Function

A workbook that contains the intermediate formulas, the megaformula, and the NOMIDDLE VBA function is available on the book's website. The workbook is named megaformula.xlsm.

Because a megaformula is so complex, you may think that using one slows down recalculation. Actually, that's not the case. As a test, I created a workbook that used the megaformula 175,000 times. Then I created another workbook that used six intermediate formulas to compute the 175,000 results. In the following, you can see the results in terms of calculation time and file size:

• Intermediate formulas: Recalculation time of 5.8 seconds; 12.60MB file size

• Megaformula: Recalculation time of 3.9 seconds; 2.95MB file size

The actual results will vary significantly, depending on system speed, amount of memory installed, and the actual formula.

The VBA function was much slower — I abandoned the timed test after five minutes. Slower performance is fairly typical of VBA functions; they are always slower than built-in Excel functions.