From page 1, we learned that if we believe the temperatures were poorly measured, it would lead to the determination of ΔH_{vap} of water to 2 significant figures.

But the graph of the data shows a very good fit of the data to a straight line ( R^{2}= 0.998), so maybe the temperature measurements were more exact than how the data was recorded. Let’s determine the statistical error on the data using Excel’s **LINEST** function …

The **LINEST** function calculates the statistics for a line by using the “least squares” method to calculate a straight line that best fits the data, and then returns an array that describes the line.

Follow these steps:

1. LINEST function returns several outputs, so to begin, select a 2 by 5 array as shown.

See screen capture.

2. Click , the *Insert function* icon.

See screen capture

3. In the Insert Function popup menu, choose the *Statistical* category and then choose the *LINEST* function. Click *OK*.

See screen capture

4. In the Function Arguments window, click the* Known y’s* field and drag your mouse to highlight the y-values.

See screen capture

5. Click into the *Known_x’s* field and drag your mouse to highlight the x-values.

See screen capture

6. Type TRUE in the *Const* and *Stats* fields and click *OK*.

See screen capture

7. Highlight the formula in the formula bar.

See screen capture

8. Press Ctrl+Shift simultaneously and hit Enter. The array we selected in Step 1 is now filled with numbers.

See screen capture

9. Let’s focus on the meaning of the highlighted numbers.

The LINEST function calculates the statistical errors of the slopes and intercepts.

Slope = -5090 +/- 50 K (3 significant figures)

Intercept = 18.2 +/- 0.2 (3 significant figures)

From the slope, we determine ΔH_{vap} of water.

slope = – ΔH_{vap} / R where R is the gas constant, 8.314 J·mole^{-1}·K^{-1}.

ΔH_{vap} = (5090)(8.314) = **42.3 kJ/mole (3 significant figures)**

(**Note:** If you use the slope value of 5086.96266 to calculate ΔH_{vap}, your answer, when rounded to 3 significant figures, should also give you 42.3 kJ/mole)

This is how one determines the uncertainty in the slope and intercept of a graph using LINEST.

In summary, the statistical uncertainty determined by LINEST is the better representation of the uncertainty in this set of data. Had the coefficient of determination for the data not been close to 1, meaning that the data does not show a linear relationship, then one cannot simply determine the uncertainty of the slope and intercept based on statistics alone.

## Comments

Leave a comment Trackback