Using Excel 2003 - 1.2) Manipulating Data

1. On a new sheet in the workbook, enter the data given below, so that it looks like the figure.
   
2. Rather than plotting P vs T, we wish to linearise the data, so that the plot will be a straight line. Using the integrated form of the Clausius-Clapeyron equation it can be shown that a plot of ln(P) vs 1/RT will be linear. So we need to calculate ln(P) and 1/RT for each of the data points. To do this we first label the columns where this transformed data will appear. Edit your spreadsheet until it appears like the figure below.
   
3. To calculate the first value in the T / K column, select cell C6 and then type =b6+273.15
   
   
   Note 1: The = at the start of the entry specifies you are typing a formula, i.e. that you wish to calculate a value.
   Note 2: Entering b6 causes Excel to use the value it finds in cell B6 when it calculates the value of the formula.
4. Now press Enter. The formula will disappear and be replaced with the calculated value.
5. Click on cell C6 again. Notice that the calculated value, not the formula, is displayed in the cell. The formula itself is shown in the box above the columns, next to the = sign.
   
   If you ever need to edit the cell formula, you can do so by clicking on the relevant part of the formula displayed in the formula box.
6. We now need to repeat this calculation for the other temperature values. However, rather than typing the appropriate formula into every cell, we can get Excel to do this for us. Cell C6 on your worksheet should currently be bordered with a dark line, with a small dark square in the bottom right corner (see figure above). Move the cursor over this small square and press and hold the mouse button down.
7. Move the mouse down, a box will appear around the block of cells that have been highlighted.
   
8. Once the block of highlighted cells extends down to, and including, C12 (see figure above) release the mouse button. Excel will automatically copy and paste the formula from C6 to the highlighted cells.
   
   Click on cell C7. Notice that the formula for this cell is =B7+273.15, i.e. excel has automatically changed the cell used in the formula from b6 to b7. This will be true for the other cells as well.
9. We now need to calculate the values for the remaining two columns. Select cell D6, type =ln(a6) and press Enter.
   Note: ln(a6) tells Excel to take the natural logarithm of the value in cell A6. Other useful mathematical functions are:

   Typical Notation	Excel Notation
   3²	3^2
   3 x 10²	3E+02
   e²	exp(2)
   Note: Obviously, the numbers 3 and 2 in the examples could be replaced by an appropriate cell reference (e.g. B6).

10. Select cell E6, type =1/(a$3*c6) and press Enter.
    Note: A $ has been included in the reference to the cell containing the gas constant (A3), since this tells Excel not to change the reference following the $ when the cell is copied. For example, if we had entered the formula as =1/(a3*c6) and had copied it to cell E7, the copied formula would read =1/(a4*c7), whereas copying the formula =1/(a$3*c6) will produce =1/(a$3*c7) in E7.
11. Select cells D6 and E6 and repeat the block copy procedure outlined in steps 6-8.
12. Your spreadsheet should look like the figure below.
    

Performing a Statistical Analysis

Using Excel 2003 - Contents Page