Entering Worksheet Formulas

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

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. 

As shown in Figure 14.1, a worksheet is divided into rovw and columns. The columns are marked by A, B, C, D, and so on, while the rows are identified by numbers 1, 2, 3,4, and so on. A cell represents the box that one sees as the result of the intersection of a row and a column. You can input (enter) various entities in a cell. For example, you can type in words or enter numbers or a formula. To enter words or a number in a cell, simply choose the cell where you want to enter the inftirmation, type the information, and then hit the Enter key on your keyboard. Perhaps the simplest and the easiest way to move around in a worksheet is to use a mouse. For example, if you want to move from cell A5 to cell C8, move the mouse such that the mouse... 

To use worksheet functions, enter the tablet erythromycin contents from Table 2.4 into the first two columns of an Excel spread-sheet (assume titles in A1 and B1 and the two sets of data in A2 A11 and B2 B11), and then enter the following formulae into A12 A14 and B12 B14 ... 

To enter a value in a worksheet cell, select the cell with the mouse pointer, which appears as a large open cross when it passes over cells. Clicking on the desired cell highlights it, indicating that this is heactive cell, the cell in which you can now enter a value. As you type in a value, the characters appear in the formula bar and the active cell. You can complete the entry in several ways. 

Instead of entering a number in a cell, you can enter an equation (called a formula in Microsoft Excel) that will calculate and display a result. Usually formulas refer to the contents of other cells by using cell references, such as A2, a reference to a cell, or B5 B12, a reference to a range of cells. The value displayed in a cell containing a formula will be automatically updated if values elsewhere in the worksheet are changed. Formulas can contain values, arithmetic operators and other operators, cell references, the wide range of Excel s worksheet functions, and parentheses. 

You can also Copy or Cut text in the formula bar and Paste it in a worksheet cell. Select the text to be copied or cut, then press the Copy or Cut toolbutton or choose the appropriate command from the Edit menu. Complete the operation by clicking the Enter button in the formula bar. Then Paste in the desired cell. 

To enter a reference from a source document into a formula in a dependent document, first select the cell where the value is to be entered and begin typing the formula. At the point in the formula where you want to enter an external reference, click on the sheet tab of the source (use the Window menu to switch to the source document if the source sheet is in a different workbook) and select the cell containing the value to be entered (let s say it s in cell H12 of the worksheet Expt XVIII-32). When you press the Enter button. Excel returns you to the dependent document, and the formula = Expt XVIII-32 H 12 is entered in the cell. 

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. 

Alternatively, you can use Excel s Edit Directly In Cell feature use function key F2 (Windows) or COMMAND+U (Macintosh) or double-click on the cell to enter edit mode. Excel uses colors to show range references in formulas and the corresponding ranges on the worksheet. You can use the right and left arrow keys to move through the formula, or CONTROL+(arrow key) to jump to the next element of the formula, or CONTROL+SHIFT+(arrow key) to select the next element of the formula. 

As you ve already seen, dates and times can be entered into worksheet cells using any one of several convenient formats July 1 can be entered as 7-1, 7/1, July 1, Jul 1 or 1 July, among others. All these date entries produce the date 7/1/xxxx in the formula bar (xxxx is the four-digit year) and the displayed date 1-Jul. Excel enters the current year unless a different year is specified. If you enter the year, either as a two-digit or four-digit number. Excel displays the date in a different format, as e.g., 7/1/xx. 

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. 

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. 

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. 

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. 

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. 

Entering an array as an explicit array of constants, as in the preceding example, is not very convenient. Nor does it provide the generality needed for more complicated examples. To generate an array of integers for use in array formulas, use the ROW worksheet function. When used in an array formula, the expression ROW(1 10), evaluates to the array of numbers 1 2 3 4 5 6 7 8 9 10. Thus the formula to evaluate N could have been entered as follows ... 

Well begin the development of our formula by creating a formula to count the number of students whose year of graduation is 1996. Enter the formula =YOG in any worksheet cell. Only one value can be displayed in the cell, but if you highlight YOG in the formula bar and press the F9 key (Windows) or COMMAND+= (Macintosh), the array of values will be displayed (only the first portion of the array is shown) ... 

Basically, to link a chart text element to a worksheet cell, you enter a formula as the chart text. The syntax of the formula is ... 

The formula entered as chart text must be only a cell reference. If you want the chart text to be text concatenated with a number, e.g. to produce a title such as Half-wave Potential = -0.74V, where the potential is a value in cell B2 of the worksheet, the complete formula = Half-wave potential = " B2 "V" must appear in the worksheet 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... 

To illustrate, let s repeat the calculation of the solubility of barium carbonate. Goal Seek allows you to obtain the same result much more easily. Open a new worksheet and in cell A1 enter the value 1. In cell B1, enter the formula =A1 2 -1.0E-6 SQRT(A1)-5.1 E-9. The task now will be to use Goal Seek... to find the value in cell A1 that makes the function (in B1) equal to zero. 

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. 

Cells may contain text, numbers, or formulas. We will begin by typing some text into the worksheet. Click on cell Al, and type Gravimetric Determination of Chloride followed by the Enter key [ J]. Notice that the active cell is now A2, so you may type Samples [ J]. As you type, the data that you enter appear in the formula bar. If you make a mistake, just click the mouse in the formula bar, and make necessary corrections using the backspace key or the delete key. Continue to type text into the cells of column A as shown below. 

Your worksheet should now display the atomic masses of Na and Cl in cells 12 and 13, respectively, and the number 1 in cells H2 and H3 to indicate the number of atoms of each element in the formula of NaCl. Now click on cell J1 and enter the following. 

As a final exercise, retrieve the spreadsheet that we created in Chapter 3 for the gravimetric determination of chloride, which we called grav chloride.xls. Enter formulas into cells B12—B14 to compute the mean, standard deviation, and the RSD in parts per thousand of the percent chloride in the samples. In this example, multiply the relative standard deviation by 1000 in cell B14. Adjust the decimal point in the results to display the proper number of significant figures. The worksheet below shows the results. Save your worksheet so that you can use it as a model for making laboratory calculations. 

---