Spreadsheet Applications

Spreadsheet Applications. The types of appHcations handled with spreadsheets are a microcosm of the types of problems and situations handled with fuU-blown appHcation programs that are mn on microcomputers, minis, and mainframes and include engineering computations, process simulation, equipment design and rating, process optimization, reactor kinetics—design, cost estimation, feedback control, data analysis, and unsteady-state simulation (eg, batch distillation optimization). 

The OPMBS used a custom-written spreadsheet application, i.e., a workbook, in conjunction with laboratory automation systems to standardize data recording, calculations, and presentation of results. Devising this approach required careful differentiation between (a) the workbook used to calculate and report the results and (b) the data acquisition systems used in each laboratory. The laboratory systems were used to collect the raw chromatographic data, but the calculation modules in the laboratory systems were not used. Instead, all calculations were done in the workbook. Use of the laboratory systems to collate and output the final results was considered but was rejected for two reasons. First, different laboratories used different systems, and some laboratories used more than one system. The output characteristics of the various systems differed considerably and would have required extensive modification... 

Dermot Diamond, Venita C. A. Hanratty. Spreadsheet Applications in Chemistry using MS Excel. Wiley 1997... 

Once the mass spectmm of a calibration standard is known and the elemental composition of the ions that are to be included in the mass reference list are established by an independent measurement, a reference list may be compiled. For this purpose, the listed reference masses should be calculated using six decimal places. Otherwise, there is a risk of erroneous reference values, especially when masses of ion series are calculated by multiplication of a subunit. Such a tasks are well suited for spreadsheet applications on a personal computer. 

The data for the trace has now been copied to the clipboard. Switch to your spreadsheet application and select the cell that you would like to contain the text label for the time data. The data will be pasted into two columns. The first column will contain the time coefficient of the data. The first row will contain the text labels of the data (Time and V(Vin) in this example). I will paste the data starting at cell Al ... 

Huber has published primers with detailed information on the validation of macros and spreadsheet applications [11] and on the qualification of network infrastructure and the validation of networked systems [12]. 

When generating the model with ANNs, one of the precautions that must be taken into account is that the maximums and the minimums of concentrations and also the readings have to be in the training subset. In order to do this, it is useful to use a spreadsheet application to arrange the data, the first samples being the ones that contain maximums and minimums. The rest of the samples can be randomized. 

In conclusion, the LINEST function found in almost all spreadsheet applications is simple to use and can quickly generate Hansch equations from activity data and tabulated parameter values of R-groups. 

Properly qualified spreadsheet applications for calculations (if used). 

BIOCHEMICAL DATA ANALYSIS WITH SPREADSHEET APPLICATION... 

Diamond, D., and Hanratty, V. C. A. (1997) Spreadsheet Applications in Chemistry Using Microsoft Excel. John Wiley Sons, New York. 

For this example, we do not give the MATLAB code for the differential equations. The code can be fairly complex and, thus, its development is prone to error. The problem is even more critical in the spreadsheet application where several cells need to be rewritten for a new mechanism. We will address this problem later when we discuss the possibility of automatic generation of computer code based on traditional chemical equations. 

Manual maintenance of RTM documents can be labor intensive and error prone, so it is recommended that the availabihty of software solutions or CASE tools for managing traceability information be considered as an alternative. Spreadsheet applications are an obvious choice in simple cases. For more complex document sets, a more sophisticated automated solution is much more likely to be up to the job. 

Level 1 If the data is to be manipulated using spreadsheet macros, the spreadsheet application must be considered as a GAMP Category 5 component. Macros in Microsoft Excel are Visual Basic computer programs written specifically for that application. While they may be simpler than the other customized elements of the integrated CDS, they stiU carry the same liabilities. 

As spreadsheet applications are usually in a bundle with the corresponding word processor, most users do not compare the features of the spreadsheet application but rather consider the overall offering of the complete office package. For the rest of this chapter aU the samples are based on Excel 97 (Excel Version 8.0h). 

Spreadsheet applications can be classified based on their complexity and functionality. Depending on the classification, different approaches to validation can be taken. Table 32.2 specifies restrictions in use of a spreadsheet that should exist for validation to be based on a particular GAMP category of software to apply. 

Spreadsheet applications need to be protected from inadvertent or unauthorized changes. Applications without database functionality should be preserved in a write-protected directory, where only a very limited number of people have access and aU changes are documented. 

A supplier assessment may be necessary if a third party is being used to develop the spreadsheet application. Supplier Audits for COTS software packages are not required. 

With the increased complexity of the spreadsheet application, and the hierarchy of specification and design documentation, it becomes very important to ensure there is good traceability between requirements and testing. For this reason the use of a Requirements Traceability Matrix (RTM) is recommended. 

Knowledgeable analysts are often developing spreadsheets in the lab where they are used. This helps ensure that applications are user friendly and as required by Annex 11 of the EU-GMP have the necessary user involvement. Nevertheless, there should be a development standard defined for spreadsheet applications. Such a standard could basically follow the life cycle presented in Figure 32.6. External developers should also follow equivalent standards. 

Users should only have read access to spreadsheet applications. All templates should be password protected. The password should not be identical with the NT password because the spreadsheet password does not age. The system owner needs to administer the passwords. If there is no authorization via the operating system (insular workstations), the system owner should define and administer the Specific access authorization. Group access should not be allowed. Only cells receiving an entry should be unprotected (unlocked). Macros need to be hidden and protected from user manipulation. 

Twelve basic requirements for electronic records and signatures are defined in U.S. regulation 21 CFR Part 11 concerning electronic records and electronic signatures. Table 32.4 suggests necessary actions to comply with spreadsheet applications. 

In most cases, though, users aren t satisfied to only use the tools that are provided with Windows. Either a particular tool they need isn t included (for example, Microsoft didn t put a spreadsheet application into Windows), or they need a more sophisticated version of a particular application—Wordpad and Word 2000 are both word processors, but that s about where the similarity ends. This demand has led to a booming software industry, and there are literally thousands of applications available that users can install and use with Windows. Because of this, installing and maintaining programs on user s computers is a big part of a technician s job. In order to prepare you for this task, we will discuss the following topics in this chapter ... 

The most popular spreadsheet programs include Microsoft Excel, Lotus 1-2-3, and Quattro Pro. Because of its wide availability and general utility, we have chosen to illustrate our examples using Microsoft Excel on the PC. Although the syntax and commands for other spreadsheet applications are somewhat different from those for Excel, the general concepts and principles of operation are similar. The examples we present may be accomplished using any of the popular spreadsheet applications the precise instructions must be modified if an application other than Excel is used.- In our examples, we will assume that Excel is configured with default options as delivered from the manufacturer unless we specifically note otherwise. 

D. Diamond and V. C. A. Hanratty, Spreadsheet Applications in Chemistry Using Microsoft Excel. New York John Wiley Son.s, 1997 H. Preiser. Concepts Calculations in Analytical Chemi.stry A Spreadsheet Approach. Boca Raton. FL CRC Pre.ss, 1992 R. de Levie, Principles of Quantitative Chemical Analysis. New York McGraw-Hill, 1997 R. de Levie, A Spreadsheet Workbook for Quantitative Chemical Analysis. New York McGraw-Hill, 1992. 

---