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!

Part 1:

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).

(a)

y = 0.8x – 0.1173

0

0.05

0.1

0.15

0.2

0.25

0.3

0.35

0.1 0.2 0.3 0.4 0.5 0.6 0.7

(b)

0

2

4

6

8

10

12

14

16

18

0 20 40 60 80 100

Displacement measured at time,t (mm)

Time (s)

Comments:

Comments:

(c)

0

2000

4000

6000

8000

10000

12000

150 200 250 300 350 400 450 500

Wavelength (nm)

Absorbance (atm-1 cm-1)

(d)

-1.5

-1

-0.5

0

0.5

1

1.5

0 2000 4000 6000 8000 10000 12000

f (degrees)

Sin(f)

(e)

Collision impact energy experiment to determine the unkown

mass of an object

y = 24.264×2 + 20.093x – 101

0

5000

10000

15000

20000

25000

0 5 10 15 20 25 30 35

Metres per second

Joules

Collision impact energy experiment to determine the

unknown mass of an object

Comments:

Comments:

Comments:

(f)

y = 0.001×5 – 0.0529×4 + 1.0805×3 – 11.303×2 + 55.519x – 15.363

0

20

40

60

80

100

120

0 5 10 15 20 25

Number of individuals (n)

Average response time (ms)

(g)

399

399.5

400

400.5

401

401.5

402

402.5

403

1 2 3 4 5 6

Year

National Debt (£ Billions)

(h)

y = 0.9143x + 64

0

20

40

60

80

100

120

140

160

180

0 20 40 60 80

Time (s)

Velocity (m/s)

2001 2002 2003 2004 2005 2006

? Total distance travelled = 0.9143 km

Comments:

Comments:

Comments:

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

EXCEL

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

Tool Bar

end of what you typed. If you now press

the highlighted cell moves to A2.

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

set the contents of A7 to be the sum of the contents of A3, A4 and A5. If you omit the =

at the start of the formula, Excel treats the expression as a string of text. Cell A7 should

show a value of 6.

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

Highlight C6 and COPY the contents to the CLIPBOARD. You can do this using the

toolbar, by using the right mouse button to bring up an options menu or by holding

down the

Now PASTE the contents of the clipboard into these cells using the toolbar, the right

mouse button options or the

be displayed. This method has an advantage over the one outlined in (7.) since

changing the entry in C5 will propagate down the rest of the column (try it). Note that

the FILL HANDLE can also be used in place of the cut and paste option here.

MATHEMATICAL FUNCTIONS

9. Excel has many in-built mathematical functions such as SIN( ), COS( ), TAN( ),

SQRT( ) (meaning Ö), 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

cells C5 to C12 is displayed in C14.

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.

SIMPLE MATHS

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

precision of a cell display, right click on I2 and them select FORMAT CELLS… from the

menu that appears. A new menu will now pop up, giving you many options that will

change the appearance of your spreadsheet (such as number and text formatting, cell

borders, cell colours etc). Select the NUMBER tab (in the SCIENTIFIC category),

change the number of decimal places from 2 to 3 and then click OK. Note that

changing the precision in a cell in this way does not affect the accuracy of a calculation.

14. In H4 enter the text “Frequency, n (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 n (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=hn 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 2p, 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

approach to update cells H6 to H9. This is a useful approach as it enables formulae to

be entered in a more ‘conventional’ format that is much easier to read and follow.

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, q (deg)” and “cos2(q)”. 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, q (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.

GRAPHS

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

show the appearance of a cos2 (q) function, as you would expect. Now change the

values in cells L5:L11 to vary by 40° from one row to the next. Notice how the graph

automatically updates as you do this. Note also that the graph is now not obviously

characteristic of cos2 (q). This is because the function is changing more quickly than

the angular increment (or sample frequency) we have now chosen – this is a simple

example of under-sampling and is something you have to be very careful of, particularly

with trigonometric functions.

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 g , using the following formula:

c2 = a2 + b2 – 2abcosg

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 g for any given

pair of lengths a and b. You should also investigate the variation in the angles a and b

with g. 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 g between 0

and 180 degrees (remember to then convert to radians) and see how c, a and b

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°… )