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

But the graph of the data shows a very good fit of the data to a straight line ( R2= 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 ΔHvap of water.

slope = – ΔHvap / R where R is the gas constant, 8.314 J·mole-1·K-1.

ΔHvap = (5090)(8.314) = 42.3 kJ/mole (3 significant figures)
(Note: If you use the slope value of 5086.96266 to calculate ΔHvap, 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.