Computer Lab exercises/B27TA Computer Lab EXERCISE 1: Excel Overview
START: WEEK 2 (week beginning 21st Sept) TO BE MARKED BY: End of computer lab session in Week 3 So far we haven’t covered enough material in the lectures to do any meaningful statistical analysis. This week’s exercises therefore fall into two categories:
Part 1: Critical analysis of plotted data
Part 2: Some simple Excel exercises
Part of your mark for the spreadsheet exercises will depend on how coherently presented your data is – make it neat and easy to follow!
Below are eight graphs plotting various data sets. In each case, comment on what is wrong (there could be more than one thing in each instance).
y = 0.8x – 0.1173
0.1 0.2 0.3 0.4 0.5 0.6 0.7
0 20 40 60 80 100
Displacement measured at time,t (mm)
150 200 250 300 350 400 450 500
Absorbance (atm-1 cm-1)
0 2000 4000 6000 8000 10000 12000 f
ff f (degrees)
Sin(f f f f)
Collision impact energy experiment to determine the unkown mass of an object
y = 24.264×2 + 20.093x – 101
0 5 10 15 20 25 30 35
Metres per second
Collision impact energy experiment to determine the unknown mass of an object
y = 0.001×5 – 0.0529×4 + 1.0805×3 – 11.303×2 + 55.519x – 15.363
0 5 10 15 20 25
Number of individuals (n)
Average response time (ms)
1 2 3 4 5 6
National Debt (£ Billions)
y = 0.9143x + 64
0 20 40 60 80
2001 2002 2003 2004 2005 2006
? Total distance travelled = 0.9143 km
Part 2: EXCEL Spreadsheet Primer
Spreadsheets are applications that provide a convenient way of entering experimental data, processing it easily using mathematical formulae and presenting information graphically.
Excel is a Microsoft application and is a fairly powerful spreadsheet which is available on many PCs run by the University.
The basic process of entering data and performing simple analysis is quite straightforward. The built in HELP function is also quite useful (accessed using the blue ? icon in the top right hand corner of the screen).
This Primer is designed to get you ‘up to speed’ with certain aspects of Excel (although some of you may find the first part of this rather basic). It shouldn’t take most of you more than an hour to get as far as the exercise at the end. Note that demonstrators are expecting to see your working for this walkthrough, not just the exercise at the end
1. On opening Excel, the centre of the screen has an array of cells which are identified by their coordinates – e.g. A1, D5, E10 etc. In Excel, the coordinates are referred to as the cell REFERENCE. The name, BOOK 1, is the default name until you save the file with your own choice of name…
2. At the top of the screen there is a menu bar and a row of buttons. This is the TOOL BAR. We will use these later. To get a brief description of what a particular button does, move the cursor over it (without clicking).
3. The cell A1 is outlined, or if it is not, click on it once. Click on various cells and see the highlighted cell move.
ENTERING TEXT & NUMBERS
4. Highlighting cell A1 again, type EXCEL 1. Notice that is appears in the cell and also on the FORMULA BAR just below the MENU BAR. There is a flashing cursor at the
Active Cell Formula Bar
end of what you typed. If you now press
5. Enter 1 in A3, 2 in A4 and 3 in A5. Notice that text is left justified and numbers are right justified (although this can be changed using options in the toolbar). Now highlight A7 and type =A3+A4+A5 and then hit
6. The spreadsheet will update automatically. Select A5, type 10 and then hit
SAVE YOUR WORK
Click on the Windows icon in the top left hand corner of the screen and then select SAVE AS. Save to either a removable drive or your allocated space on the network drive. Be sure not to save to a drive that will be cleared when you log off!
Be sure to save your work regularly using the disk icon at the top left of the screen (every 10 minutes or so)
QUICK WAYS OF ENTERING NUMBERS
7. To enter the series 1.0, 1.2, 1.4, 1.6….2.2 is going to be rather tedious but…select B5 and enter 1.0 and B6 and enter 1.2. Select B5 again (left click) and drag the cursor down to B6. Grab the FILL HANDLE (the small square in the bottom right hand corner), drag it down a few cells to B12 and then release the mouse button. You should now have all these cells highlighted and containing the first few numbers in the required series. Excel works out the sequence from the first two numbers.
8. A second way to enter a number series is as follows: Select C5 and enter 1.0. Now select C6 and type =C5+0.2 followed by
9. Excel has many in-built mathematical functions such as SIN( ), COS( ), TAN( ), SQRT( ) (meaning v), EXP( ) (meaning exponential) etc, as well as SUM( ), AVERAGE( ), STDEV( ) and many more operations… A number of these that will be useful to you are provided at the front of this booklet.
The group of cells C5 to C12 is called a RANGE and is specified as C5:C12. In C14 enter =SUM( and then select C5 and drag downwards until you reach C12. Notice how the Excel fills in the range values for you. Then type ) and hit
10. In C15 enter =COUNT(C5:C12). This counts the number of cells containing numbers or formulae but not text or completely blank cells. Note that 0 (zero) is counted because it is a number. In C16 enter =C14/C15. This should now display the average of cells C5 to C12 (/ is the symbol for division). In C17 enter =AVERAGE(C5:C12). This should return the same answer as C16. In C18 enter =STDEV(C5:C12) to return the standard deviation of the data. Add some text in
neighbouring cells D14 to D18 to indicate what is going on e.g. “sum”, “number”, “average” etc.
It is important to ensure that a spreadsheet is easy to understand and follow, so always make sure data is clearly labelled.
VERY SIMPLE MATHS
11. In F4 enter the text “x value”. In F5 start a column of numbers from 1 to 12. In G4 enter the text “y=mx + c”. In G5 enter the formula =3*F5 + 3. The * is the symbol for multiplication. Select G5, grab the FILL HANDLE and drag down to the bottom of the neighbouring column of x-values. Look at the contents of each cell in column G and notice that the constants 3 remain the same but the reference to the x-value increments automatically as you go to lower cells.
12. In H2 enter the text “Planck’s constant, h (J s)”. You can change the width of a column by positioning the cursor over the divisions between the letter headings, left clicking and then dragging the bar left or right. Text effects such as superscript may be added using FORMAT and then FONT options on the toolbar.
13. In I2 type 6.626E-34 (This is equivalent to writing 6.626 × 10-34). Note that after you have hit
14. In H4 enter the text “Frequency, ? (Hz)” and then in H5 to H9 generate a column of numbers beginning at 5E12 and increasing by an order of magnitude with each step. Note that the symbol we use for frequency is the Greek letter ? (nu), not the standard letter v. Greek symbols may be added to a cell using the INSERT menu followed by the SYMBOL option. In I4 enter “Energy (J)” and then in I5 input the formula =H5*I2.
15. Now select I5, grab the FILL HANDLE and drag down to the bottom of the data column. The answers are clearly not all correct. Looking at the contents of each cell it should be obvious why – the constant, h, in our E=h ? equation is changing from line to line of the calculation. There are 3 ways to stop this:
One is to simply change the input formula in cell I5 to =H5*6.626E-34 before applying the FILL HANDLE, although this can become rather cumbersome if we wish to edit our equation at a later point.
A second way is to type =H5*$I$2 into I5 (followed by FILL HANDLE) – try it and you’ll see that the $ signs around the I let Excel know not to change the value in that cell from one line to the next. This is useful as we can now change a single value in cell I2 and it will automatically update all other cells that use this constant
For example, if we realised that our frequency values were actually expressed in terms of angular frequency (rad s-1) rather than Hz (s-1), we could simply divide Planck’s constant in cell I2 by 2 p , rather than change the equation in I5 and then use this to update all other relevant cells, which becomes increasingly inconvenient the larger a spreadsheet gets.
The major drawback with this approach, however, is that for more complicated equations, things become very hard to follow and mistakes are easily made (i.e. don’t do it this way!)
16. The third (and best) method to overcome the problem is to attach a variable name to the contents of the cell I2. To do this, right click on I2 and then select DEFINE NAME… from the menu that appears. A new menu will now pop up and in the NAME field, type h and then hit OK. We can now simply use the letter h when we input equations and Excel will automatically assign whatever value is in cell I2 to that letter (or name). In I5 now type = H5*h, hit
Unhelpfully, Excel will not accept ‘c’ or ‘r’ as variable names as these are reserved as designators for ‘columns’ and ‘rows’. This is slightly annoying when you want to define things like the speed of light or a radius, but you just have to work around it – for example, using ‘rad’ as a variable name for radius rather than ‘r’…
SIMPLE MATHS PART II
17. In cells K4 and L4 input the headings “Angle, ? (deg)” and “cos2( ? )”. In cells K5:K11 then input angles from 0° to 90° in 15° increments. Before we can calculate cells L5:L11 however we have to bear in mind that trigonometric functions in Excel require an angle in radians rather than in degrees. Position the cursor over the box marked L at the top of that particular column, right click and then select INSERT from the menu. A new column will appear. Label the new (empty) L4 cell “Angle, ? (rad)”.
18. To convert the data in K5:K11 into radians we have several options for what we can enter in L5 before applying the FILL HANDLE approach to cells L6:L11
(i) Simply type =K5*3.141/180.0
(ii) Be slightly more clever and type =K5*ACOS(-1.0)/180.0
(iii) Define a variable ‘pi’ in a different cell as discussed previously in (16.), preferably using the ACOS(-1.0) trick for better precision, and then type =K5*pi/180.0
(iv) Type =K5*PI()/180.0 and make use of the fact that PI() is an in-built function in Excel
(v) Type =RADIANS(K5) and make use of another in-built Excel function
Although method (v) is the easiest option, methods (ii)-(iv) all illustrate some useful things that may be of use to you in future.
19. Once you have converted the angles into radians (any way you like), then input =COS(L5)^2 into M5, noting that the ^ symbol means ‘raise to the power of’. Finally, apply FILL HANDLE to populate cells M6:M11 and then change the display precision of cells L5:M11 to 3 decimal places – as outlined in (13.) – to make everything look nice.
20. To create a graph, select the range F5:G16 and then select INSERT on the toolbar and then the SCATTER option. Choose SCATTER WITH ONLY MARKERS (top left icon) and a graph should appear – you can adjust the position and size of this graph with the cursor. When the graph is highlighted, select the LAYOUT tab on the toolbar and use the various options to add a title and axis labels – you can also change the font size etc by selecting options from the HOME tab on the toolbar.
21. Repeat the procedure for the data range H5:I9. Notice how a lot of the data points are bunched up towards the origin. This is due to the fact that the data in this instance spans a large range (on both axes). A log-log plot is more appropriate for this type of data and to change the axis scaling (for both x and y) select the AXES option within the LAYOUT tab on the toolbar. Finally, you can change the range of the plot by right clicking on the axis you with to modify, selecting FORMAT AXIS… and then adjusting the plot range. You can also reposition the tick labels and tick markers etc (to make things look less cluttered) in this option menu.
22. Repeat the procedure again to plot the data ranges K5:K11 vs M5:M11. To select non-adjacent columns, select the first column as you would normally and then hold down the
A SIMPLE EXERCISE
Start a new spreadsheet by selecting the SHEET 2 tab (bottom left hand corner of the screen). Next consider the triangle below:
The well-known cosine rule for triangles enables the length of side c to be determined if the lengths of a and b are known, along with the angle ? using the following formula: ?
2cos222 babca +-=
Now use the methods that have been discussed previously in this Excel primer to investigate the relationship between the length of side c and the angle ? for any given pair of lengths a and b. You should also investigate the variation in the angles a and ß with ?. Use the cosine rule throughout to do this (do not use the sine rule!)
A couple of pointers:
(i) Define sides a and b just once using the DEFINE NAME function. Vary ? between 0 and 180 degrees (remember to then convert to radians) and see how c, a and ß change, plotting three separate graphs to illustrate this.
(ii) If you are unsure whether your answers are correct, consider some limiting cases that are easy to check against (for example, think about Pythagoras and also consider what happens when one internal angle is 0° or 180°… )