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

Order your essay today and save **15%** with the discount code: VACCINE