Spreadsheet Simulations for Analytical and Physical Chemistry
[ Introduction | Analytical | Physical | Data Analysis | Related Resources ]
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 firstname.lastname@example.org.
- Spreadsheets were made in Microsoft Excel® '97 unless noted.
- My screen resolution is 1024x768, so you might have to reformat column widths or chart sizes for optimal use.
- Most of the variables that can be changed by the user are in gray boxes. Be careful changing other cells so that you do not overwrite formulas.
- Internet Explorer will display the spreadsheets and let you change values in data cells. To download a spreadsheet to you local disk right click on the hyperlink and use "Save Target As".
- activity-coefficients.xls (26 kB)
- Calculator to find ionic strength and activity coefficients.
- alpha-plot-ML-complex-stepwise.xls (228 kB)
- 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)
- Simulation of up to 10 peaks in a chromatogram. User enters total time, column efficiency, and retention time and relative concentration of each component.
- 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)
- Harmonic oscillator potential well with energy levels.
- morse-potentials-3-diatomics.xls (192 kB)
- Enter diatomic parameters to view 3 potential wells.
- morse-potentials-ground-and-excited-states.xls (143 kB)
- Plot of Morse potentials for the ground and excited states of H2.
- 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.
More Chemistry Spreadsheets and Related Stuff
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.
Copyright © 2000-2003 by Brian M. Tissue, all rights reserved.