Slope spreadsheet calculation

A double logarithmic plot, given in Fig. 19.20(b), allows determination of the oxidation parameters k and n. A change in the oxidation mechanism (e.g. spallation or breakaway oxidation) becomes apparent by a change in the slope in the double logarithmic plot. Analysis of the linear part of the curve by linear regression using simple spreadsheet calculations yields the slope, b = 1/n, and the y-axis intercept, as shown in Fig. 19.20(b). The values for the oxidation parameter k and the exponent n as well as the number of... 

The rate constant for the alkyl bromide reaction is equal to the slope of the line. The best way to determine a slope is by doing a linear curve fit using a spreadsheet or graphing calculator. Somewhat less accurately, any two points on the line determine the slope ... 

One difficulty with the Trendline is that the equation only appears graphically. The values for slope and intercept have to be copied manually into the spreadsheet if they are to be used in later calculations. 

The method of least squares is used to determine the equation of the best straight line through experimental data points. Equations 4-16 to 4-18 and 4-20 to 4-22 provide the least-squares slope and intercept and their standard deviations. Equation 4-27 estimates the uncertainty in x from a measured value of y with a calibration curve. A spreadsheet greatly simplifies least-squares calculations. 

Many conventional texts express regression equations in the form of summations rather than matrices, but both approaches are equivalent with modern spreadsheets and matrix oriented programming environments it is easier to build on the matrix based equations and the summations can become rather unwieldy if the problem is more complex. In Figure 5.2, the absorbance of the 25 spectra at 335 nm is plotted against the concentration of pyrene. The graph is approximately linear, and provides a best fit slope calculated by... 

This method is the simplest and is well-suited to implementation in computer programs or spreadsheets [9]. Various standard statistical criteria, e g. the correlation coefficient, r the standard error of the slope of the regression line, 5 or the standard error of the estimate of g(or) from t, s, are used to quantify the deviation of a set of experimental points from the calculated regression line through them [10]. The inadequacies of r as an indicator of fit have been stressed [4,11]. The use of 5 is preferable in that its value is dependent upon the range of t used in the analysis. 

Spreadsheet Summary In the first exercise in Chapter 10 of Applications of Microsoft Excel in Analytical Chemistry, a spreadsheet is developed to calculate the electrode potentials as a function of the ratio of reductant-to-oxidant concentration ([R]/[0]) for the case of two soluble species. Plots of E versus [R]/[0] and E versus log([R]/[0]) are made, and the slopes and intercepts are determined. The spreadsheet is modified for metal/metal ion systems. 

Once it has been verified that the data can be properly fit to a one-compartment bolus IV model, a linear regression analysis is performed on the data, with time (t) entered as the independent (x) data, and In(Cp entered as the dependent (y) data. Linear regression analysis can be performed on calculators that handle two-variable statistics, or using spreadsheet, graphing, or statistical analysis software on a computer. The analysis should provide values for the intercept (b) and the slope (m) that provide the best possible fit to the measured data in the form y = b + mx, as illustrated in Figure 10.23. The linear regression analysis also often provides a value called the correlation coefficient (r). 

We can use the Excel statistical functions to calculate the slope and intercept for a series of data, and the R value, without a plot. Open a new spreadsheet and enter the calibration data from Example 3.21, as in Figure 3.9, in cells A3 B7. In cell A9 type Intercept, in cell A10, Slope, and in cell All, R Highlight cell B9, click on / Statistical, and scroll down to INTERCEPT under Function name, and click OK. For Known x s, enter the array A3 A7, and for Known y s, enter B3 B7. Click... 

The LINEST program of Excel allows us to quickly obtain several statistical functions for a set of data, in particular, the slope and its standard deviation, the intercept and its standard deviation, the coefficient of determination, and the standard error of the estimate, besides others we will not discuss now. Linest will automatically calculate a total of 10 functions in 2 columns of the spreadsheet. 

The linear least-squares line gives a slope of 0.861 and an intercept of —0.002 (using Options under Chart, Add Trendline, when highlighting the chart or line). Hence, the concentration of the unknown is equal to (0.463 — 0.002)70.861, as given by the formula in the spreadsheet (below). The sample concentration is 0.540 ppm. We will now perform the same calculation without charting the calibration curve, and including the standard deviation of the sample concentration. 

The value of the constant and the slope in the equation are calculated using the linear regression formulas for the terms. These terms can be estimated using the Regression Function in Excel or another spreadsheet. Or, they can be calculated by hand using the formulas found in any statistics text. The method that is used to calculate them is the least squares estimate. This method minimizes the sum of the square errors between the actual value and the predicted value. 

Linear regression functions in a calculator or spreadsheet can determine the slope of a best-fit line. 

This shows us how we can determine values for or A. We will need to measure k for at least two temperatures, but we already know ways to do that. The rearranged equation is in the femiliar form of a line, y = mx + b, so z plot of In k versus 1/T should be a straight line. The line s slope is -EJR, where R is the gas constant, whose value is known. So we can get an estimate of the activation energy from the slope, as shown in Figure 11.13. This type of problem is easily attacked with a graphing calculator or a spreadsheet, and there are several such problems at the end of this chapter. 

The slope (which is also called the first derivative) displayed in the middle of Figure 10-4 is calculated in Figure 10-5. The first two columns of this spreadsheet give experimental volumes and pH measurements. (The pH meter was precise to three digits, even though accuracy ends in the second decimal place.) To compute the first derivative, each pair of volumes is averaged and the quantity ApH/AV is calculated. 

The spreadsheet and Stern-Votmer plot are shown in Figure 15-4. The data are entered into columns B and C, and F /ris calculated in column D. The slope and its standard deviation are - 210 0.9 M" as obtained from the spreadsheet statistics. Note that the intercept is very nearly unity. 

Alternatively, any spreadsheet can be used to determine constants A and B.The [REGR] function in a spreadsheet like ExceF or Lotus 1-2-3 is used. The [REGR] function is defined as (=LINEST(known y s,known x s,TRUE,TRUE). To use this function, you must first put the FPY function into the form y = Ax -PB.This is done by creating two columns complexity index (which we will call XI) and yield. A third column is created for log[log(Xl)]), whereas a fourth column is created for log[ln(-yield/100)]). Provide the regression function with column 4 as known ys and column 3 as known xs. The regression function will return 10 values FIT (slope int.), sig-M (slope int.), r2, sig-B(slope int.), F,df (slope int.), and reg sum sq (slope int.). The constant B is equal to the FIT (slope) and the constant A is [-FIT(int.)/FIT(slope)]. (Remember, to calculate an array, follow these steps highlight the array on the spreadsheet type the array formula, making sure that the cursor is in the edit bar then press CTRL -t SHIFT -t ENTER.)... 

To find the heat of vaporization, use an Excel spreadsheet or a graphing calculator to make a plot of the natural log of vtpjr pressure (In P) as a function of the inverse of the temperature in kelvins (l/T). Then fit the points to a line and determine the slope of the line. The slope of the best-fitting line is —3773 K. Since the slope equals —AHy /R, we find the heat of vjptrization as follows ... 

The plot is linear, confirming that the reaction is indeed first order. To obtain the rate constant, fit the data to a line. The slope of the line will be equal to -k. Since the slope of the best fitting line (which is most easily determined on a graphing calculator or with spreadsheet software such as Microsoft Excel) is -2.90 X 10 " s the rate constant is therefore-1-2.90 x 10 " s ... 

The uncertainty of the slope and intercept values can be calculated by standard statistical techniques. These calculations are straightforward in an EXCEL spreadsheet. An example is provided in Appendix 6-C. In fact, the calculations can be done automatically with the Data Analysis package in EXCEL. 

---