Spreadsheet buffer titration
Spreadsheet titrations create buffer tables and aid in understanding buffers. The following simple model was created with Lotus 1-2-3. At the top of the table is the pKa of the buffer (acetic acid, obtained from tables). In the cell to the right is the starting acid concentration (0.1M) and to the right is the increment of base to be added, 0.001M. The first column contains the starting amount of acid, the 2nd contains the amount of base added (increasing amounts), the 3rd column contains the resulting acid conc. (subtract the 2nd column from the first) and the 4th column plugs values of pKa, acid and base into the Henderson Hassebalch equation.
Sample spreadsheet formulae
Resulting numbers
Graph of pH vs. added base (titration)
This model does not correct for ionic strength. For monovalent buffers the calculations should be good enough. This spreadsheet can be used for any acid by substituting pKa into the appropriate cell (A3 in this case). Similarly one can vary the starting acid concentration and the increment. One can create more sophisticated tables by added additional columns for ionic strength corrections. It's also instructive to create a column displaying DpH displaying differences between adjacent values of column 4. This will show graphically how buffering capacity varies with pH.
