Introducing Spreadsheets

Spreadsheets are powerful tools for manipulating quantitative information with a computer. They allow us to conduct what if experiments in which we investigate effects such as changing acid strength or concentration on the shape of a titration curve. Any spreadsheet is suitable for exercises in this book. Our specific instructions apply to Microsoft Excel, which is widely available. You will need directions for your particular software. Although this book can be used without spreadsheets, you will be amply rewarded far beyond this course if you invest the time to learn to use spreadsheets. 

Let s prepare a spreadsheet to convert temperature from degrees Celsius to kelvins and degrees Fahrenheit by using formulas from Table 1-4  

In cell Bl, type the label °C (or Celsius or whatever you like). For illustration, we enter the numbers —200, -100, 0, 100, and 200 in cells B2 through B6. This is our input to the spreadsheet. The output will be computed values of kelvins and °F in columns C and D. (If you want to enter very large or very small numbers, you can write, for example, 6.02E23 for 6.02 X 10 and 2E-8 for 2 X 10 . ) 

Label column C kelvin in cell Cl. In cell C2, we enter our first formula—an entry beginning with an equal sign. Select cell C2 and type =B2+ A 3 . This expression tells the computer to calculate the contents of cell C2 by taking the contents of cell B2 and adding the contents of cell A3 (which contains the constant 273.15). We will explain the dollar signs shortly. When this formula is entered, the computer responds by calculating the number 73.15 in cell C2. This number is the kelvin equivalent of — 200°C. 

Now comes the beauty of a spreadsheet. Instead of typing many similar formulas, select cells C2, C3, C4, C5, and C6 all together. In the Home ribbon, go to Editing and select Fill and then Down (or just press the two keys Ctrl+D). This command tells the computer to do the same thing in cells C3 through C6 that was done in cell C2. The numbers 173.15, 273.15, 373.15, and 473.15 will appear in cells C3 through C6. 

---

By now you should understand the chemistry that occurs at different stages of a precipitation titration, and you should know how to calculate the shape of a titration curve. We now introduce spreadsheet calculations that are more powerful than hand calculations and less prone to error. If a spreadsheet is not available, you can skip this section with no loss in continuity. Consider the addition of liters of cation M+ (whose initial concentration is C ) to liters of solution containing anion X- with a concentration C%. 

New Spreadsheet Calculations. Throughout the book we have introduced spreadsheets for problem solving, graphical analysis, and many other applications. Microsoft Excel has been adopted as the standard for these calculations, but the instructions could be readily adapted to other programs. Several chapters have tutorial discussions of how to enter values, formulas, and built-in functions. Many other examples worked in detail are presented in our companion book. Applications of Microsoft Excel in Analytical Chemistry. We have attempted to document each stand-alone spreadsheet with working formulas and entries. 

Suppose that you measure the density of a mineral by finding its mass (4.635 0.002 g) and its volume (1.13 0.05 mL). Density is mass per unit volume 4.635 g/1.13 mL = 4.101 8 g/mL. The uncertainties in mass and volume are 0.002 g and 0.05 mL, but what is the uncertainty in the computed density And how many significant figures should be used for the density This chapter answers these questions and introduces spreadsheets—a powerful tool that will be invaluable to you in and out of this course. 

An ANN is an array of three or more interconnected layers of cells called nodes (much like columns of cells in a spreadsheet). Data are introduced to the ANN through the nodes of the input layer. For instance, each input layer node can contain the relative intensity of one of the m/z values from a bacterial pyrolysis mass spectrum. The output layer nodes can be assigned to iden-... 

In addition, the presence of the same element concentration (in our example, Th) in both the coordinates introduces a strong artificial correlation of no meaning in terms of process. Using a random number generator, e.g., in a spreadsheet, the reader may check that randomly and independently generated values of Ta and Th usually produce quite good correlations in Th/Ta vs Th diagrams. This topic is specifically dealt with in Section 4.2. 

As you have probably already noticed, Excel uses alphabetical letters for the column index and numbers for the row index in order to specify a cell on the spreadsheet. The column index appears before the row index, e.g. cell A3 refers to column A row 3. This is contrary to the general convention as introduced earlier and, if desired, you can change Excel s default settings (T ools-Options-General) to accommodate a row-column notation. The notation A3 will then be altered into R3C1 (row 3, column 1). 

A few comments and observations are appropriate. The quality of the initial guesses for the free concentrations of the components is more critical than in the Matlab Newton-Raphson routine introduced previously. The main disadvantage of the Solver, however, is the fact that it can only be applied to one instance. It cannot be dragged around on the spreadsheet like most other functions of Excel. It means, for our present example, that for each solution the Solver needs to be set up individually, defining the Set Target... 

This section introduces a few basic features of Excel 2000 for a PC computer. Other versions of Excel and other spreadsheets are not very different from what we describe. Excellent books are available if you want to learn much more about this software.19... 

The systematic treatment of equilibrium is a way to deal with all types of chemical equilibria, regardless of their complexity. After setting up general equations, we often introduce specific conditions or judicious approximations that allow simplification. Even simplified calculations are usually very tedious, so we make liberal use of spreadsheets for numerical... 

At this point, a considerable amount of theory on Hansch analysis has been presented with almost no examples of practice. The next three Case Studies will hopefully solidify ideas on Hansch analysis that have already been discussed. Each Case Study introduces a different idea. The first is an example of a very simple Hansch equation with a small data set. The second demonstrates the use of squared parameters in Hansch equations. The third and final Case Study shows how indicator variables are used in QSAR studies. If you are unfamiliar with performing linear regressions, be sure to read Appendix B on performing a regression analysis with the LINEST function in almost any common spreadsheet software. A section in the appendix describes in great detail how to derive Equations 12.20 through 12.22 in the first Case Study. 

This chapter is aimed at introducing the concepts of biostatistics and informatics. Statistical analysis that evaluates the reliability of biochemical data objectively is presented. Statistical programs are introduced. The applications of spreadsheet (Excel) and database (Access) software packages to analyze and organize biochemical data are described. 

These functions are monotonic (i.e., increasing or decreasing in a continuous fashion) with respect to [L] (or to pL) for the first and last species in the equilibria involved they show a maximum for the intermediate species (called ampholytes). The resulting chemical species distribution diagrams can easily be constructed by introducing these equations in a spreadsheet (e.g., Excel). See Examples 2.5 and 2.6. T/pical examples can be found in the educational literature, and several programs are available for these calculations (see, for example, Kim, 2003). 

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. 

Most cost-estimating and economic analysis calculations are easily carried out using spreadsheets. Templates are introduced in the examples throughout the chapter. Blank templates are given in Appendix G and in the online material at http //books. elsevier.com/companions. The more sophisticated software that is used in industry for preliminary estimating is discussed in Section 6.3. 

Physical properties and/or the equations used to predict physical properties can be stored on spreadsheets. No programming experience is required, and simple calculations are easy to carry out using the spreadsheet software. Numerical data such as tables can be typed into cells, where they are easily seen. Equations can also be typed into cells and are introduced as needed. Labels and units as well as remarks can be added as needed. 

From an industrial perspective the most elegant chemistry is the most economical chemistry. Synthesis through resolutions are less glamorous than those in which the stereochemistry is set through asymmetric synthesis nonetheless, resolutions have been shown to be more economically effective in some cases [8,45, 60]. With asymmetric synthesis the desired stereochemistry must be efficiently introduced. If it is not, the undesired stereoisomers may complicate isolations and reduce the yield of the desired product. Many of the features of a route that need to be considered were mentioned in Chapter 2. Comparing product costs through spreadsheets may be extremely valuable [61]. 

Spreadsheet Summary In Chapter 2 oi Applications of Microsoft Excel in Analytical Chemistry, we introduce the use of Excel s Analysis ToolPak to compute the mean, standard deviation, and other quantities. In addition, the Descriptive Statistics package finds the standard error of the mean, the median, the range, the maximum and minimum values, and parameters that reflect the symmetry of the data set. 

Spreadsheet Summary In Chapter 2 of Applications of Microsoft Excel in Analytical Chemistiy, we develop a worksheet to calculate the pooled standard deviation of the data from Example 6-2. The Excel function DEVSQO is introduced to find the sum of the squares of the deviations. As extensions of this exercise, you may use the worksheet to solve some of the pooled standard deviation problems at the end of this chapter. You can also expand the worksheet to accommodate more data points within data sets and larger numbers of sets. 

Spreadsheet Summary Chapter 4 of Applications of Microsoft Excel in Analytical Chemistry introduces another way to perform a least-squares analysis. The Analysis ToolPak Regression tool has the advantage of producing a complete ANOVA table for the results. A chart of the fit and the residuals can be produced directly from the Regression window. An unknown concentration is found with the calibration curve, and a statistical analysis is used to find the standard deviation of the concentration. 

Use a spreadsheet to compare the masses of (a) TRIS (121 g/mol), (b) Na2C03 (106 g/moI), and (c) Na2B407 10H2O (381 g/mol) that should be taken to standardize an approximately 0.020 molar solution of HCl for the following volumes of HCl 20.00 mL, 30.00 mL, 40.00 mL, and 50.00 mL. If the standard deviation associated with weighing out the primaiy-standard bases is O.I mg, use the spreadsheet to calculate the percent relative standard deviation that this uncertainty would introduce into each of the calculated molarities. 

Our major objective of this text is to provide a thorough background in those chemical principles that are particularly important to analytical chemistry. Second, we want students to develop an appreciation for the difficult task of judging the accuracy and precision of experimental data and to show how these judgments can be sharpened by the application of statistical methods. Our third aim is to introduce a wide range of techniques that are useful in modern analytical chemistry. Additionally, our hope is that with the help of this book, students will develop the skills necessary to solve analytical problems in a quantitative manner, particularly with the aid of the spreadsheet tools that are so commonly available. Finally, we aim to teach those laboratory skills that will give students confidence in their ability to obtain high-quality analytical data. 

Our example in polarography illustrates how a spreadsheet can be used to simulate a rather complex curve, in this case reflecting the interplay between the Nernst equation, Fick s law of diffusion, and drop growth. The first two factors also play a role in cyclic voltammetry, where we introduce semi-integration as an example of deconvolution. 

Finally, introduce your sample by overwriting the instruction in cell C6 with the number 1, equivalent to injecting your sample at the end of the column connected to the injection port. Bingo The spreadsheet fills, showing the distribution of the species in the various plates (each plate being represented by a separate column) and at different times (in the rows, with time increasing as you move down). The top of the spreadsheet (except for cells E1 H2) will now look like Fig. 6.5-1, with numbers that depend on the value of p used. 

In this chapter we have briefly introduced user-defined functions. These allow us to extend the range of available spreadsheet functions. They work very efficiently at the level of single-cell instructions. Macros operate in a similar way, but are more effective in dealing with entire blocks of data. The next chapter shows in fair detail how to write macros, and illustrates this with many worked-out examples. Once the material in chapter 10 has been digested, writing more complicated user-defined functions (such as for section 9.3e) should not present any problems. With the facility to make your own functions and macros, there is virtually no limit to what you can do on a spreadsheet. 

The major focus of this chapter will be on macros that extend the already considerable power of the spreadsheet, by incorporating external program instructions. Starting with Excel 5, the macro language (i.e., the computer language used to encode the macro) of Excel is VBA, which is sufficiently flexible and powerful to allow the spreadsheet user to introduce additional mathematical operations of his or her own choice, operations that are not already part of the usual spreadsheet repertoire. Earlier versions of Excel used a less transparent and certainly much less powerful macro language, calledXLM, which will not be discussedhere. 

---