Loci (2008)

Chemical Graph Theory, Kimberly Jordan Burch

The goal of this project is to develop melting point models based on the diameter of the normal alkanes having between five and twenty-five carbon atoms. These models could then be used to predict the melting points of normal alkanes having more than twenty-five carbon atoms for which no experimental data exists. In order to test the predictive abilities of your models, five alkanes will be selected at random to withhold as a predictive data set. You will use your model to predict the melting points of the five withheld alkanes and compare your results with the known experimental data. A list of the melting points and diameters for twenty-one normal alkanes has been provided.

- Save your spreadsheet as
`yourname.xls`

. - Create a new sheet by selecting
`Insert > Worksheet`

. - Use the
`RAND`

function 5 times to get your predictive set. To get a random number between 2 and 22 type`=RAND()*(22-2)+2`

in a cell. Cut and paste the five rows containing your predictive set to the bottom of your spreadsheet, hence removing them from the data with which you will construct your model. - Plot the sixteen data points and determine what type of fit (linear, polynomial, logarithmic, etc.) you should use to construct your model.
- To do this select
`Insert > Chart`

. - From the
`Chart type`

menu select`XY (Scatter)`

with the top`Chart sub-type (points only)`

selected, hit`Next`

. - Under the
`Data Range`

tab, click in the box for`Data range`

and then highlight the two columns containing the sixteen diameters and melting pt values. - Under the
`Series`

tab, make sure the`X Values`

box has the sixteen diameter values and that the`Y Values`

box has the sixteen melting pt values. Hit`Next`

. - Under the
`Titles`

tab, type`Diameter`

in the`Value (X)`

axis box and type`Melting Point (K)`

in the`Value (Y)`

axis box. Under the Gridlines tab, select`Major gridlines`

for both the X and Y-axes. Under the`Legend`

tab, uncheck the box next to show legend. Hit`Next`

. - To place the chart in your worksheet, select
`As object in`

and hit`Finish`

. - If you would like to change the color of your plot from gray, right click on the plot and select Format Plot Area. Under Area on the right you can choose a background color or a Texture from the
`Fill Effects`

menu.

- To do this select
- Right click on any of the plotted data points in your chart and select Add Trendline. Choose the type of trendline you feel will best fit the data.
- Right click your trendline and select
`Format Trendline`

. Under`Patterns`

change the`Weight`

to be one less so the trendline is less heavy in the graph. Under`Options`

check the boxes next to`Display equation on chart`

and`Display R-Squared Value on chart`

. - Enter the equation of your model in the spreadsheet (using the value in the diameter column in place of x) and calculate the melting points of all the alkanes.
- Compute the error between the experimental (given) melting point data and the modeled melting point data by adding a column that subtracts the experimental melting pt from the modeled melting pt.
- The chart gives you the coefficient of determination (R2) for your model. This value ranges from 0 to 1 with 1 being a perfect fit. You can also calculate the standard deviation of your model by typing
`=STDEV`

(highlight the sixteen differences). - Insert a second Worksheet into your spreadsheet and repeat the above process choosing a different set of five alkanes to withhold as predictive data.