Computer spreadsheet files

Figure 7.3-1 outlines steps in the scoping process. However, if this information is to be gathered, it should be in a convenient form such as collecting the data as a computer file in a notebook personal computer. The file may be a word processor, a spreadsheet, or a database. If the input is into a word processor such as WordPerfect or Word, it may be in table format. Very similar but Process Scoping Steps... 

Lab VIEW Front Panel (top left) and Block Diagram (bottom) for a Virtual Insftument (VI) for ftansfer of oscilloscope data to a computer for display and generation of a spreadsheet file. The content of the subVI for the latter operation is shown at the top right. 

Regarding the validation of computerized spreadsheets used for in process and finished product analytical calculations (FDA-483, 4), your response states fiiat current spreadsheets were challenged using the proposed revisions to SOP 644, QA/QC Computer Spreadsheet Validation. However, your response does not indicate if computerized spreadsheets for all products which use file spreadsheets were challenged using the proposed revisions to SOP 644. 

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. 

The third set of tools involves the pharmacist s documentation of interventions and results. If lipids are to be measured and followed, the use of a monitoring flow sheet is extremely useful (Fig. 1). Flow sheets may be on paper files, created on computer spreadsheets, or use special software programs. 

A special feature in this book is the inclusion of the Hansen solubility parameter theory that can be used to classify solvents in terms of their nonpolar, polar and hydrogen bonding characteristics. Use of the Hansen solubility parameter theory will allow the worker to systematically search for a solvent substitute or determine the solubility of a resin/polymer in a certain solvent or solvent blend. The files necessary to construct computer spreadsheets that can utilize the Hansen solubility parameter theory are included with this book. The useful spreadsheet files on a computer disk are included in a plastic pocket on the back inside cover of the book. These files can be used on an IBM-compatible computer with Lotus 123 (or Excel) software. These computer spreadsheets were developed in the Lotus 123. WKl file format. The data files can be used with the Lotus 123 Version 5.0 for Windows, the Microsoft Excel Version 5.0 for Windows or any earlier version of the spreadsheet software. The files can also be translated into the Macintosh Excel format if the correct version of Excel is available. The coating industry will find the information on solvent substitution using the Hansen solubility parameter theory of particular interest. The use of computer spreadsheets to compare the solubility envelope of the polymer with likely solvent candidates has been very helpful to the author in past work and others in the coating in selecting substitute solvents or solvent blends. The Hansen solubility parameter values for 166 resins and polymers and 289 solvents are listed. 

The new nonpolar, polar, and hydrogen-bonding values for the solvent blend are calculated in three separate equations utilizing the three solubility parameter values of each blend solvent. A computer spreadsheet data file discussed in Chapter 19 can be used to automatically calculate the blend parameters. Blends of two or three solvents with different solubility values can yield a solvent blend with intermediate values that match a specific resin. Two nonresin solvents can be blended to give a solvent mixture that dissolves the resin. 

The total solubility parameter of a resin or polymer is the point in three-dimensional space where the three partial solubility parameter vectors meet as the center point of the idealized spherical envelope. The distance in space between the two sets of parameters (solvent and polymer) can be represented by the radius of interaction term, R. The radius of interaction term is used to express the degree of mutual solubility. All of these solubility comparisons can be made by using computer spreadsheets that are described in this chapter. These computer spreadsheets were developed in the Lotus 123. WKl file format. The data files are listed on the IBM compatible computer disk in the Lotus. WKl and can be translated to Excel for Windows format. 

Figure 19.2 Computer spreadsheet data file similar to Figure 19.1 which calculates the Hansen solubility parameters for a solvent blend that contains up to seven solvents. Source spreadsheet file developed by the author using his data and [4] cited in Chapter 4.

Figure 19.8 The computer spreadsheet SPCALC.WKl utilizes the contribution constants suggested by Small [I] to calculate the three partial Hansen solubility parameters of a solvent. The solvent specific gravity is also needed for the calculations. Source spreadsheet from the author s files.

The next type of standard output is the spreadsheet. These file formats use comma-separated ASCII code, but also add calculation information and addressing information for the columns and rows they occupy. The simplest of these are. DIF files, which originated to allow information transfer between VisiCalc worksheets in the Apple II computer and have been retained as a standard format.. WK files are Lotus-1,2,3 formats and. XLS are Microsoft Excel formats that have become spreadsheet standards, allowing transfer of data, calculations, addresses, and macro programs. 

Traditionally data, properties, information etc has been stored in files on computer disks. More recently, it has become common practice on Macintosh computers, when using Microsoft software or some UNIX applications, to use either extensions to the file name or the first few bytes in the file (or another file) to indicate some aspects of the data, for example that it is suitable for Microsoft Excel. While this approach is practical to indicate something about files containing columns of data, it is not appropriate to store information about the values in cells in spreadsheet or how it relates to data in other columns. This requires a relational database such as ORACLE, and for performance reasons the values in the cells may only be accessed via the ORACLE API (Application Programming Interface) or SQL (Standard Query Language), in other words, it is suggested that relational databases such as ORACLE should be viewed as sophisticated file systems which allow the values to be organised, efficiently stored, rapidly retrieved etc. 

The next most cited group of observations concerned backup and archive. Systematic backups are required to meet defined schedules. Backups and archives must be maintained for the duration of the record retention requirements and for records readily retrievable. The Warning Letters making these observations referred to CDS, Spreadsheets, electronic drawings and to the implied use of Computer Aided Drawing (CAD) application, complaint files, and Device History Records. 

Electronic spreadsheets. 2. Chemistry—Data processing. 3. Microsoft Excel (Computer file) I. Title. 

If you are continuing the Spreadsheet Exercise from Chapter 5, begin with the data on your computer screen. Otherwise, retrieve the file average.xls from your disk by clicking on File/Open. Make cell D1 the active cell, and type... 

An important aspect we have not yet described so far is how to print, assuming of course that you have a printer attached to the computer, and that it is turned on. The simplest way is to click on the printer icon on the toolbar, usually just under the View menu command. In our case, the spreadsheet is somewhat too complicated for that, so we will use the mouse to select, from the menu, the File Print Preview. This will show us the first page by clicking on the Next button we can see the next page to return to page 1 click on Previous. Clearly, the spreadsheet could use some cleaning up before we print it. Close the preview (this time the Close button is on the icon bar) to return to File, then click outside the menu to go back to the spreadsheet. 

Spreadsheets are created to facilitate computation. Commonly used mathematical operations (such as SIN, LOG, SQRT, and MINVERSE) are built-in as functions, and some more complicated procedures (e.g., Solver, Random Number Generation, Regression) are provided as macros. However, no spreadsheet maker can anticipate the needs of all possible users, and Excel therefore allows the introduction of so-called user-defined functions and macros. In section 9.2d we will describe some user-defined functions, while chapter 10 deals extensively with user-defined macros. However, beyond the simple exercises of section 10.1, it makes no sense to enter long macros by hand, and they are therefore provided in a web site from which they can be downloaded and stored onto your own computer disk or diskette. The web site also contains a sample file that is, likewise, larger than you might want to enter manually. 

The macros find their home in a module that becomes part of the spreadsheet. We therefore need to make the module first, then import the macros into that module. The procedures are slightly different for earlier versions (through Excel 95) and for more recent ones (starting with Excel 97), and are therefore described separately. For the sake of simplicity we will assume that the macros and data are stored in either a computer file (i.e on a hard disk) or a diskette. 

It is usually most convenient to store macros with the particular workbook where they will be used. If you later switch to another workbook, just copy those macros to the new workbook if you need them there as well. However, if you anticipate using a macro often, and you use your own personal computer, you may want to store it in the Personal Macro Workbook, which will keep it as a PersonaLxls file in the XLStart folder. All files stored in that folder are opened automatically when Excel is started, and will then be available to any spreadsheet. To keep them out of view, hide them with Windows => Hide. To modify these files, first use Windows O Unhide, which will show them in the Unhide dialog box. Select the particular hidden file you want to edit, in this case Personal, and click on the OKbutton. 

Lab notebooks are commonly used in the laboratory to record data. This information is often then transferred into a computer file or spreadsheet once outside of the lab. Scientists use spreadsheets to organize, analyze, and display data. Use of spreadsheets simplifies data collection and manipulation and allows the presentation of data in a logical and understandable format. Models are another common way of portraying evidence. A model may be built to demonstrate an object too large or too small to otherwise visualize with ease. In the case of chemistry, computers and their simulations are often used to show the three dimensional structure of a molecule and to predict its possible chemical interactions. 

We are very accustomed to using forms and reports, often prepared in a standard fashion for a particular application. Record-based models are computer implementations of these spreadsheet-like forms. Two types can be identified. The first of these, common in the early days of file processing systems (FPSs) or file management systems (FMSs), is the individual record model. This is little more than an electronic file drawer in which records are stored. It is useful for a great many applications. More sophisticated, however, is the relational database data model, in which mathematical relations are used to electronically cut and paste reports from a variety of files. Relational database systems have been developed to a considerable degree of sophistication, and many commercial products are available. Oracle and Informix are two leading providers. 

---