ENV* K105

Assignment #7: Curve Fitting & Solver
Part I Due 10/18/05

In this assignment you will do two unrelated things: (1) You will work with linear relationships, using the forecast() function to predict values and using Insert, Trendline..., to put a line on a graph. (2) You will use the solver to create a calculator sheet that can solve an equation that cannot be solved algebraically.

0. Read this assignment.

Part I: Curve Fitting

1. Read Chapter 7 in Liengme. Work Exercises 1, 2, and 3 and Problems 1 (a) and 1 (b). For problem one, there's a hint if you need it.
2. Save the worksheet titled "ConductivityTDSData.xls" to your I: drive (click here if you would like to review how to save from a link.) Open the saved workbook and fill in the last column ("Predicted TDS") using the Forecast() function.
• The Forecast() function takes three arguments:
• The X value: This would be the conductivity value you wanted to predict TDS from.
• The range of known Y values: These have the units of whatever you are looking for. In this case they are the range of TDS values.
• The range of known X values: These have the same units as your X. In this case they are the range of conductivity values.
• Be sure to set the known Y and known X ranges using absolute references (with the $'s).
• Add cells that give the slope and intercept of the linear regression line that has conductivity as the X values and TDS as the Y values.
3. Create an X-Y Graph (scatter plot) of the Conductivity and TDS values. Add a linear trendline. Label the graph, the axes, and the data series appropriately.
4. Print the appropriate sections of the worksheets you have worked on.
5. Use WebCT to send me all your files. Please include your initials somewhere in the name of each file you turn in.

NOTE: Mid-term exam will cover up to this point.

Part II: Goal Seek and Solver

1. Work the following Goal Seek example:
• Save and open "GoalSeekinaChart.xls"
• Point at the Location E point in the chart. The tool tip (yellow box) should appear reading 'Series "Food Consumed (kg/yr)" Value: 7,665'.
• Click once and release. All the points in the series should be highlighted. Click a second time and release. The points for D and E only should be highlighted. Click a third time and hold. Drag the point down until the value of 5,910 is displayed. Release. The Goal Seek dialog box will appear asking which cell you wish to change. Point at cell B6 then click OK. How many birds are there now in Location E? (Answer: 31)
• In Liengme, read pages 192 ("Finding Roots with Goal Seek") through (stop at "Concepts: C"). There is also information on pages 877-882 of Using Office 2000 (on reserve in the Thames Valley Library).
• You don't need to print or save anything from this section (II-1).
2. Build a water chemistry calculator and use it with Solver.
• The Solver is discussed on pages 892-908 of Using Office 2000, which I have copied and placed on reserve in the library.
• Background: The pH of a solutions is defined as follows:
• pH = -log [H⁺] with
  [H+] = the hydrogen ion concentration in moles per liter (M)
• In a solution consisting of a weak acid and the salt of a weak acid (eg, an acetic acid/sodium acetate solution), the hydrogen ion concentration obeys the following formula:

(equation 1)

• where:
• C_T = [acid] + [salt], ie, the total amount of acid and salt added to the solution.
• [salt] = the amount of salt added to the solution.
• All the values in equation 1 are constants or properties that are manipulated in the solution, except for [H+]. If you solve equation 1 for [H+], you will predict the pH of the solution. However, the equation cannot be solved using algebra. Excel's mighty Solver can do the job! Note: Check the Tools menu for the Solver... command. If it's not there, let me know and I'll walk you through adding it.
• Build the calculator.
• Save and open the file "weakAcid.xls". Under the Calculation tab of Tools, Options..., set Calculation to Manual. This will keep you from getting strange error messages while you set up the calculator.
• Name the cells you will be using in your calculations. For example, name cell C3 "KA"; name cell C4 "acid"; etc. To name a cell or a range, highlight it then choose Insert, Name>, Define.... Note: You can't use the plus sign ("+") in a range name, so you might name the cell giving [H+] concentration something like "Hion". Remember that when you are in a named cell, the name appears in the address box to the left of the formula bar.
• Type in values for K_A, [acid] and [salt]. Start with K_A equal to 1.76*10^-5 and [acid] and [salt] equal to 1. Enter a formula to calculate C_T as [acid] plus [salt] divided by 1000 (the values in cells C4 and C5 are entered in mmolar and the calculation requires molar values). Note: To enter values in scientific notation, type in the value before the times sign (eg "1.76") then type the letter "e" and the exponent on the 10 (eg, "e-5").
• Type in a value for [H⁺]. Start with 10^-7 ("1e-7"). Type in a formula to set pH equal to -log([H+]). Reformat cell C10 to number format with two decimal places. The pH should now be "7.00". Your spreadsheet should look like the first picture in "WeakAcidPictures.doc".
• So far nothing special has happened. Now you need to type a formula in cell C12 that corresponds to the right hand side of equation 1. Be sure to divide the value of "salt" by 1000 to convert it to molar units. If you type the formula in correctly, the cell will display a value of 0.0009887. Remember to use parentheses as needed to maintain the correct order of operations.
• Use solver to calculate pH. (Note: You need to be sure the Solver is installed before you can use it. If Solver... is not on the Tools menu, choose Tools, Add-ins..., and scroll down to Solver Add-in. Check the box and click OK.)
• With cell C12 active, choose Tools, Solver.... The Solver dialog box opens.
• Be sure that the Target Cell is $C$12. Set "Equal to: Value of:" to 0.
• Set "By Changing Cells:" to Hion by typing or pointing.
• Hit Solve. Choose to Keep Solver Solution.
• Let's spruce the calculator up a bit.
• Hide the cells B12 and C12. The user of the calculator doesn't need to see them. Highlight the cells. Choose Format, Cells..., Number, Custom, then type ";;;" in the type box.
• Format all the cells (A1:D12) in the calculator to have a light gray background (Format, Cells..., Pattern).
• Select the three cells that require user input (C3:C5). Format them to have a no color pattern and with nothing checked on the Format, Cells..., Protection tab.
• Under Tools, Protection>, choose Protect Sheet.
• Under the View tab of Tools, Options..., uncheck gridlines.
• Your worksheet should look like the third picture in "weakAcidPictures.doc".
• Fine tune the calculator.
• Set K_A equal to 6.31*10^-8 (type this as 6.31e-8), [acid]=20 mmolar and [salt]=100 mmolar. This corresponds to a mixture of 20 mM NaH₂PO₄ and 100 mM Na₂HPO₄.
• Try using Solver to get the new pH. You will encounter two problems. First, you cannot use Solver on a worksheet which is protected. Before you can modify the worksheet you have to choose Tools, Protection>Unprotect Sheet. Now use the Solver.
• You get a #NUM! Error for the pH because Solver came up with a negative value for the [H⁺] concentration. Since the pH of any solution we are likely to be looking at with the calculator will most likely be less than 14, set a Constraint in solver that $C$8>=1e-14. Try again.
• Print this result and save the file for me and for you. You will make this really fancy next time by creating macros and control buttons. You can read Chapter 8 in Liengme and Chapters 41 and 42 in Special Edition Using Microsoft Office 2003 to get psyched up for that.