Using a Worksheet Formula

Using the Sampling tool would be laborious if you needed to sample a data file with, for example, 17 columns a column of x values and 16 columns of associated y values. You d have to use the Sampling tool 17 times. In a case like this it would be preferable to have a formula, using relative references, that could be entered once, filled down to sample the column of x values, then filled right to create formulas that would sample each of the columns of y values. The following formulas illustrate how to do this. 

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 fourth method uses an array formula that can be entered into a single row of cells (here T5 U5) and then copied into the complete range of cells using either Fill Down or AutoFill  

---

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. 

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

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. 

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). 

NAME is displayed when you use a name that Excel doesn t recognize. Most often this occurs when you misspell a name or function, or when you enter a name without having defined it. It will also happen when you enter a text argument in a formula and forget to enclose the text in quotes, or when you use a worksheet function in a formula and forget to include the parentheses and/or arguments. 

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. 

To use Paste Function, follow the procedure described in Chapter 3. Select the worksheet cell or the point in a worksheet formula where you want to enter the function (e.g., cell B2 in Figure 13-10). Choose Function... from the Insert... 

This chapter covers the basics of working with Excel navigating around the worksheet, entering values and formulas, and formatting and editing a worksheet. If you are an experienced Excel user, you can probably skip this chapter however, even experienced users may find a few useful tips in this chapter,... 

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. 

If you enter 10 AM in cell A1 of a worksheet, and =A1 +3 in cell B1, you may at first be confused when cell B1 displays 10 00 AM. But remember that you ve added three days, not three hours if you apply the date format m/d/yy to the cell, you ll see that you ve calculated a date three days from the current date. If you change the formula to =A1+3 00, you get an "Error in formula" message. That s because 3 00 is not a numerical value. To obtain the desired result, 1 00 PM, use the formula =A1+"03 00 . Excel recognizes that 03 00 is text and evaluates it just as it would if you d typed it into a cell. 

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... 

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. 

As a first example, well create the ChemicalFormat macro to format text in a selected cell as a chemical formula. The macro will be a simple one. It will examine each character of a text string if the character is a number, the character will be subscripted. You ll find this macro useful if you label rows or columns in your worksheets with chemical formulas, such as "CH3CH=CH2" or "moles of H3PO4" the macro will be a real timesaver. 

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. 

Prepare a spreadsheet similar to the one shown in Figure 3-7 for the gravimetric determination of nickel using dimethylglyoxime. See Section 37B-3 for details. Use the worksheet from Problem 3-9 to calculate the molar mass of Ni(DMG)2 if it is available. 3-4. Write an Excel formula using the FIND and MID functions to eliminate the square brackets and the uncertainty from the atomic mass of lithium in the lUPAC table and display the numeric characters of the atomic weight. 

---