## Least Squares Fit to a Straight Line Using the Worksheet Functions Slope Intercept and RSQ

Figure 13-1 shows the phase diagram of methane hydrate, one of a class of compounds known as clathrate hydrates. Methane hydrate, an ice-like solid, consists of methane molecules trapped in a crystalline lattice of water molecules; each unit cell of the crystal lattice contains 46 water molecules and up to 8 gas molecules. The figure shows that the solid phase forms under conditions of high pressure and relatively low temperature. Previously, information about the formation of methane hydrate was important in the natural gas transmission business because the solid can clog valves. More recently, the discovery of methane hydrate deposits on the ocean floor has led to estimates that they contain enough natural gas to provide an energy source for the next several hundred years, if they can be accessed.

The data of Figure 13-1 conforms to an exponential curve. It can be shown that the vapor pressure varies with the absolute temperature according to the Clausius-Clapeyron equation (13-9):

a 2000

4000

3000

1000

Figure 13-1. Methane hydrate phase diagram. The line is the least-squares fit to the data points, (folder 'Chapter 13 Examples', workbook 'Methane Hydrate', sheet 'Finished chart')

Figure 13-1. Methane hydrate phase diagram. The line is the least-squares fit to the data points, (folder 'Chapter 13 Examples', workbook 'Methane Hydrate', sheet 'Finished chart')

a i |
b |
c Td |
E |
F | ||

M et 1 t.ine HydratÂ« |
Data for Cliuisius- | |||||

1 |
Phase Di.Kji.im Dilta |
Cl.ipeyron Plot | ||||

2 |
t.k |
t.-c |
p, atm |
1/t |
In P | |

3 |
273 7 |
0.5 |
27 |
0 00365 |
3 31 | |

4 |
280.9 |
7.7 |
58 |
0.00356 |
4 06 | |

5 |
285.9 |
12,7 |
97 |
0.00350 |
4.57 | |

6 |
286.5 |
13,3 |
105 |
0.00349 |
4.65 | |

7 |
286.7 |
13.5 |
107 |
0.00349 |
4.67 | |

8 |
290.2 |
17.0 |
157 |
000345 |
5.06 | |

9 |
295.7 |
22.5 |
335 |
0 00338 |
5.82 | |

10 |
301.0 |
27.8 |
640 |
0.00332 |
6.46 | |

11 |
301.6 |
28.4 |
645 |
0.00332 |
6 47 | |

12 |
302.0 |
28.8 |
765 |
0 00331 |
6.64 | |

13 |
315.1 |
41 9 |
2344 |
0.00317 |
7.76 | |

14 |
320.1 |
46,3 |
3918 |
0.00312 |
8.27 |

Figure 13-2. Portion of spreadsheet for Clausius-Clapeyron plot for methane hydrate, (folder 'Chapter 13 Examples', workbook 'Methane Hydrate', sheet 'Phase diagram data')

When the data of Figure 13-2 is plotted in the form In P vs. HT where T is in Kelvin, Figure 13-3 is obtained. The line is the least-squares best-fit line, obtained as follows.

The SLOPE, INTERCEPT and RSQ worksheet functions were used to obtain the least-squares best fit coefficients of the data, plus R2, the coefficient of determination. The syntax of the SLOPE function is SLOPE(/rnown y's, known_x's); the arguments of INTERCEPT and RSQ are the same as for the SLOPE function. The values are shown in Figure 13-4.

E |
F | |

16 |
slope- |
-9705 |

17 |
intercept = |
38.61 |

16 |
R: = |
0.9959 |

Figure 13-4. Slope, intercept and R2 of the plot of In P vs. 1/7 for methane hydrate.

(folder 'Chapter 13 Examples', workbook 'Methane Hydrate', sheet 'Phase diagram data')

Figure 13-4. Slope, intercept and R2 of the plot of In P vs. 1/7 for methane hydrate.

(folder 'Chapter 13 Examples', workbook 'Methane Hydrate', sheet 'Phase diagram data')

The formulas in cells F16, F17 and F18 are

=SLOPE(F3:F14,E3:E14)

=INTERCEPT(F3:F14,E3:E14)

The least-squares line shown in Figure 13-1 was calculated using the regression coefficients A and B found for equation 13-9.

## Post a comment