# Analytical Chemistry Calculations Using Lotus 1-2-3.

This month's column looks at an interesting application of
spreadsheets to chemistry calculations in the form of a text published
by A. David Adley, who teaches in the department of chemistry and
chemical technology at Dawson College, Montreal. This book represents a
very serious effort to do the double task of teaching the student to use
a spreadsheet and at the same time apply spreadsheets to perform the
fundamental analytical chemistry calculations. The two aspects are
reviewed separately.

The Spreadsheet User's Perspective

The book stores are overflowing with books designed to teach people how to use Lotus. Most of them appear to be aimed at the business world and their popularity indicates that either the Lotus manuals are inadequate or that many people work with pirated copies, which of course have no manuals. Adley's book is designed for the scientific reader and uses examples to which chemists can relate. It also assumes the availability of economical versions which enable each reader to own a legitimate copy of Lotus complete with a manual.

The introduction will get the beginner started. From that point onward, new techniques are introduced with each application. These enable the reader to quickly gain proficiency in preparing spreadsheets to perform chemical calculations and plot the results. Extensive use is made of macros, those little miracle programs within the main program that make spreadsheets run as if by magic. It is a proficiency in writing macros that separates the power user from the general user. While the title and contents refer to Lotus, the techniques and spreadsheets will, for the most part, work with other spreadsheet programs. If an incompatibility problem appears, it will likely be with the macros as the other spreadsheets are forced to develop different menus and commands to avoid infringing upon the Lotus copyright. By consulting the manual for the other spreadsheet, it should be possible to modify or generate an alternate macro.

The sections and examples related to statistical analysis were very well done. If this material was segregated into a single volume, it would stand on its own. From my point of view, I saw this as a spreadsheet statistics manual with analytical chemistry examples. It is one of those rare manuals that recognizes the differences between population and sample standard deviations and shows how to get the sample standard deviation from the Lotus @STD function. Linear regression a nalysis is built into Lotus, yet few users get to master it. It is well handled here along with variety of non-linear regressions, all of them accompanied by meaningful examples. Among the statistical tests covered are the Q test for rejecting outliers, the Student "t" test for comparing means and the F test for comparing precision.

I like Adley's book. It had a lot more useful information than several other manuals claiming to cover scientific, engineering or statistical calculations. If I had to make a negative comment, it would be that the title might not attract enough readers. The statistics within this book will be of value to anyone who does calculations with spreadsheets and wants to incorporate some statistics into them. I would certainly like to incorporate some of them into my work.

I believe that many teachers may initially want their students to create the templates using only the instructions given in the book. In this way the skills of spreadsheet programming will be more rapidly acquired. On the other hand, experienced users will want to have both the book and diskette.

Therefore, there are two purchase options for single copies. 1) Book alone, $64 plus GST; 2) Book and diskette (either a single 3.5-in. or two 5.25-in.), $112 plus GST. Quebec residents should add $9.59 PST.

Price discounts are applicable to a volume purchase to make it feasible for course adoption. Contact Datrex Publications for more information.

The Chemistry Instructor's Perspective

Quantitative chemical analysis continues to expand as new methods and instrumentation are developed. Any tool that leads to more efficient learning of the fundamentals of analytical chemistry is to be welcomed by the student and instructor alike. The personal computer has now become a ubiquitous tool for the student of chemistry primarily through word processing software and increasingly with the electronic spreadsheet that allows efficient construction of data tables, data reduction by incorporating formulas, and modelling through "what if" parameter adjustments. Adley's Analytical Chemistry Calculations Using Lotus 1-2-3 is an extensive set of spreadsheet templates containing example calculations that will be greatly appreciated by the student, instructor, and practitioner of analytical chemistry. Several of the templates will also be of interest to those involved with physical chemistry laboratory courses. These spreadsheets encompass the type of calculations frequently made in the learning and practice of chemical analysis including those calculations applied in gravimetric, volumetric and potentiometric methods as well as those associated with quantitative chromatographic, photometric methods and the statistical analysis of experimental results.

Adley's textbook is a hands-on manual in 38 short chapters, each built around one or more templates that provide an easy way to harness the power of Lotus to help learn the fundamentals of analytical chemistry through numerical problem solving. The value of these templates extends beyond the study of the illustrative examples since users can substitute their own results.

Adley's first chapter uses a template consisting of nine example calculations commonly encountered in elementary gravimetric and volumetric analysis. The graphing capability is introduced in Chapter 2 by examining the calculation for the way affects the abundances of the various species derived from polyprotic acids like EDTA. Other aspects of EDTA titrations are presented later in the manual. Calculations concerning solubilities based on solubility products, effects of common ions and pH, and the calculation of activity coefficients are presented in chapters 3 and 6. pH calculations involving hydrolysis, polybasic acids, buffer solutions and the common titrametric methods are treated in chapters 7 through 12 with most of the templates providing for graphing. Many of these also contain small databases of the Cequilibrium constants (|K.sub.a~'s, |K.sub.b~'s, |K.sub.sp~'s, etc.) for the substances often used for these types of textbook problems.

Also included in the manual are chapters covering equilibrium concentrations using iteration methods, non-linear regression analysis for fitting calibration curves showing deviations from Beer's law, titration curves for compatible mixtures showing multiple endpoint, end-point determination by second derivative analysis and use of the Gran plot method, potentiometric titrations, numerical aspects of photometric analysis and quantitative gas chromatography as well as some applications of matrix algebra and determinants to analytical calculations. Adley also includes material on the creation and management of a database using Lotus. The examples enable on to learn the rudiments of database construction, sorting, extraction, and statistical analysis of selected data.

There are a small number of typographical errors in the text explaining the structure of the templates which are annoying when they refer to particular spreadsheet cells. Otherwise, this is an extremely valuable manual. The spreadsheet templates, with their accompanying explanations, can increase the efficiency of student learning as well as aid the practicing analyst in data handling. It might be difficult for the beginning student to make use of some of the templates at the start of the introductory analytical chemistry course, but most examples should be readily understood by the end of the semester's work.

Both reviewers' perspective

We both found this to be an excellent manual for a wide spectrum of academic and non-academic users, but had one major concern. There is an accompanying disk with all the templates. This is available at an additional cost. We felt that it should be included as part of the package.

Analytical Chemistry Calculations Using Lotus 1-2-3. A. David Adley, Datrex Publications, 5705 Wentworth Street, Montreal, QC H2W 2S2; Tel: 514-489-0335; Fax: 514-489-0335.

The Spreadsheet User's Perspective

The book stores are overflowing with books designed to teach people how to use Lotus. Most of them appear to be aimed at the business world and their popularity indicates that either the Lotus manuals are inadequate or that many people work with pirated copies, which of course have no manuals. Adley's book is designed for the scientific reader and uses examples to which chemists can relate. It also assumes the availability of economical versions which enable each reader to own a legitimate copy of Lotus complete with a manual.

The introduction will get the beginner started. From that point onward, new techniques are introduced with each application. These enable the reader to quickly gain proficiency in preparing spreadsheets to perform chemical calculations and plot the results. Extensive use is made of macros, those little miracle programs within the main program that make spreadsheets run as if by magic. It is a proficiency in writing macros that separates the power user from the general user. While the title and contents refer to Lotus, the techniques and spreadsheets will, for the most part, work with other spreadsheet programs. If an incompatibility problem appears, it will likely be with the macros as the other spreadsheets are forced to develop different menus and commands to avoid infringing upon the Lotus copyright. By consulting the manual for the other spreadsheet, it should be possible to modify or generate an alternate macro.

The sections and examples related to statistical analysis were very well done. If this material was segregated into a single volume, it would stand on its own. From my point of view, I saw this as a spreadsheet statistics manual with analytical chemistry examples. It is one of those rare manuals that recognizes the differences between population and sample standard deviations and shows how to get the sample standard deviation from the Lotus @STD function. Linear regression a nalysis is built into Lotus, yet few users get to master it. It is well handled here along with variety of non-linear regressions, all of them accompanied by meaningful examples. Among the statistical tests covered are the Q test for rejecting outliers, the Student "t" test for comparing means and the F test for comparing precision.

I like Adley's book. It had a lot more useful information than several other manuals claiming to cover scientific, engineering or statistical calculations. If I had to make a negative comment, it would be that the title might not attract enough readers. The statistics within this book will be of value to anyone who does calculations with spreadsheets and wants to incorporate some statistics into them. I would certainly like to incorporate some of them into my work.

I believe that many teachers may initially want their students to create the templates using only the instructions given in the book. In this way the skills of spreadsheet programming will be more rapidly acquired. On the other hand, experienced users will want to have both the book and diskette.

Therefore, there are two purchase options for single copies. 1) Book alone, $64 plus GST; 2) Book and diskette (either a single 3.5-in. or two 5.25-in.), $112 plus GST. Quebec residents should add $9.59 PST.

Price discounts are applicable to a volume purchase to make it feasible for course adoption. Contact Datrex Publications for more information.

The Chemistry Instructor's Perspective

Quantitative chemical analysis continues to expand as new methods and instrumentation are developed. Any tool that leads to more efficient learning of the fundamentals of analytical chemistry is to be welcomed by the student and instructor alike. The personal computer has now become a ubiquitous tool for the student of chemistry primarily through word processing software and increasingly with the electronic spreadsheet that allows efficient construction of data tables, data reduction by incorporating formulas, and modelling through "what if" parameter adjustments. Adley's Analytical Chemistry Calculations Using Lotus 1-2-3 is an extensive set of spreadsheet templates containing example calculations that will be greatly appreciated by the student, instructor, and practitioner of analytical chemistry. Several of the templates will also be of interest to those involved with physical chemistry laboratory courses. These spreadsheets encompass the type of calculations frequently made in the learning and practice of chemical analysis including those calculations applied in gravimetric, volumetric and potentiometric methods as well as those associated with quantitative chromatographic, photometric methods and the statistical analysis of experimental results.

Adley's textbook is a hands-on manual in 38 short chapters, each built around one or more templates that provide an easy way to harness the power of Lotus to help learn the fundamentals of analytical chemistry through numerical problem solving. The value of these templates extends beyond the study of the illustrative examples since users can substitute their own results.

Adley's first chapter uses a template consisting of nine example calculations commonly encountered in elementary gravimetric and volumetric analysis. The graphing capability is introduced in Chapter 2 by examining the calculation for the way affects the abundances of the various species derived from polyprotic acids like EDTA. Other aspects of EDTA titrations are presented later in the manual. Calculations concerning solubilities based on solubility products, effects of common ions and pH, and the calculation of activity coefficients are presented in chapters 3 and 6. pH calculations involving hydrolysis, polybasic acids, buffer solutions and the common titrametric methods are treated in chapters 7 through 12 with most of the templates providing for graphing. Many of these also contain small databases of the Cequilibrium constants (|K.sub.a~'s, |K.sub.b~'s, |K.sub.sp~'s, etc.) for the substances often used for these types of textbook problems.

Also included in the manual are chapters covering equilibrium concentrations using iteration methods, non-linear regression analysis for fitting calibration curves showing deviations from Beer's law, titration curves for compatible mixtures showing multiple endpoint, end-point determination by second derivative analysis and use of the Gran plot method, potentiometric titrations, numerical aspects of photometric analysis and quantitative gas chromatography as well as some applications of matrix algebra and determinants to analytical calculations. Adley also includes material on the creation and management of a database using Lotus. The examples enable on to learn the rudiments of database construction, sorting, extraction, and statistical analysis of selected data.

There are a small number of typographical errors in the text explaining the structure of the templates which are annoying when they refer to particular spreadsheet cells. Otherwise, this is an extremely valuable manual. The spreadsheet templates, with their accompanying explanations, can increase the efficiency of student learning as well as aid the practicing analyst in data handling. It might be difficult for the beginning student to make use of some of the templates at the start of the introductory analytical chemistry course, but most examples should be readily understood by the end of the semester's work.

Both reviewers' perspective

We both found this to be an excellent manual for a wide spectrum of academic and non-academic users, but had one major concern. There is an accompanying disk with all the templates. This is available at an additional cost. We felt that it should be included as part of the package.

Analytical Chemistry Calculations Using Lotus 1-2-3. A. David Adley, Datrex Publications, 5705 Wentworth Street, Montreal, QC H2W 2S2; Tel: 514-489-0335; Fax: 514-489-0335.

Printer friendly Cite/link Email Feedback | |

Author: | Silbert, Marvin D.; Walker, Leonard G. |
---|---|

Publication: | Canadian Chemical News |

Article Type: | Book Review |

Date: | Mar 1, 1993 |

Words: | 1300 |

Previous Article: | Development of a first-year chemistry course with emphasis on environmental chemistry. |

Next Article: | We need a stronger lobby. |

Topics: |