Spreadsheets and calculations

Even for a simple equation like equation 5.1, by making different assumptions about the data we will arrive at different values of a and b. Most spreadsheets and calculators perform classic linear regression. The assumptions are ... 

In the case of determination of kinetic data from raw experimental data, this usually means feeding such data (e.g., mass and temperature) into a spreadsheet and calculating the preexponential term and the activation energy in the Arrhenius equation. 

This program takes numeric data from an Excel spreadsheet and calculates the average of the numbers. The input data look similar to the following ... 

Note that different spreadsheets and different versions of the same spreadsheet vary in the details of the calculation but that the basic idea for all is to cany out the calculation for the top cell and fill in the remaining cells in the same column with the mouse—a very convenient technique for simple calculations on large data sets. Consult the Help section of your spreadsheet for specific details. 

Although equations 5.13 and 5.14 appear formidable, it is only necessary to evaluate four summation terms. In addition, many calculators, spreadsheets, and other computer software packages are capable of performing a linear regression analysis based on this model. To save time and to avoid tedious calculations, learn how to use one of these tools. For illustrative purposes, the necessary calculations are shown in detail in the following example. 

TABLE 16.1 Example of a Spreadsheet for Calculating Energy and Maintenance Costs in the Procurement Process a Liquid Cooling Lfnit... 

The OPMBS workbooks required the user to enter sample identification and the dates of collection, extraction, and analysis. Additionally, entries were required for the analytical responses of calibrants, the corresponding responses of sample extracts, and the parameters needed to calculate the LOD. Given the required input information, the spreadsheet automatically calculated and displayed, for each analyte ... 

The construction of the problem table to find the minimum utility requirement and the pinch temperature is facilitated by using a spreadsheet. The calculations in each cell are repetitive and the formula can be copied from cell to cell using the cell copy commands. 

Using the data given below, draw an information flow diagram of the process and calculate the process stream flow-rates and compositions for the production of 600 t/d ammonia. Use either the Nagiev split fraction method, with any suitable spreadsheet or manual calculations. 

The system methanol-cyclohexane can be modeled using the NRTL equation. Vapor pressure coefficients for the Antoine equation for pressure in bar and temperature in Kelvin are given in Table 4.176. Data for the NRTL equation at 1 atm are given in Table 4.186. Assume the gas constant R = 8.3145 kIkmol 1-K 1. Set up a spreadsheet to calculate the bubble point of liquid mixtures and plot the x-y diagram. 

Modify the spreadsheet to calculate the energy flux from each star and hence identify the wavelength most suited for energy harvesting in each case. 

Since Excel is a powerful tool used widely for many different purposes with many options not all options can be discussed in this chapter. The focus of the chapter is on manually self-created spreadsheets for data calculation and checks against acceptance criteria (logical operations). Excel spreadsheets that are used with other electronic systems for automatic data or information entry, for further operations, or used as a view tool for databases are not within the scope of this chapter. Nevertheless, these types of spreadsheets are viewed as a normal spreadsheet with automatic entry, and validation, including validation of the interface, will be included to cover this item. In this chapter we provide guidance in validation and revalidation of Excel spreadsheets and information about managing validated spreadsheets. 

Figure 18.1. Spreadsheet for calculation and visualization of dissolution results.

An example of a validation plan, including related information, is given in Figure 18.2. This example is used in all individual sections following it to explain what is needed in the various sections. The example is based on a combination of spreadsheets for calculation of content uniformity by UV analysis developed for a development area. The spreadsheet is developed so that it is flexible and can be used for different products. Furthermore, it checks acceptance criteria set for calibration standards and a system suitability test. 

The Runge-Kutta algorithm is entered into a spreadsheet, and the two Stefan-Maxwell equations (12.196 and 12.197) are integrated using a step size Z/200, with guessed values for the fluxes Ni, N2. The calculated mole fractions of species 1 and 2, Xj (Z) and X2 (Z) were used to define a residual r,... 

Programs such as dBASE II, III PLUS, or IV, and LOTUS allow the user to work easily with databases and perform the calculations that can be summarized in tabular form, respectively. It should be noted that it is also possible to connect these programs to other software. For instance, to use the SPSS/PC + (ref. 6), one can enter the data first into a spreadsheet and perform corrections in this spreadsheet. Another possibility is to use dBASE as a receptacle of knowledge, which can be changed by the user, and connect this to an expert system in which the knowledge that cannot be changed by the end user is embedded. 

Henry Freiser, Concepts and Calculations in Analytical Chemistry. A Spreadsheet Approach, 3rd ed., CRC, Boca Raton, FL, 1992. 

Many conventional texts express regression equations in the form of summations rather than matrices, but both approaches are equivalent with modern spreadsheets and matrix oriented programming environments it is easier to build on the matrix based equations and the summations can become rather unwieldy if the problem is more complex. In Figure 5.2, the absorbance of the 25 spectra at 335 nm is plotted against the concentration of pyrene. The graph is approximately linear, and provides a best fit slope calculated by... 

Concentration Determination of In-Process Samples. The concentration of the unisolated desired product in solution at a particular intermediate step may also need to be determined by HPLC. A data calculation sheet such as Excel with the response factors of the standards and the dilution factor of the sample could be incorporated in the data calculation sheet prior to injection of reaction sample to facilitate the results reporting for the concentration of the intermediate in solution. Hence, only the area of the desired intermediate in solution needs to be populated in the spreadsheet, and the concentration result then can be determined. The determined concentration of the intermediate in solution ensures adequate charging of the raw materials used in the further steps of the synthesis. Also, this intermediate in solution is sometimes further concentrated and the concentration is monitored until the desired concentration is obtained. A solvent switch step is sometimes performed, and the HPLC method must be able to selectively separate the reaction solvents (if they are UV active) from the desired intermediate and potential impurities that may be formed. These reaction solvents may include toluene, inhibited THF with cresol or BHT (if inhibited with BFIT, this is very hydrophobic, so proper elution of this additive may be necessary), ethyl acetate, and so on. Sample preparation here is also important, and the appropriate diluent must be determined to ensure solubility of all components and no reactivity with the sample analyte. 

Every worksheet and printout must list the Spreadsheet and OS version that was used to calculate the results. 

FORM FDA-483 Item 14) There is no provision in the test method procedure for the use of an [redacted] spreadsheet for entering test parameters and calculation of test results that was used to calculate the results of blend samples of Vitaroca and Vinatal from 5/6-13/02. 

Failure to have an adequate validation procedure for computerized spreadsheets used for in-process and finished product analytical calculations. The current validation procedure uses only the values that result in within specification findings, aberrant high findings, and aberrant low findings [21 CFR 211.165(e)]. For example, SOP 644.00, QA/QC Spreadsheet Validation, is deficient in that only a small range of values are being used to challenge computerized spreadsheet mathematical calculations. 

Regarding the failure to use fully validated computer spreadsheets to calculate analytical results for in-process and finished product testing (FDA-483, item 5), your response states fiiat old spreadsheets will be revalidated according to file proposed revisions to SOP 644 prior to being implemented into use. You identify that SOP 644 will not be revised until July 20, 2001. 

---