Description
Spreadsheet: precipitation titration curve
Chem253 (fall 2020)
It’s often very useful to create spreadsheets to simulate chemical and physical phenomena so
you can graph them and get an intuitive feel for the system behavior. When doing so, it’s
usually useful to make the output functions and graphs autorange or adapt to changing
boundary conditions for the problem.
Here, you’ll follow Harris’ instructions for making a spreadsheet to simulate a 1:1 precipitation
titration curve, like the one created by the reaction,
Ag+ + Cl- → AgCl(s)
Ksp = 1.8×10-10
(1)
Harris’ instructions are attached to this handout, and an example spreadsheet will be up on the
lab projection screen for you to study. Key features of this spreadsheet are (a) a pM scale
whose upper and lower bound values adjust with changing Ksp, and (b) a graph of pM versus
VM that changes and auto-scales with changing input parameters. These features make the
simulation adaptable to a wide range of possible Ksp values, concentrations, and volumes.
Before you begin
Read over Harris’ instructions for preparing the spreadsheet. See your instructor if you have
any questions before starting to program the spreadsheet.
The basics
• Document the spreadsheet in a sensible fashion.
•
Choose and organized way to lay out the fundamental input parameters. These are Ksp,
VX (the sample volume, where X stands for a halide), CX (the sample concentration), and
CM (the titrant concentration, where M stands for metal).
•
To make the formulas easier to enter and interpret, name the input parameter cells
according to their symbols. Do this by clicking in the little box at the top left that normally
shows a cell name and entering the variable name instead. Note: if you enter the name
incorrectly or want to change it later, go to the “Formulas” tab and use the “Name
Manager” to edit variable names.
•
Set up output columns as shown in the example spreadsheet on the projection screen.
Setting the pM range
The minimum value [M+] (maximum pM) will be fixed by the solubility equilibrium:
MX(s) → M+ + X[M+]min = Ksp / CX
Ksp = [M+][X-]
where CX is the concentration of X- in the sample
(2)
(3)
Set the first cell in the pM column to be equal to the negative log of equation 3. This sets your
maximum pM value.
The maximum value of [M+] you could ever obtain is CM. However, setting that as the maximum
in the spreadsheet would lead to titration curves that go way, way past the endpoint volume.
More practically, when [M+] reaches 50% of the original titrant concentration, the titration will be
a bit past the endpoint, and the curve will be well-defined. Now, a titration curve will look best if
it has at least 25 or so points in it. So, in a cell 25 rows below your first pM value, enter a
formula for the negative log of ½ the CM value.
Because there are 25 steps between the maximum and minimum pM values, the increment
between values can be conveniently set to equation 4:
pM = (pMmax – pMmin) / 25
(4)
You want to enter a formula in the rows between pMmax and pMmin that increments the value of
pM by pM at each row. That is, each successive row, n, should have a formula that computes,
pM(n+1) = pM(n) + pM
(5)
Compute [M+], [X-], and VM
Follow Harris’ instructions to compute [M+], [X-], and VM at each value of pM.
Create a graph of pM versus VM
To graph two columns of data that aren’t next to each other,
–
first create a graph with two adjacent columns of data
–
right click in the graph, and choose “select data”
–
select the series you want to edit, then click “edit”
–
choose the x and y data in the following dialog box and “ok” your way out
Explore some reaction conditions
Once your spreadsheet is complete, Play with the following scenarios:
1. Set Ksp to 1E-10, VX to 25 mL, and CX to 0.1 M. Examine what happens as you vary CM
between 0.01 and 1 M. Do the changes in the graph make sense to you?
2. Set VX to 25 mL, and CX and CM to 0.1 M. Examine what happens as you vary Ksp
between 1E-10 and 1E-4. Do the changes in the graph make sense to you?
3. With Ksp = 1E-4 in scenario 2, can you think of changes in CX, VX, or CM that could make
the endpoint sharper? Try out your ideas in the spreadsheet and see if you can
rationalize what you see.
Spreadsheet to calculate 1:1 metal (M) halide (X) precipitation titration curves
Approach follows Harris, QCA 9th ed., pp. 154-155
PM scale autoranges based on (high end) Ksp and highest [X] and (low end) 50% of the titrant [M]
YELLOW = user inputs
BLUE = formulas
8.0
Ksp =
Vx =
Cx =
CM =
1.00E-08
Sn
0.100
0.100
ImL
M
7.0
M
6.0
5.0
§ 4.0
3.0
2.0
1.0
0.0
20
60
80
40
VM (mL)
pM
7.00
6.77
6.54
6.32
6.09
5.86
5.63
5.40
5.18
4.95
4.72
4.49
4.26
4.04
3.81
3.58
3.35
3.12
2.90
2.67
2.44
2.21
1.98
1.76
1.53
1.30
[M]
1.00E-07
1.69E-07
2.86E-07
4.83E-07
8.16E-07
1.38E-06
2.33E-06
3.94E-06
6.66E-06
1.13E-05
1.90E-05
3.22 E-05
5.44E-05
9.19E-05
1.55E-04
2.63E-04
4.44E-04
7.50E-04
1.27E-03
2.14E-03
3.62E-03
6.13E-03
1.04E-02
1.75E-02
2.96E-02
5.00E-02
[X]
1.00E-01
5.92E-02
3.50E-02
2.07E-02
1.23E-02
7.25E-03
4.29E-03
2.54E-03
1.50E-03
8.88E-04
5.25E-04
3.11E-04
1.84E-04
1.09E-04
6.44E-05
3.81E-05
2.25E-05
1.33E-05
7.88E-06
4.66E-06
2.76E-06
1.63E-06
9.66E-07
5.71E-07
3.38E-07
2.00E-07
VM
0.00
6.41
12.04
16.42
19.54
21.62
22.95
23.76
24.26
24.57
24.75
24.86
24.94
24.99
25.05
25.11
25.21
25.37
25.64
26.09
26.88
28.26
30.77
35.61
46.00
75.00
Purchase answer to see full
attachment