Chapter 48

Determine unknowns from a calibration curve produced in Excel. The following data are for a set of calibration standards for chlorophenol in a sample extract. $$ \begin{array}{cc} \text { Concentration }\left(\mathrm{mg} \mathrm{L}^{-1}\right) & \text { Peak area } \\ 0 & 0 \\ 2 & 14567 \\ 4 & 30124 \\ 6 & 45623 \\ 8 & 60021 \\ 10 & 71209 \\ 15 & 112458 \\ \hline \end{array} $$ Using PC-based software (e.g. Excel), fit a trend line (linear regression) and determine the chlorophenol content of the following extracted water samples with the following peak areas: (a) 8741 , (b) 23478 (c) 38500 . Give your answers to three significant figures in each case.

Using the Trendline feature. This quick method provides a line of best fit on an Excel chart and can also provide a set of equation values for predictive purposes. 1\. Create a graph (chart) of your data. Enter the data in two columns within your spreadsheet, select the data array (highlight using left mouse button) and then, using the 'Insert' icon, select 'Scatter' and then the icon 'Scatter only with markers'. 2\. Add a trend line. Right-click on any of the data points on your graph, and select the AddTrendline menu. Choose the Linear trend line option, but do not click OK at this stage. Select: (i) Display equation on chart and (ii) Display R-squared value on chart. Now click OK. The equation (shown in the form \(y=b x+a)\) gives the slope and intercept of the line of best fit, while the \(R\)-squared value (coefficient of determination, p. 499) gives the proportional fit to the line (the closer this value is to 1, the better the fit of the data to the trend line). 3\. Modify the graph to improve its effectiveness. For a graph that is to be used elsewhere (e.g. in a lab write-up or project report), adjust the display to remove the default background and gridlines and change the symbol shape. Right-click on the trend line and use the Format Trendline \(>\) line style menu to adjust the Weight of the line to make it thinner or thicker. Drag and move the equation panel if you would like to alter its location on the chart. Fig. \(48.4\) shows a typical calibration curve produced in this way. 4\. Add a title and axes labels. Click on the Layout icon \(>\) chart title (to add a title) and Layout icon \(>\) chart axis (horizontal or vertical to add a label to the \(x\) and \(y\) axis respectively). 5\. Use the regression equation to estimate unknown (test) samples. By rearranging the equation for a straight line and substituting a particular \(y\)-value, you can predict the amount/concentration of substance ( \(x\)-value) in a test sample. This is more precise than simply reading the values from the graph using construction lines. If you are carrying out multiple calculations, the appropriate equation, \(x=(y-a) / b\), can be entered into a spreadsheet, for convenience.