1B40 Computing: Excel

Excel

Some aspects of the Excel course will be discussed in the lecture slots, but the main thrust of the computing elements are carried out in ten weekly sessions during the first term with each student attending one afternoon per week from 2-5.30 pm. You should have been informed during Induction week as to which session you have been assigned. This may be checked by viewing the file 1B40_Allocations.xls. If your name does not appear, please go to the sessions on Tuesdays.

For each week detailed instructions and some additional explanation of the mathematical background are given in task files. (These are in WORD or PDF format.) The starting positions for some spreadsheets are provided in some of the exercises, in others you create them from a description of the problem given in the task file. Samples of the results are shown in some of the files.

For some exercises the final result e.g. a graph, is well defined but the precise details of the spreadsheet layout are not. This is left to you to design. While good practice, elegance and clarity of the spreadsheets will be encouraged it is recognised that to many physicists and astronomers the final outcome is all that matters. Consequently spreadsheets that 'work' may get 50% of the marks, but well constructed ones will attract a higher reward!

Some exercises get you to implement a simple task discussed in the lectures or task files. In other cases the problems involve the use of spreadsheet functions, methods or tools NOT covered in the notes. This is to encourage you to make use of the Excel help facility (or colleagues) - the way you will need to work on problems outside the classroom environment. At the end of some of the exercises there is a question asking you to give some explanation for the results obtained in the spreadsheet. These questions may not have a unique answer!

In the first session you will work through a set of short exercises, which will not be assessed.

The first assessed exercise is a simple extension of what has been given in the Introductory session of the first week of the course. You add error bars to a plot (all experiments have uncertainties), label it properly and use Excel to find the "best line" through the points using the "trendline" facility. Click on the link Excel-1. (This exercise uses the spreadsheet comp_pendtask.xls.)

Exercise Excel-2 is an extension of the basic material. In this exercise (medical physics module) you have to plot several histograms of marks, normalised to the same sample size. The medics are also to be graded on a scale with some "compensation" of the grade if the mark is a "fail" mark. You need to find and look-up the usage of various Excel worksheet functions (e.g. the IF function) using the Excel Help facility. (This exercise uses the spreadsheet Med_mod_results.xls.)

In exercise Excel-3 we create the first spreadsheet dealing with the material given in the data analysis lectures. It is a simple example of the properties of the normal distribution and the idea of error on the mean.

The lectures on data analysis discuss how the errors on the sum or product of two independent variables is related to the individual errors. In exercise Excel-4 you investigate the "error propagation" results in a practical way - subject to the usual statistical fluctuations! In Excel-5 you apply the error propagation formulae to data satisfying different forms, i.e. linear, logarithmic and sine functions from (a) a compound pendulum experiment, (b) a chemical reaction, (c) an X-ray scattering experiment. You will need to download the spreadsheets comp_pend_data.xls and reaction_data.xls.

One of the major practical procedures discussed in the data analysis lectures is the least squares fitting of a straight line to a set of data points. Exercise Excel-6 is a guided spreadsheet application. (You will need the spreadsheet Least_squares_fit.xls.) In exercise Excel-7 you construct the spreadsheet yourself.

Many physical systems that we could model using an Excel spreadsheet involve differential equations, e.g. motion of a body under gravity. Therefore exercise Excel-8 looks at how we can approximate a mathematical derivative of a continuous function by discrete quantities in cells of the spreadsheet. You examine two slightly different approximations for dy/dx.

In exercise Excel-9 we consider a two-dimensional problem of the motion of a particle the Earth's gravitational field. You may well have met a simple projectile problem before - this was most likely the "drag-free" case - and solved it analytically. In this simulation you model the more realistic case of a projectile subjected to a drag force proportional to its speed. The aim (sic) is to find, by computer simulation, the angle of firing to achieve maximum range.

The use of random numbers (really pseudo-random numbers) in a computer is a powerful tool for the simulation of physical processes. In Excel-10 we first investigate some properties of random numbers and meet the central limit thereom - the saving grace for many physicists! You then simulate the exponential decay of nuclei. While this can be described by a simple mathematical expression (the exponential law) it is instructive to replicate this as close as possible in the discrete cells of a spreadsheet. The general ideas can be extended to cases which are not amenable to an analytical solution. Finally you investigate the 'Drunkard's walk' - a classical random process! This exercise also introduces you to some sophisticated use of the Excel functions to save tedious typing of formuale.