Linest
From Chemistry Resource
The LINEST function in Excel calculates the statistics error for a line by using the "least squares" method and then returns an array that describes the line. Below is a set of data of the boiling point of water at various temperatures. By plotting a graph of "ln p Vs. 1/T" with temperature in Kelvin, the heat of vapourization of water can be determined. Follow the steps below to plot a graph of "ln p Vs. 1/T" and use the Linest function in Excel to determine the statistical error of the set of data.
| T (oC) | P (kPa) |
| 0.0 | 1.227 |
| 10 | 0.612 |
| 20 | 2.536 |
| 30 | 4.242 |
| 40 | 7.37 |
| 50 | 12.33 |
| 60 | 19.9 |
| 70 | 31.15 |
| 75.7 | 46.12 |
| 89.7 | 70.1 |
| 100 | 101.32 |
| 120 | 198.5 |
| 200 | 1554.3 |
Steps to use the Linest function in Excel
- LINEST function returns several outputs, so to begin, select a 2 by 5 array as shown.
- Click
, the Insert function icon. - In the Insert Function popup menu, choose the Statistical category and then choose the LINEST function. Click OK.
- In the Function Arguments window, click the Known y's field and drag your mouse to highlight the y-values.
- Click into the Known_x's field and drag your mouse to highlight the x-values.
- Type TRUE in the Const and Stats fields and click OK.
- Highlight the formula in the formula bar.
- Press Ctrl+Shift simultaneously and hit Enter. The array we selected in Step 1 is now filled with numbers.
- 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)
Calculations of the Heat of Vapourization (ΔHvap) of Water
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)
