Student Instructions

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.

1. Save your spreadsheet as yourname.xls.
2. Create a new sheet by selecting Insert > Worksheet.
3. 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.
4. Plot the sixteen data points and determine what type of fit (linear, polynomial, logarithmic, etc.) you should use to construct your model.
   1. To do this select Insert > Chart.
   2. From the Chart type menu select XY (Scatter) with the top Chart sub-type (points only) selected, hit Next.
   3. 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.
   4. 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.
   5. 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.
   6. To place the chart in your worksheet, select As object in and hit Finish.
   7. 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.
5. 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.
6. 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.
7. 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.
8. 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.
9. 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).
10. Insert a second Worksheet into your spreadsheet and repeat the above process choosing a different set of five alkanes to withhold as predictive data.