Excel basics

Introduction

In this exercise you are going to process the data already obtained from heating different masses of water in an electric kettle (see datalogger basics) . Your table of results will look something like this:

You are also going to process the uncertainties. These were estimated by considering the accuracy of the measuring device or by repeated measurement as outlined in the section on uncertainties. For this example the uncertainties are taken as:

Mass ±0.001kg (because the smallest digit on the balance was 1g)

Temperature change ±1°C (because the spread of data was 1°C)

Headers

Open a blank spreadsheet by double clicking the excel icon then click the top left cell (that's the name for one of the boxes in the table) and type the heading and uncertainty (Mass/kg ±0.001). To get the ± and ° symbols you choose "insert symbol" from the menu at the top then find the symbol you need.

Move to the next cell at the top and type in the header for the second column (Temp. Change/ °C ±1)

Now type in your data or if the data is already typed in word you can copy and paste. Your spreadsheet should now look like this:

Note that the number of decimal places in the mass column has been changed so that is the same as the uncertainty, this is important. To do this highlight the column by clicking the top data cell (0.400) hold the mouse button and drag the cursor down to the last and releasing. Now click on one of the highlighted cells with the right button and select "format cells" then number. Now you can adjust the number of decimal places displayed.

Calculating 1/m

As you will have realised when you plotted temp change vs mass the relationship is not linear, in fact they are inversely proportional, this means that if you plot ΔT against 1/m  you will get a straight line. Before doing that we need to calculate 1/m.

Add a third column called 1/m. To do the calculation follow these steps

  • Click the first cell in the new column
  • Write =1/ then click the first cell in the mass column.  The equation should now read =1/A3
  • Press return
  • Now hold the cursor on the bottom corner of the first cell until you get a cross like this one
  • Keeping the mouse button pressed pull this down like a blind, the equation will now copy into all the other cells automatically calculating 1/m for all the values.

Calculating the uncertainties in 1/m

Add the headers to the columns as shown below:

  • mmax is m + 0.001 (m+the uncertainty), fill this in by writing the equation=A3+0.001 and filling down as before.

Remember you don't have to type B3 you simply click in the B3 cell.

  • mmin is m - 0.001, fill this in by writing the equation=A3-0.001 and filling down.
  • In the 1/mmax column write an equation =1/D3 and fill down.
  • In the 1/mmin column write an equation =1/E3 and fill down.
  • The error in 1/m is found by subtracting (1/mmin – 1/mmax)/2 so enter the equation =(G3-F3)/2.

Note that the error in 1/m has two decimal places so 1/m has been formatted to display 2 decimal places as well. Format your cells accordingly.

This may seem an awful lot to take in but you will get a lot more practice at doing it, then its simply a matter of following the same procedure every time. After you get the hang of it you can reduce the number of columns by calculating the uncertainty with one equation.

Note:

In an earlier version of this worksheet I calculated 1/ΔT instead of 1/m however I realised that 1/m was more logical.

Here is a screencast of how to add an equation in Excel

All materials on this website are for the exclusive use of teachers and students at subscribing schools for the period of their subscription. Any unauthorised copying or posting of materials on other websites is an infringement of our copyright and could result in your account being blocked and legal action being taken against you.