Using Excel for your LO3 and Investigation

We’ll spend the next two lessons in the library learning how to use Excel.  Download the instructions using the link below.  If you have your own LO3 data, feel free to work with those values instead of the numbers I have provided.

By the end of this activity you will be able to;

  • manipulate raw data using formulae in cells
  • plot a graph of your results
  • add error bars to your graph
  • add a line of best fit
  • calculate the gradient and y-axis intercept of your line

AH investigations – uncertainties and graphs

Now you have some measurements in your daybook, it’s time to look at the uncertainties in your data.  There will be scale reading uncertainty, calibration uncertainty and randon uncertainty to consider.  I’ve attached a copy of the latest guidance from SQA on uncertainties for AH Physics candidates.  Download a copy and use it to help with number crunching.

Remember that there is help available when you are ready to graph your data.  Start here.

AH – help using Excel

This tutorial will help you with adding a line of best fit to your LO3 graph.  If you use the linest function, you can extract useful information on gradient and intercept of the line.  There are also screenshots to show how error bars can be added to individual points.

Excel Tutorial: Using LINEST function, Plotting a graph, Adding Error Bars

I have added a download link to a short pdf on the use of linest.


Using excel for AH Physics investigation graphs

We spent some time today looking at ways of analysing data obtained from your investigation experiments. It is often a good idea to generate a graph of your results.  Your graph does not have to be drawn by hand, it can be produced on a spreadsheet.  If you do decide to use a spreadsheet, remember to print the gridlines on your final version of the graph.

If you use Excel, there is a function called linest that can be used to determine the gradient of the best-fit line and the intercept with the y-axis.  If you follow the instructions below, the linest function will also calculate the uncertainty in the gradient and intercept point for you.

Here is a linest tutorial to get you started.  It also includes instructions on how to add custom error bars to your graph.

Excel Tutorial: Using LINEST function, Plotting a graph, Adding Error Bars

I have included a pdf of a different help sheet – download it using the link at the end of this post.

uncertainties booklet

It’s time to start looking at the uncertainties in your investigation results.  I have attached a pdf of a guide to uncertainties in AH Physics.  You should pay particular attention to the sections on combining uncertainties and analysing graphs.

I will post additional resources to help you deal with uncertainties in your graph.

AH investigation report guidelines

You should all be well underway with the writing phase of your investigation now.  I have attached some guidance from the SQA that is designed to help you with this.  Check pages 5-9 of the attached pdf document for detailed information on what is required of you in the final report.  Note the word limits and detailed specification of what the marker will be looking for in each section.

Remember that detailed feedback from me will only be possible if I receive your draft report by 3.30pm on Friday.  The deadline for submission is the end of the first week back at school after the Easter holiday.

significant figures

In our discussions yesterday, one of the things that cropped up was that we need to revise the material covered before the summer holidays.  I thought I would make a start on this by looking at significant figures.  

You might have heard me referring to “calculator vomit” in class.  This is an expression I use whenever people simply write down the answer provided by their calculator, without thinking about whether or not the number of decimal places reported is appropriate.  In Physics, we can avoid “calculator vomit” by using significant figures.  I’ve provided some links below to direct you to sites that explain what significant figures are and how to use them.


If you’ve read through some of those pages and feel that you are ready for a test, you can try your luck at

Note: these links might also be useful for AH pupils analysing their investigation data.