Spreadsheet Applications in Chemistry Using

This book illustrates how EXCEL can be used to help scientists process, analyze, and present scientific data. EXCEL provides a flexible environment that can be used to prepare spreadsheet solutions for scientific data processing problems. It demonstrates step-by-step how to program EXCEL to perform appropriate tasks, automate repetitive data processing tasks, and prepare integrated documents by transferring data and graphics. It covers problems such as how to perform linear regression and use the built-in statistical tools of EXCEL, as well as addressing advanced topics such as graphing complex equations, model building and the use of VBA and macros for user interface. Though the examples emphasize chemical applications, the information provided will prove useful to all scientists.

Dermot Diamond, PhD, Senior Lecturer at the Dublin City University School of Chemical Sciences, is currently the Director of the DCU Biomedical and Environmental Sensor Technology (BEST) Centre.<br> <br> C. A. Hanratty, PhD, has lectured at Trinity College Dublin and Dublin City University in physical chemistry and computer applications in chemistry. Currently, she is a software support specialist with Digital Equipment Ireland, Ltd.

Preface

vii

(3)

Acknowledgments

x

(1)

Using the Diskette

xi

1 Introduction to Excel

1

(38)

1.1 What Is a Spreadsheet?

1

(1)

1.2 Fundamental Spreadsheet Operations

2

(12)

1.2.1 The Spreadsheet and the Excel Workspace

2

(2)

1.2.2 Excel Commands

4

(3)

1.2.3 Moving around the Spreadsheet

7

(1)

1.2.4 Cell References

8

(1)

1.2.5 Ranges and Arrays

9

(1)

1.2.6 Entering Data

10

(2)

1.2.7 Editing Data

12

(2)

1.3 Mathematical Operations on Cells--Entering Formulas

14

(7)

1.3.1 Formulas

14

(2)

1.3.2 Function Wizard

16

(4)

1.3.3 User-Defined Functions

20

(1)

1.4 Graphing

21

(17)

1.4.1 Chart Wizard

21

(6)

1.4.2 Graphing Mathematical Functions

27

(2)

1.4.3 Editing a Graph

29

(3)

1.4.4 Inserting a Trendline

32

(6)

1.5 Printing

38

(1)

1.6 Summary

38

(1)

2 Statistical Functions and Regression Analysis

39

(26)

2.1 Statistical Functions of the Function Wizard

39

(3)

2.2 Statistical Functions from Data Analysis ToolPak

42

(5)

2.3 Linear and Nonlinear Regression

47

(1)

2.4 The LINEST Function

48

(3)

2.5 Linear Regression: Access Via Data Analysis_Tools

51

(2)

2.6 Linear Regression Using Insert Trendline

53

(4)

2.7 Inserting Error Bars

57

(4)

2.8 Non-Linear regression--Modeling Potential Relaxation in Membrane Electrodes

61

(3)

Reference

64

(1)

3 Introduction to Macros and VBA

65

(32)

3.1 Macros

65

(10)

3.1.1 Formatting Macro

66

(4)

3.1.2 Calculation Macro

70

(5)

3.2 Introduction to Visual Basic for Applications

75

(7)

3.2.1 Code of the Formatting Macros

76

(3)

3.2.2 Editing Macros

79

(1)

3.2.3 Code of the Calculation Macros

80

(2)

3.3 Functions

82

(2)

3.4 Solid-State Chemistry Examples Using VBA

84

(13)

3.4.1 Calculation of Bond Distances and Angles

84

(5)

3.4.2 Calculation of Crystallographic Angles

89

(8)

4 Case Studies in Quantum Chemistry

97

(16)

4.1 Quantum Leaks

97

(4)

4.2 Plot of the Radial Distribution Function

101

(4)

4.3 Molecular Structure

105

(8)

4.3.1 Molecular Orbitals

105

(2)

4.3.2 Electron Densities

107

(6)

5 Case Studies in Physical Chemistry

113

(34)

5.1 Introduction

113

(1)

5.2 Activity and Concentration

114

(3)

5.2.1 Background

114

(3)

5.3 Kinetics

117

(7)

5.3.1 Introduction

117

(1)

5.3.2 Arrhenius Equation--Suggested Method

118

(1)

5.3.3 First- and Second-Order Reactions

119

(4)

5.3.4 Suggested Extension

123

(1)

5.4 Metal-Complex Equilibria

124

(5)

5.4.1 General Background

124

(1)

5.4.2 Generalized Metal-Complex Equations

124

(5)

5.5 Titration Curves

129

(6)

5.5.1 Introduction

130

(1)

5.5.2 Derivation of Equations Used

130

(4)

5.5.3 The Derivative Curve

134

(1)

5.6 Gran's Plot

135

(2)

5.6.1 Advantages of Gran's Plot

135

(2)

5.6.2 Background

137

(1)

5.7 Titrations Involving Polybasic Acids

137

(8)

5.8 Conclusion

145

(1)

Reference

146

(1)

6 Importing and Processing Instrumental Data

147

(25)

6.1 Introduction

147

(1)

6.2 Importing Instrumental Data

148

(1)

6.3 Importing TEXT (ASCII) Files

148

(6)

6.4 Spectral Data Processing

154

(1)

6.5 Digital Averaging Techniques

155

(7)

6.5.1 Moving Average Filter

156

(1)

6.5.2 The Savitzky-Golay Filter

157

(4)

6.5.3 Suggested Additional Activities

161

(1)

6.6 Feature Removal through Integration

162

(1)

6.7 Feature Enhancement through Differentiation

163

(6)

6.8 Conclusion

169

(2)

References

171

(1)

7 Processing Experimental Data Using Solver

172

(42)

7.1 Introduction

172

(1)

7.2 Using Solver

173

(8)

7.3 Case Study: Deconvoluting Two Overlapping Gaussian Peaks

181

(2)

7.4 Fitting Experimental Data

183

(1)

7.5 Case Studies Involving Experimental Data

183

(29)

7.5.1 Modeling Chromatography Peaks

183

(29)

7.5.2 Modeling Fluorescence Decay Processes

190

(7)

7.5.3 Modeling of Ion-Selective Electrode (ISE) Dynamic Response in Flow-Injection Analysis

197

(4)

7.5.4 Modeling the Nikolskii-Eisenman Equation

201

(2)

7.5.5 Determining Rate Constants for a Ligand Replacement Reaction

203

(4)

7.5.6 Michaelis-Menten Enzyme Kinetics

207

(5)

7.6 Conclusion

212

(1)

References

213

(1)

8 Case Studies Involving VBA

214

(23)

8.1 Titration of a Strong Acid with a Strong Base

214

(12)

8.2 Buffer Solutions

226

(5)

8.3 The Marcus Treatment of Electrochemical Kinetics

231

(6)

8.3.1 Surface Immobilized Redox Couples

231

(6)

Appendix A

237

(4)

Index

241

What is included with this book?

The New copy of this book will include any supplemental materials advertised. Please check the title of the book to determine if it should include any access cards, study guides, lab manuals, CDs, etc.

The Used, Rental and eBook copies of this book are not guaranteed to include any supplemental materials. Typically, only the book itself is included. This is true even if the title states it includes any access cards, study guides, lab manuals, CDs, etc.

FREE SHIPPING

Amazon no longer offers textbook rentals. We do!

Spreadsheet Applications in Chemistry Using Microsoft Excel

Summary

Author Biography

Table of Contents

Supplemental Materials