QUICK FIND

Below are hyperlinks to electronic spreadsheets. Different browsers will open these files in different ways (often with a security warning). The files were prepared using Microsoft Excel, but can be opened with a variety of different spreadsheet tools. Instructions for using these files are contained in the window entitled "Instructions". Click here for additional models!

Spreadsheet Models (.xls files)

Model 1: Weak Acid Titration

Model 2: Weak Acid Titration Solution

Model 3: Copying Formulas

Model 4: Copying Formulas Solution

Model 5: Plotting Data

Model 6: Plotting Data Solution

Instructions

The "Laboratory" tab gives you a sense of what it might be like to perform an experiment in a laboratory. "Models" gives you the ability to extend this knowledge by building your own models of such simulations. You can work in Virtlab without building such models, but we hope that you will give it a try. You may need to step through these instructions several times before you are comfortable with the process, but the rewards will stay with you over many endeavors (in and out of science) throughout your life. Electronic spreadsheet programs are designed and written by companies that want to sell their products to the largest possible audience. They are designed to be easy to use. All electronic spreadsheets work essentially the same way. To build a model you must - Point to a cell.
- Write into a cell.
- Repeat on a new cell.
You can build any model using these steps, but there are shortcuts. For example, often you will find it convenient to generate tables of data by placing the same (or very similar) formula in an entire column of cells. Electronic spreadsheets have special commands that permit you to copy the contents of one or more cells to new locations. Therefore, you can clone formulas as many times as you wish. You can clone identical copies of formulas, or you can instruct your program to make subtle changes in the formula to reflect its new location in the spreadsheet. Although we will hint at some of these features in the material that follows, we leave it to you to extend your knowledge of your specific spreadsheet program by exploring the tutorial that was provided with the software. We don't use many of the advanced features of spreadsheets in Virtlab (e.g., database access) and so you should be able to pick up the basics in a few hours if you don't already have them. Microsoft Excel is the most widely used spreadsheet program, and spreadsheets from other manufacturers are often crafted to look as much like Excel as possible. We will build our worksheets using Excel features and syntax.
(Items 1 and 2 can be found on the "Laboratory" tab.) 3. Later in Virtlab, you will explore the dissociation of a weak acid as an example of a simple but very important equilibrium relationship:
HA can dissociate into H One equation that can predict the concentrations of the ionic species in this equilibrium is the Henderson–Hasselbalch equation (2):
where pH is –log[H Let's use Equation 2 to build a simple model capable of exploring the behavior of weak acids and bases. Near the upper-left of this page you should see some hyperlinks in a box titled "Spreadsheet Models". One of these hyperlinks is labeled "Model 1: Weak Acid Titration". - Click on the Model 1 hyperlink
to download this model.
Depending on your operating system
and settings, the model may open immediately or you may need to locate and open
it after it has downloaded. (You may need to reorganize your screen to display the model and
these instructions simultaneously. You can also download these instructions by clicking on the hyperlink
in the first line of these instructions.) Model 1 is a very simple model that calculates pH from the
pK of a weak acid and the concentrations of its dissociated and undissociated forms.
Although you can easily perform these calculations on a pocket calculator, this
model is a good place to learn about elementary spreadsheet manipulations.
BEFORE YOU GO ANY FURTHER BE SURE THAT YOUR SPREADSHEET IS NOT CONFIGURED FOR WHAT IS CALLED "R1C1" REFERENCE STYLE. IF YOU ARE USING MICROSOFT EXCEL 2003 GO TO THE "TOOLS" MENU AND SELECT THE "OPTIONS" ITEM. IN THE "SETTINGS" REGION OF THE "GENERAL" TAB BE SURE THAT THERE IS NO CHECKMARK IN FRONT OF "R1C1 REFERENCE STYLE". THIS FEATURE SHOULD BE TURNED OFF. Part of the model has already been built for you. All spreadsheets consist of cells. In many spreadsheets the cells are identified by their column-row intersection. In our system the columns are labeled A, B, C, ... and the rows are labeled 1, 2, 3, ... . Cells A1 to A3 contain labels that identify the values of Cells B1 to B3. Cell B1 contains the value of [A] (the molar concentration of A), Cell B2 contains the value of [HA], and cell B3 contains the value of pK. Cell A5 contains the label pH, but cell B5 has been left blank. If cell B5 contained the formula "=B3+LOG10(B1/B2)", cell B5 would always display the pH of a weak acid defined by the contents of cells B1 - B3. Cells A1 - B5, taken together, would serve as a model of the behavior of a weak acid!. Enter this formula (or its equivalent for your spreadsheet program) into cell B5. (If cell B5 displays anything other than a number close to 5.20, return to the CAPITALIZED PARAGRAPH above and try again.) "Model 2: Weak Acid Titration Solution" is our solution and you should open it to verify that your model yields the same results. Solution files are usually locked so that you can't accidentally change the formulas. If you wish to change some feature of the solution simply go to the "Tools" menu and choose the "Protection" item to turn off this feature. - When strong acid is added to a
solution of the salt of a weak acid, the added protons H
^{+}can combine with A^{-}to form HA (therefore increasing [HA] and decreasing [A^{-}]). Verify that the change in pH with changes in [HA] (or [A^{-}]) is not linear by completing the table below and plotting the results. Of course, you can also open "Laboratory Notebook" and complete the table there. (The laboratory notebook that opens when you are in "Models" is not the same notebook that opens when you are in "Experiments".)
pK = 4.8 [A-] [HA] pH .20 .01 .19 .02 .18 .03 .17 .04 .16 .05 .15 .06 .14 .07 .13 .08 .12 .09 .11 .10 .10 .11 .09 .12 .08 .13 .07 .14 .06 .15 .05 .16 .04 .17 .03 .18 .02 .19
Can you identify a range of pH values where the addition of strong acid will change pH the least? [Answer] In the next section, you see how this model can be changed and your spreadsheet software used to plot [HB] vs pH automatically.
4. Before moving on, however, take one last look at Model 1. There is a powerful technique for building formulas that can help eliminate typing errors. Instead of typing the formula, try this (if you are using Excel): Position your cursor at cell B5 and type the single character =. Now use your arrow keys (or mouse) to move the cursor to cell B3 where the pK value is located. Do you see what's happening in cell B5? Once you start the formula by entering =, Excel knows your intentions and you can enter cell addresses by "pointing" instead of typing. Continue with the keystrokes +LOG10( and then "point" to cell B1, type /, "point" to cell B2, type ), and finally press <ENTER>. Building formulas in this way can reduce your errors because you are fetching cell addresses in an intuitive fashion. Most spreadsheets have a similar feature. 5. Although the cycle of pointing to a cell, writing into a cell, and repeating on a new cell ultimately builds a model of any complexity, an electronic spreadsheet program has features that ease the task of building large models. You may want to learn about all of them by reading your program's instruction manual. In this experiment, study one feature that you will use frequently: the COPY command. - Click on the hyperlink "Model 4:
Copying Formulas Solution" and launch the worksheet it represents. There are several
features of this model that you should notice:
* The model is organized as a table of values, beginning at row 7, that is driven by a parameter table located in cells A1 - B5. * The table itself uses multiple copies of the Henderson-Hasselbalch equation (column C) to compute the pH of the solution from a range of values of [A] (column A), [HA] (column B), and the pK of the weak acid (cell B1). * The range of values of [A] and [HA] in columns A and B are calculated and they always sum to the concentration of total acid (cell B2). Take a moment to explore the model and its structure and features. If you look carefully you will see that some of the formulas contain a special character "$". This character makes it possible for you to copy formulas to new locations such that some cell references never change during the copy while others change relative to the location of the formula that uses them as arguments (e.g., one cell to the left). For example, look at cells A9 - A46. These cells sum two numbers but the first argument changes with each row while the second argument does not (it is always B5). By prefacing "B" and "5" with the special character "$" (i.e., $B$5) the system knows that "B" and "5" should not change when this formula is copied and pasted to a new location or locations. - Click the hyperlink "Model 3: Copying
Formulas" and launch the worksheet it represents. Model 3 is the same as Model 4
except that we have deleted rows 10 - 46. It will be your job to replace those rows.
First, column A. Cell A9 contains the formula "=A8 + B5". "A8" refers to the cell in the previous row and is a relative reference. "B5" on the other hand is an absolute reference because it always refers to the the same cell in the parameter table. Rewrite the formula in cell A9 as "=A8 + $B$5" and then copy and paste it into cells A10 - A46. The column of formulas should now be of the form: =A8 +$ B$5 =A9 +$ B$5 =A10 +$ B$5 =A11 +$ B$5 ... - Complete Model 3 using the same technique and compare your results with our solution, Model 4.
6. Finally, let's plot the table of data generated in Item 5. In Virtlab we almost always use what are called xy (or scatter) plots. There are so many charting features in spreadsheet programs that we won't cover them here. We will usually build the chart for you when you need one. However, you are invited to build your own whenever you wish. We will, however, try a simple one (assuming you are running Excel 2003). - Click on the hyperlink "Model 5:
Plotting Data" and launch the worksheet it represents.
Use your mouse to select all of the cells from B8 - C46. - Select the "Chart" item in the "Insert"
menu.
Select the chart type"XY (Scatter)" and the chart sub type "Scatter with data points connected by smooth lines". Click "Next". Make certain that "Series In" is "Columns" and click "Next" again. - In the "Titles" tab enter "pH vs [HA]"
for "Chart Title", "[HA]" for "Value (x) axis", and "pH" for "Value (y) axis".
In the "Legend" tab uncheck "Show legend". Click "Next". Make certain that "Place chart" is set to "As object in" and then click "Finish". That's it. You should have your chart. - Compare your results with "Model 6: Plotting Data Solution".
There are many charting features available in spreadsheet programs and we will, occasionally, use features not mentioned here. We'll discuss them as we get to them, but you may want to take a moment to read up on spreadsheets and charting in your instruction manual. 7. You've now covered the basics of the Virtlab environment. We hope you're excited about learning chemistry "by discovery". Please register with the site for simulations involving stoichiometry, ideal gas law, solutions, acid-base equilibria, and more. If you encounter problems or see opportunities where we can improve this product write our editor at dbarkley@virtlab.com. Above all, have fun! |