Worksheet formula

This chapter shows you how to use Excel s wide range of worksheet functions to construct sophisticated worksheet formulas. In addition, you ll learn how to use named references in formulas, which makes constructing complicated formulas an easier and more error-free task. You ll also learn techniques of formula editing and worksheet troubleshooting. 

A worksheet formula consists of some or all of the following values, cell references, operators and worksheet functions. 

There are a number of useful techniques that you can use for entering worksheet formulas or editing formulas that you have entered in worksheet cells. 

Instead of a reference, you can type a numeric value or a formula in the Refers To box. You can use named formulas to simplify long worksheet formulas by assigning names to parts of the formula. See "Returning an Array of Unique Entries in a List" in Chapter 4 or "A Drop-down List Box on a Worksheet" in Chapter 8 for examples of this technique. 

Inevitably, your worksheet formulas will at times contain errors. Earlier in this chapter you learned some of the ways to prevent or discover errors while entering formulas. But even if a formula is syntactically correct, it may produce an error when it is evaluated. This section provides some techniques and tips for tracking down errors in your spreadsheets. 

In the same way that a worksheet formula can contain a simple constant, e.g., the value 3 in the formula =3 A1+A2, an array formula can contain an array constant. An array constant is included in a formula by enclosing the array of values in braces. In this case you must type the braces. When you enter the completed array formula by pressing CONTROL+SHIFT+ENTER, Excel automatically provides braces around the whole formula. 

An array constant is usually incorporated in a worksheet formula, e.g.,... 

To use a worksheet formula that returns an array result, you must first select a suitable range of cells, with dimensions (R x C) large enough to accommodate the returned array, then type the formula in the formula bar, and finally enter the formula by pressing CONTROL+SHIFT+ENTER. Excel will indicate that the formula is an array formula by enclosing it in braces and will enter the array formula in all the selected cells. 

If you have imported a data file with a large number of data records (e.g., 8000 data points) you may wish to work with a reduced data set, say, every fiftieth point. The following sections describe three ways you can create such a list by using AutoFill to Fill Down a pattern, by using Excel s Sampling tool, or by using a worksheet formula. 

Any of the four worksheet formulas described below can be used to select every Nth data point from a data table. All four utilize the INDEX(array, row num,column num) function to select an element from the array of data, but they use different methods to calculate the pointers row num and column num into the reference that contains the data. 

The first method uses names for the x and y data ranges a separate worksheet formula must be entered for each column of data. The worksheet formula... 

The second method uses a single worksheet formula that employs a mixed reference. The formula can then be copied into multiple columns. The formula... 

Figure 7-16. Using a worksheet formula to select every Nth data point.

Most, but not all, of the controls that you have seen in Excel s dialog boxes can be installed on a worksheet. These controls return a value to a worksheet cell (the cell link). You can then use this value in worksheet formulas. 

The process of finding the derivative or slope of a function is the basis of differential calculus. Since you will be dealing with spreadsheet data, you will be concerned not with the algebraic differentiation of a function, but with obtaining the derivative of a data set or the derivative of a worksheet formula by numeric methods. 

The spreadsheet fragment shown in Figure 9-14 illustrates the calculation of the first derivative of a function (F = x - 3x - 130x + 150) by evaluating the function at x and at x + Ax. Here a value of Ax of 1 x 10 was used alternatively Ax could be obtained by using a worksheet formula such as =1E-9 x. For comparison, the first derivative was calculated from the expression from differential calculus F = 3x - 6x -130. 

A custom function is used in a worksheet formula in exactly the same way as any of Excel s built-in functions. You can enter it in a formula by using Paste Function, or by typing it. The workbook containing the custom function must be open. 

To send values from a module sheet back to a worksheet, simply use an assignment statement like the following. Numbers, text, the contents of a VBA variable, even a worksheet formula can be entered into worksheet cells or ranges. 

Some function macros perform calculations that could be performed by using a worksheet formula or formulas, while in other cases implementing the function on the worksheet is impossible (when looping or branching is required, for example). 

The main advantage of using a custom function instead of worksheet formulas is minimization of errors occurring when formulas are entered. The main disadvantage is that custom functions are slower than worksheet formulas. For example, in a sheet that uses worksheet formulas to calculate the four alpha values for phosphoric acid in 0.1 pH increments from pH 0 to 14 (calculation of 564 cells) using worksheet formulas (see Chapter 20), there was no detectable delay when the sheet was recalculated. On the same PC, when the ALPHA function was used, it took three seconds to recalculate the sheet. 

CurvArea.xls illustrates three worksheet formulas that can be used to obtain the area under a ciuve. 

---