Using spreadsheets

The spreadsheet is one of the most powerful and wide ranging of all microcomputer applications. It is the electronic equivalent of a huge sheet of paper with calculating powers and provides a dynamic method of storing and manipulating data sets. 

Statistical calculations and graphical presentations are available in many 

The spreadsheet (Fig. 47.1) is divided into rows (identified by numbers) and columns (identified by alphabetic characters). Each individual combination of column and row forms a cell which can contain a data item, a formula, or a piece of text called a label. Formulae can include scientific and/or statistical functions and/or a reference to other cells or groups of cells (often called a range). Complex systems of data input and analysis can be constructed (models). The analysis, in part or complete, can be printed out. New data can be added at any time and the sheet recalculated. You can construct templates, pre-designed spreadsheets containing the formulae required for repeated data analyses. 

CmiRtriictiru) a spreadsheet - start with a simple model and extend it gradually, chocking for correct operation as you go. 

The power a spreadsheet offers is directly related to your ability to create models that are accurate and templates that are easy to use. The sequence of operations required is  

Spreadsheet a display of a grid of cells, into which numbers, text or formulae can be typed to form a worksheet. Each ceil is 1 uniquely identifiable by its column and row number combination (Lei its two- dimensional co-ordinates) and can contain a formula, which makes it possible for an entry to one cell to alter the contents of one or more other cells  

Template - a pre-designed spreadsheet without data but including all formulae necessary for (repeated) data analysis. 

---

Whereas the general office community reaped the benefits (and endured the pain) of the rise of the personal computer, the scientific and technical market was forced to wait. Chemists used spreadsheets and suffered through word processors that did not know what a chemical stmcture was. However, there is now a good variety of personal productivity tools that have been designed specifically for the chemist. 

Energy balance calculations are best solved using spreadsheets or by writing a short computer program. A suitable program is listed in Table 3.2 and its use described below. The use of a spreadsheet is illustrated in Example 3.14b. 

This can be solved by trial and error or using spreadsheet software (see Section 3.9) ... 

World-scale producers use spreadsheet analysis to evaluate the economics of different options over the lifetime of the plant (often 20 years is assumed), taking account of operating, maintenance and capital costs. The chlor-alkali industry also expects the current density (CD) to increase in a manner that is dependent on membrane development. Other important factors expressed by producers about membrane technology choice included component lifetimes and reliability. 

Using spreadsheet software, plot the following data and give the concentration of the unknown solution ... 

You should see a peak in the absorbance at about 660 nm. Obtain the precise maximum absorbance at the peak for each solution, and prepare a bar graph (use spreadsheet software) of absorbance vs. solvent. 

The values for ionization energy in the periodic table in Appendix C are first ionization energies. Construct a bar graph to show the relative sizes of lEi values for the main group elements. If available, use spreadsheet software to plot and render your graph. 

Finally, one can use spreadsheets (e.g. Excel, Lotus) to calculate the effects of the factors and to interpret the results. An example with Lotus-1-2-3 is given in reference [44]. 

Lim, K. F. "Using spreadsheets to teach quantum theory to students with weak calculus backgrounds," http //www.mathcentre.ac.uk/resources/ casestudies/mathsteam/lim.pdf, (Accessed May 22, 2006). 

The tests should be repeated thereafter at regular or nonregular intervals. Therefore, any test case should be designed for reuse using sets of data inputs and known outputs. If the program is used infrequently, it is good practice to run the tests each time the program is used. Spreadsheet validation is discussed in detail in Chapter 18 and in Ref. [11]. 

If you plan to use spreadsheets in this course, you should know how to enter formulas in a spreadsheet and how to draw a graph of data in a spreadsheet. 

Anyone with a serious need to calculate redox titration curves should use a spreadsheet with a more general set of equations than we use in this section.5 The supplement at www.freeman.com/qca explains how to use spreadsheets to compute redox titration curves. 

Use spreadsheet (Excel) or a statistical program to solve the following problems (1-8) after reviewing pertinent topics discussed in biochemistry texts (Garrett and Grisham,1999 Van Holde et ah, 1998 Voet and Voet, 1995 Zubay, 1998). 

One of the most useful tools to spot and eliminate errors is a spreadsheet, such as Excel or QuattroPro. QSAR modelers very frequently use spreadsheets to organize data into columns and rows of standardized values of the independent and dependent parameters. Spreadsheets allow easy sorting and filtering — two important functions used to find problem data and duplicates and other errors. In addition, spreadsheets have search and replace routines, plotting, and correlation functions, which allow the data to be reviewed in various comprehensive ways. The data can also be exported to other file types, which allow analysis by other software for statistics and any types of quantitative and qualitative relationships that may exist. It cannot be emphasized enough that the typical spreadsheet functions (including graphing functions) are excellent tools to find and eliminate erroneous or questionable values, duplicates, and other problem entries. 

Most of the exercises in Chapter II can be done using a spreadsheet program, and you are encouraged to do so. It would also be worthwhile to duplicate the examples in Chapter II, and in Chapter XXI on least-squares procedures, using spreadsheet functions. 

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. 

Use spreadsheets only in a controlled way. Inventorize spreadsheets and reference fiiem in the mefiiod. 

Statistical analysis using spreadsheet format experimental design. Macintosh. Anonymous ftp at ftp.sas.com. SAS statistical and data management system on IBM mainframes, VAX, PCs (Windows), Sun, HP, and IBM UNIX workstations. 

Statistical analysis using spreadsheet format experimental design. PCs under Windows, VAX, and mainframes. 

Trial-and-error searches of this sort are easy to do using spreadsheet programs. Enter a guessed value of Tpp in one cell and the formula for /(Tpp) (including the Antoine equation for each vapor pressure) in an adjacent cell, then vary the value in the first cell until the value in the second cell is sufficiently close to zero. If the spreadsheet program has a goabeek tool, the... 

For the final activity calculations a software package called LSC Plus (supplied by Raddec Ltd.) is used. This software was developed to avoid using spreadsheet calculations, to minimize transcription errors and to save time. The counting of samples on Quantulus consists of 3 repeat counts. For example, when counting a batch of 20 samples, it produces 60 individual results you then have to manually enter into a spreadsheet. It automatically imports those data and calculates final results and total expanded uncertainties together with the LODs (Fig. 5). It has built-in quality control features introduced to assist in the requirements of ISO17025. 

The intent of this book is not simply to provide a series of templates that can be applied to particular situations (although there are lots of useful spreadsheet templates, macros and other tools on the disk that accompanies this book), but to show how you can create your own spreadsheets or macros to solve completely different chemical problems. 

---