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
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 , the FORMULA BAR empties and
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 . This is a formula and it tells Excel to
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
. A7 should now show 13.
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 . C6 should show a value of 1.2.
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 and keys. Now select cell C7 and drag the cursor down to C12.
Now PASTE the contents of the clipboard into these cells using the toolbar, the right
mouse button options or the and keys. The sequence of numbers should
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.
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 . The sum of
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”,
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 , the number in the cell changes to 6.63E-34. To change the
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
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 and then apply the FILL HANDLE
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
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
(iv) Type =K5*PI()/180.0 and make use of the fact that PI() is an in-built function
(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 key while selecting the second column. The points on the graph
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°… )
Do you need a similar assignment done for you from scratch? We have qualified writers to help you. We assure you an A+ quality paper that is free from plagiarism. Order now for an Amazing Discount!
Use Discount Code “Newclient” for a 15% Discount!
NB: We do not resell papers. Upon ordering, we do an original paper exclusively for you.
The post B27TA Computer Lab – custom papers appeared first on The Nursing TermPaper.