Useful Excel Functions

This section will examine various Excel functions that can be used for solving statistical problems. 

---

To use this table in an Excel spreadsheet, you enter three readings made by your trained observer and three made by the device. The mean error and its SD is calculated by using Excel functions. If the error is more than 5 mmHg, this device should not be used in this patient. 

Some examples of commonly used Excel functions, along widi their proper use and descriptions, are shown in lable 14.3. Refer to Example 14.3 and Figure 14.6 when studying Table 14.3. 

To use Excel functions for which there is no VBA counterpart (e.g., ATAN2), the Appl icat ion object can be used as shown in Chapter 2. Given the code... 

An Excel macro is given in Appendix 7.2, and some results are shown in Figure 7.7. The macro is specific to the example reaction with v = - -l but can be generalized to other reactions. Components of the macro illustrate many of the previous examples. Specific heats and enthalpies are calculated analytically using the functional form of Equation (7.19) and the data in Tables 7.1 and 7.2. The main computational loop begins with the estimation of Kthermo using the methodology of Example (7.15). 

Errors and Inconsistencies in Formatting Dates. It is also necessary to use standardized formats to record dates. For example, Oracle stores a date as the number of seconds since January 1, 4712 BC, and then uses various functions to display the dates in more human-friendly formats. There are many different and personal ways for recording dates one of the authors (AS) has noted at least 25 different ways in a single clinical trial submitted for FDA review Should February 1,2007 be recorded as 1 February 2007,1 Feb 2007, 1 Feb 07, or 02/01/07 This problem still exists when data for numerical dates are extracted into software programs such as Excel that do not force the user to select a unique format for dates. 

To compute these probabilities in Microsoft Excel, put the value of x in cell B2, say, and use the functions... 

Above values refer to a single tail outside the indicated limit of. For example, for 95 percent of the area to be between —t and + in a two-tailed distribution, use the values for 0.025 or 2.5 percent for each tail. This table is obtained in Mircrosoft Excel using the function TINV(a ydf)y where a is. 05. 

Organomanganese reagents can be used for the formation of ketones from acid chlorides with excellent functional group tolerance, the corresponding ketones isolated in good yield. Gallo et al. have reported some hetero-... 

You can carry out the calculations using the Microsoft Excel function key (found by clicking on the insert button in the toolbar) ... 

An allene serves as an excellent functional group for cyclization via isomerization with the use of a novel nickel-chromium bimetallic catalyst (Scheme 16.69) [76]. 

The spreadsheet in Figure 4-62 is heavily matrix based (see Chapter 2, for an introduction to basic matrix functions in Excel). It is the only way to keep the structure reasonably simple. The matrix C in cells A21 C31 is computed in the usual way, see equation (4.63) the parameters required to compute the concentration matrix are in cells Q4 S4, they include the initial concentration for species A and the two rate constants that are to be fitted. In cells E 16 018 the computation of the best absorptivity matrix A for any given concentration matrix C, is done as a matrix equation, as demonstrated in The Pseudo-Inverse in Excel (p.146). Similarly the matrix Ycaic in cells E21 031 is written as the matrix product CA. Even the calculation of the square sum of the residuals in cell R7 is written in a compact way, using the Excel function SUMXMY2, especially designed for this purpose. We refer to... 

Table 6.3. Values of the two-tailed Student s f distribution for 90%, 95%, and 99% confidence calculated using the Excel function...

To find values of t that are not in Table 4-2, use the Excel function TINV. For 12 degrees of freedom and 95% confidence, the function TINV(0.05.12) returns f = 2.179. 

Bill Excel LINEST function. Enter the data from Problem 4-23 in a spreadsheet and use the LINEST function to find the slope and intercept and standard deviations. Use Excel to draw a graph of the data and add a TRENDLINE. 

Figure 7-12 Spreadsheet for efficient experimental design uses Excel UNEST routine to fit the function y = mAxA + nv% + /Tfcxfc to experimental data by a least-squares procedure.

---