Spreadsheet quadratic equation

Spreadsheet Summary In the first three exercises in Chapter 5 of Applications of Microsoft Excel in Analytical Chemistry, we explore the solution to the types of equations found in chemical equilibria. A general purpose quadratic equation solver is developed and used for equilibrium problems. Then, Excel is used to find iterative solutions by successive approximations. Excel s Solver is next employed to solve quadratic, cubic, and quartic equations of the type encountered in equilibrium calculations. 

The spreadsheet setup for Example 6.1 is given in your CD, Chapter 6. You can use it for solving other quadratic equations by inserting the appropriate a, b. 

We will hot constract a diprotic titration curve here, but if you want a good mental exercise, try it You just can t make the simplifying assumptions that we can usually use with monoprotic acids that are sufficiently weak or not too dilute. See your CD, Chapter 8, for auxiliary data for the spreadsheet calculation of the titration curve for 50.00 mL 0.1000 M H2C1O4 versus 0.1000 M NaOH. You can download that and enter the Kai and Kai values for other diprotic acids and see what their titration curves look like. Try, for example, maleic acid. For the calculations, we used the more exact equations mentioned above for the initial pH, the first buffet zone, and the first equivalence point. We did not use the quadratic equation for the second equivalence point since Cr04 is a quite weak base (Kbi = 3.12 X 10 ). See Ref. 8 for other examples of calculated titration curves. 

K2/[H ]. With AgOAc, however, [OAc ] = ajC = KiC3/([H +KJ and, with increasingly higher [HOAc], [OAc ] KjCy[H ]. Inasmuch as HOAc, unlike H2SO4 is a weak monoprotic acid, [H" ] never approximates at high values, and S decreases with the Develop the spreadsheet to solve for [H" ] from the quadratic equation... 

With 0, Xjjj, a, a, Pp, Pp and M known, this is a quadratic equation in Xp. The equation can be solved by the quadratic formula, although this requires a fair amount of algebra. The equation can also be solved with an Excel spreadsheet using Goal Seek or Solver. 

This equation can conveniendy be solved for for any specified value of using the formula for solution of quadratic equations, or by using Goal Seek or Solver in a spreadsheet. Note that the effective equilibrium parameter in Eq. 118-431 is (K g Crj/Ct). Since the total concentration in the fluid can easily be changed, this effective equilibrium parameter can be changed. This behavior is illustrated in Exanple 18-8 and Figure 18-19. 

When you solve a quadratic equation, retain all the digits in your calculator during the computation, or serious round-off errors can occur in some cases. Alternatively, create a spreadsheet to solve quadratic equations (Problem 8-36) and use it often. 

For polynomial equations with = 3 or higher, analytical solutions analogous to the quadratic equation for n = 2 either do not exist ( > 5) or are quite complex (n = 3 or 4). For such equations, the roots are found numerically using a comnuter nrpgram or spreadsheet or a graphing calculator Apago PDF Enhancer... 

Problems 14-41 through 14-43. We will set up spreadsheets that will solve a quadratic equation to determine [HsO ] or [OH ], as needed. While approximate solutions are appropriate for many of the calculations, the approach taken represents a more general solution and is somewhat easier to incorporate in a spreadsheet. As an example consider the titration of a weak acid with a strong base. Here and Vi represent initial concentration and initial volume. 

Using the HCl spreadsheet of Table 1.2 (p. 9) copy contents of column A to column G. Now write in H2 and 12 the formulas+G2 2 and G2 3. Copy H2.I2 to H2.I17. You now have the independent variable(s) as either G2.G17, G2.H17, or G2.I17. Let us focus on molarity, M, as the dependent variable, i.e. C2.C17. Carry out the regression /TAR /AR /DR for these three choices of independent variables, placing the outputs in convenient locations on the spreadsheet. Compare the overall reliabilities of linear, quadratic and cubic equations. 

Use a suitable statistics or spreadsheet program to calculate a quadratic relationship between absorbance and concentration. Using and values, comment on whether the data would be better described by a cubic equation. 

In Example 13.4, Eq. 13.Z, the Ky, term is certainly much smaller than the others. Set that equal to zero, and determine how much it changes your answer. If you have set up the spreadsheet recommended in Problem 13.7, you can manually set Ky, = 0 and your spreadsheet will solve if for you. Alternatively, you can note that this change makes the equation a quadratic, easy to solve numerically or analytically. 

---