Question

Correlation coefficient and Excel graphing. Synthetic

data are given below for a calibration curve in which random Gaussian

noise with a standard deviation ofwas superimposed on

y values for the equation$\mathbf{y}\mathbf{=}\mathbf{26}\mathbf{.}\mathbf{4}\mathbf{x}\mathbf{+}\mathbf{1}\mathbf{.}\mathbf{37}$

. This exercise shows that

a high value of${\mathbf{R}}^{\mathbf{2}}$

does not guarantee that data quality is excellent.

(a)Enter concentration in column A and signal in column B of a

spreadsheet. Prepare an XY Scatter chart of signal versus concentration

without a line as described in Section$\mathbf{2}\mathbf{-}\mathbf{11}$

. Use LINEST

(Section 4-7) to find the least-squares parameters including${\mathbf{R}}^{\mathbf{2}}$

.

(b)Now insert the Trendline by following instructions on page 88.

In the Options window used to select the Trendline, select Display

Equation and Display R-Squared. Verify that Trendline and LINEST

give identical results.

(c)Add $\mathbf{95}\mathbf{\%}$confidence interval y error bars following the instructions

at the end of Section 4-9. The$\mathbf{95}\mathbf{\%}$

confidence interval is 6tsy,

where${\mathrm{s}}_{\mathrm{y}}$

comes from LINEST and Student’s tcomes from Table 4-4

for$\mathbf{95}\mathbf{\%}$confidence and$\mathbf{11}\mathbf{-}\mathbf{2}\mathbf{=}\mathbf{9}$

degrees of freedom. Also, compute

t with the statement $\mathbf{"}\mathbf{=}\mathbf{TINV}\mathbf{(}\mathbf{0}\mathbf{.}\mathbf{05}\mathbf{,}\mathbf{9}\mathbf{)}\mathbf{"}$.

Short answer

(a) The value of ${\mathrm{R}}^2$is $0.9932$and$m=24.807,$$s_m=0.683532.$

(b) It is verified that Trendline displays the same data as LINEST.

(c) The $95\%$confidence interval for y is calculated for the following bars has to be added and t with the statement " =$TINV(0.05,9)$" was determined.

Step-by-step solution

Definition linest and linearity

Linest : The linest is a Microsoft Excel function that measures the statistics for a straight line and an array defining that line using the least squares approach. It is a built-in function that may be categorised. Linearity: It's a measurement of how closely a calibration curve follows a straight line, indicating that the response is proportionate to the analyte quantity.

$$\begin{gathered} R^2=\frac{{\left[\sum _{}^{}\left(x_1-\overline{x}\right)\left(y_1-\overline{y}\right)\right]}^2}{\sum _{}^{}{\left(x_1-\overline{x}\right)}^2\sum _{}^{}{\left(y_1-\overline{y}\right)}^2} \\ \mathrm{R}\mathrm{is}\mathrm{the}\mathrm{square}\mathrm{of}\mathrm{correlation}\mathrm{coefficient}. \end{gathered}$$

Find the least-squares parameters

(a)

Plot the graph with x as concentration values and y with signal values.

The value of ${\mathrm{R}}_2\mathrm{is}0.9932\mathrm{and}\mathrm{m}=24.807,{\mathrm{s}}_{\mathrm{m}}=0.683532\mathrm{in}\mathrm{cells}\mathrm{B}19:\mathrm{C}21.$

Check the trendline and linest

(b)

Again consider the graph with x as concentration values and y with signal values.

$\mathrm{As}\mathrm{it}\mathrm{can}\mathrm{be}\mathrm{seen}\mathrm{that}\mathrm{the}\mathrm{values}\mathrm{of}{\mathrm{R}}^2\mathrm{is}0.9932\mathrm{and}\mathrm{m}-24.807,{\mathrm{s}}_{\mathrm{m}}-0.683532\mathrm{in}\mathrm{cells}\mathrm{B}19\mathrm{C}21.$

The Trendline displays the same data as LINEST, which are displayed within the graph.

Determine t

(c)

The graph with x as concentration values and y with signal values.

Therefore,$95\%$confidence interval for$\mathrm{y}$ is calculated in cell$\mathrm{C}24\mathrm{as}162.1728.$