CHP Home GenChem     Analytical     Instrumentation     Index   

Spreadsheet Simulations for Analytical and Physical Chemistry

[ Introduction | Analytical | Physical | Data Analysis | Related Resources ]


Introduction

I have tried to shape up my Excel® simulations so they are useful to other people. Below are what I have posted so far, classified under different headings, although there is obvious overlap. These spreadsheets are freely available to anyone who desires to use them for non-commercial purposes. Please do not modify or redistribute any of these files without my permission.

I will appreciate your feedback at tissue@vt.edu.

User Notes:


Analytical

activity-coefficients.xls (26 kB)
Calculator to find ionic strength and activity coefficients.
alpha-plot-ML-complex-stepwise.xls (228 kB) new
Alpha plots (alpha fraction vs. p[L]) for metal-ligand complexes up to 1:4.
alpha-plot-diprotic.xls (97 kB)
Alpha plots (alpha fraction vs. pH) for a diprotic weak acid.
alpha-plot-triprotic.xls (142 kB)
Alpha plots (alpha fraction vs. pH) for a triprotic weak acid.
log-conc-polyprotic-acid.xls (127 kB, Excel 2000®)
Log concentration plots for triprotic, diprotic, or monoprotic weak acids.
nernst-equation.xls (20 kB, Excel 2000®)
A simple calculator to calculate Ecell for arbitrary activities in a redox reaction.
titration-strong-acid.xls (102 kB)
Titration plot of a strong acid with a strong base.
titration-weak-acid.xls (585 kB)
Titration plot of a weak acid with a strong base. Two curves are plotted to compare different Ka's or concentrations.
titration-henderson-hasselbalch.xls (189 kB)
Titration plot of a weak acid using the Henderson-Hasselbalch expression. This simulation is only valid for reasonably high acid concentrations. Spreadsheet also shows first derivative plot, Gran plot, and plot of log(V/E-V) vs. pH to find pKa.
solubility.xls (31 kB, Excel 2000®)
Solubility calculation correcting for activity effects. Does not include common-ion effects or competing equilibria.
beers-law.xls (26 kB)
Simulation of the attenuation of light through an absorbing sample. [more info] (See also the exponential decay simulations below.)
spectrum-simulation.xls (618 kB)
Simulation of up to 10 peaks in a spectrum. User enters peak position, relative intensity, and relative width of each peak.
chromatogram-simulation.xls (640 kB) updated
Simulation of up to 10 peaks in a chromatogram. User enters total time, column efficiency, and retention time and relative concentration of each component.

Physical

1s-2s-3s-radial-distributions.xls (264 kB)
Radial distribution plots (2-D) for 1s, 2s, and 3s orbital.
blackbody-radiation-vs-frequency.xls (82 kB)
blackbody-radiation-vs-energy.xls (87 kB)
blackbody-radiation-vs-wavelength.xls (87 kB)
Blackbody emission curves as a function of frequency (Hz), energy (wavenumbers), and wavelength (nm). [more info]
gases-kinetic-theory-vs-t.xls (125 kB)
Plot of the Maxwell distribution for the speed of a gas at four temperatures.
gases-kinetic-theory-vs-m.xls (125 kB)
Plot of the Maxwell distribution for the speed of four different gases at a user-selected temperature.
harmonic-oscillator.xls (1.3 MB) updated
Harmonic oscillator potential well with energy levels.
morse-potentials-3-diatomics.xls (192 kB) updated
Enter diatomic parameters to view 3 potential wells.
morse-potentials-ground-and-excited-states.xls (143 kB) updated
Plot of Morse potentials for the ground and excited states of H2.

Data Analysis

gaussian-lorentzian.xls (160 kB)
Comparison of Gaussian and Lorentzian distributions.
quadratic-solutions.xls (16 kB)
Calculator for solving quadratic and cubic equations.
transient-single-exponential.xls (50 kB)
Enter a lifetime and initial intensity to view a single exponential decay curve. Also finds the intensity at any given time for the entered lifetime. [more info]
transient-biexponential.xls (60 kB)
Enter lifetimes and initial intensities to view two exponential decay curves and their sum.
transient-single-exponential-curve-fit.xls (50 kB)
Does a nonlinear least-squares curve fit to experimental data using the lifetime, initial intensity, baseline, and start time as adjustable parameters. Also functions as a simulation by entering these parameters. [Curve fitting requires the Solver add-in under the Tools menu. It is not accessible when viewing the worksheet in a browser window.]
transient-single-exponential-integration.xls (50 kB)
Displays an exponential decay and calculates the relative area under the curve between two x positions.

Other Chemistry Spreadsheet Resources

More Chemistry Spreadsheets and Related Stuff

Books

Below are a few books on Excel®. I have not used any of them and cannot make a recommendation, but the reviews of the two books from 1997 are quite positive.

"How to Use Excel® in Analytical Chemistry and in General Scientific Data Analysis." By Robert de Levie. Cambridge University Press: Cambridge. 2001.
ISBN: 0521642825. $120.00 (hardback); ISBN: 0521644844. $45.00 (paperback).

"Spreadsheet Applications in Chemistry Using Microsoft Excel®." By Dermot Diamond and Venita C. A. Hanratty. John Wiley & Sons: New York. 1997.
ISBN: 0-471-14987-2. $34.95.

"Excel® for Chemists: A Comprehensive Guide." By E. Joseph Billo. Wiley-VCH: New York. 1997.
ISBN: 0-471-18896-4. $49.95.

Ed Vitz reviewed both of the 1997 books in J. Chem. Inf. Comput. Sci. 1997 37 961; http://pubs.acs.org/isubscribe/journals/jcisd8/jtext.cgi?jcisd8/37/i05/html/ci970391+.html.

See also: Greathouse, J. A. "Spreadsheet Applications in Chemistry Using Microsoft Excel® by Dermot Diamond and Venita C. A. Hanratty" J. Chem. Educ. 1997 74 1279; http://jchemed.chem.wisc.edu/Journal/Issues/1997/Nov/abs1279_2.html.


 Top of Page   
 Copyright © 2000-2003 by Brian M. Tissue, all rights reserved.