Functions worksheet

With reference to Figure 2.19, Figure A2.1[a] displays a portion of the Function worksheet in the file hga.xls for the calculation of the projections of the 5Hg[aj listed in Table A1.1 of Appendix 1 on the vertex positions of the regular orbit cage of In point symmetry. These are the coefficients of the linear combinations of equation A2.1, with the assumption that the vertices are decorated by p local radial functions to which Hiickel approximations can be applied and are collected as a coefficient matrix on the Setup worksheet shown in Figure A2.1[b]. 

The locked cells in any worksheet could not be opened or altered by laboratory staff without the knowledge and agreement of the Study Director. Use of the locking function in the workbook, therefore, constrained the analytical laboratories to a single system for recording data and calculating results. 

This Worksheet demonstrates using Mathcad s F distribution function and programming operators to conduct an analysis of variance (ANOVA) test. 

The one thing at which Excel does not excel is statistical analysis. The (very basic) level of material covered in this chapter is about at (if not beyond) the limit of its capabilities. It can be used to generate means, medians, SDs and quartiles, but while the first three are OK, the quartile values generated are somewhat unconventional and will not be pursued further. The mean, median and SD of a data set can be generated by using either worksheet functions or the Data Analysis tool. 

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

However, computers are quicker (and more reliable). Excel does not offer the SEM as a standard worksheet function, but it is included in the output from the Data Analysis tool (see Chapter 2). Both Minitab and SPSS include it in their Descriptive Statistics routines. 

There are several useful logical functions in Excel. IF is a common feature. Figure A. 12 represents the function =IF(A1[Pg.435]

A Maple worksheet for a similar ealenlation of Franek-Condon factors for harmonic and Morse oscillators is available from the Maple Application Center at www.maple-apps.com see Estimation of Franck-Condon Factors with Model Wave Functions by G. J. Fee, J. W. Nibler, and J. F. Ogilvie (2001). A Mathcad calculation for a harmonic oscillator is described by T. J. Zielinski, J. Chem. Educ. 75, 1189 (1998). 

In the first column of the DFMEA worksheet, list all the components that could contribute to a failure mode and the intended function for each one. For example, one component of our shaving system is the blade, and its... 

Note the availability of another Setup command button on the main toolbar of this worksheet its functionality is similar to the Setup button on the main logo sheet, but, as you can see in Figure 1.6, the SET command bar button to save the window size is replaced by a new button, the action of which is to place particular displays on this window and the others accessible using the command buttons of Figure 1.4 as considered best for a particular monitor and the sharpness of the viewer s eyes. This second command bar is included as the Setup option on the main command bar for all the worksheets accessed through the command buttons displayed in Figure 1.5. 

In Figure A2.2[a] the application of the powerful TABLE() and LOOKUP() functions of EXCEL as the formula in cell E 16, the uppermost top-left cell of the 121 x 121 [120-1-1 to include the lookjup values] selected area of the worksheet... 

For the Hiickel calculations, the remainder of the Setup worksheet is devoted to the imposition of the orthonormality condition on the 5Hg[a] functions of Table ALL This condition in Hiickel theory requires only matrix multiplications between the matrix of coefficients and its transpose, with stepwise imposition of, for example, Gram-Schmidt orthogonalization until... 

Figure A2.1 Screen dumps from the file Ih.xls in the BonusPack, showing portions of the Function [a] and Setup [b] worksheets. In [a] the function projections on the regular orbit cage are summed over the individual terms. Then in [b] the 5 x hga coefficients over the pjr decorations of the regular orbit cage are displayed in matrix form.

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. 

To change only a selected range of values to "precision as displayed", use the FIXED worksheet function (see "Text Fimctions" in Chapter 3). 

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. 

Many worksheet functions can be used in a 3-D formula, for example AND, AVEDEV, AVERAGE, COUNT, COUNTA, DEVSQ, LARGE, MAX, MEDIAN, MIN, OR, PRODUCT, SMALL, STDEV, SUMSQ, VAR. 

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. 

Appendix A lists selected worksheet functions in the Database, Date Time, Information, Logical, Lookup Reference, Math Trig, Statistical, Lookup Reference, and Text categories. Appendix B provides an alphabetical list of these worksheet functions along with the required syntax, some comments on the required and optional arguments, one or more examples and a list of related functions. 

Most worksheet functions require one or more arguments the values that the function uses to calculate a return value. The arguments are enclosed in parentheses following the function name, e.g., SQRT(125) or SUM( F 3 F 28) or SUBSTITUTE(PartNumber, "-1995, "-1996"). A few functions, such as Pl() or NOW(), do not require arguments, but the opening and closing parentheses must still be provided. 

When an optional argument is omitted, a default option for that argument is used, so you need to know what is assumed when the argument is omitted. For example, the worksheet function LOG (number, base) returns the logarithm of a number to a particular base. If the optional argument base is omitted, the function returns the base-10 logarithm. 

Times are represented by the decimal part of the serial number. You can use either number formatting or worksheet functions to convert these date serial values into comprehensible dates. 

Dates can also be entered using the worksheet functions DATE(yy,/nni,dcO, TODAY() or NOW(). DATE is used to enter the serial value of any date. The function TIME(AiAj, mm, ss) performs the same function for a particular time. 

TODAY()and NOW() return the serial value of today s date, or date and time, respectively. TODAY() returns an integer number (date only) while NOW() produces a number with decimal (date and time). Both functions return the serial value at the time the function was entered this value is not updated continuously but is updated each time the worksheet is recalculated. 

The following worksheet functions operate on the date serial value to return a date or time YEAR(value), MONTH(va/ue) and DAY value). Value canhe a serial value, a cell reference or a date as text. MONTH returns a number between 1 and 12, DAY a number between 1 and 31. The text function TEXT can also be used to format a date serial value any custom date format can be applied (see "The FIXED and TEXT Functions" later in this chapter). 

Excel provides a wide range of worksheet functions that operate on text. You are already familiar with the operator, to concatenate text or text and values. Most of Excel s text functions select or modify one or more characters within a text string. 

Occasionally you will not be sure of the arguments and their proper order. If you type the function name in a worksheet cell and then press CONTROL+A, you will go directly to the Step 2 dialog box. You can then enter values for the function s arguments. 

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. 

Microsoft Excel 97 Worksheet Function Reference, Microsoft Press, Redmond WA, 1997. 

You can select individual values in an array by using the INDEX function, but this is a "read-only" option. If you need to be able to change individual values in an array, you should enter the values in a range of worksheet cells, rather than as an array. 

A few of Excel s worksheet functions are array functions, that is, they return arrays as results. One of these, the LINES statistical function, is described in Chapter 11. 

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

The problem with this formula is that if you insert a row above the row containing the formula, the formula becomes =PRODUCT(ROW(2 11)). You can prevent this by using the INDIRECT worksheet function, described next. 

The text argument of the indirect worksheet function evaluates to "1 20", and the expression ROW(INDIRECT("1 " A1)) evaluates to... 

ExceTs COUNTIF and SUMIF worksheet functions can return a value based only on a single criterion. For example, consider a database, the first portion of which is shown in Figure 4-7. You could use COUNTIF to return the number of students in the Chem 1 class whose major is biology, or the number whose year of graduation (YOG) is 96, but you can t use COUNTIF to find out how many students are biology majors and are in the class of 96. You can construct an array formula that will do this. 

You can use the LARGE worksheet function in an array formula to sort a list of number values automatically by means of an array formula, rather than manually by using the Sort... command. 

Excel provides a number of menu commands and functions that permit you to use a list as a database. You can use menu commands to find records in the database that match criteria that you define, or you can use worksheet fimctions to extract numerical information from a database. 

---