B27TA Computer Lab

EXERCISE 1: Excel Overview START: WEEK 2 (week begin" rel="nofollow">innin" rel="nofollow">ing 21st Sept) TO BE MARKED BY: End of computer lab session in" rel="nofollow">in Week 3 So far we haven’t covered enough material in" rel="nofollow">in the lectures to do any meanin" rel="nofollow">ingful statistical analysis. This week’s exercises therefore fall in" rel="nofollow">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 plottin" rel="nofollow">ing various data sets. In each case, comment on what is wrong (there could be more than one thin" rel="nofollow">ing in" rel="nofollow">in each in" rel="nofollow">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" rel="nofollow">in(f) (e) Collision impact energy experiment to determin" rel="nofollow">ine the unkown mass of an object y = 24.264x2 + 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 determin" rel="nofollow">ine the unknown mass of an object Comments: Comments: Comments: (f) y = 0.001x5 - 0.0529x4 + 1.0805x3 - 11.303x2 + 55.519x - 15.363 0 20 40 60 80 100 120 0 5 10 15 20 25 Number of in" rel="nofollow">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 enterin" rel="nofollow">ing experimental data, processin" rel="nofollow">ing it easily usin" rel="nofollow">ing mathematical formulae and presentin" rel="nofollow">ing in" rel="nofollow">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 enterin" rel="nofollow">ing data and performin" rel="nofollow">ing simple analysis is quite straightforward. The built in" rel="nofollow">in HELP function is also quite useful (accessed usin" rel="nofollow">ing the blue ? icon in" rel="nofollow">in the top right hand corner of the screen). This Primer is designed to get you ‘up to speed’ with certain" rel="nofollow">in aspects of Excel (although some of you may fin" rel="nofollow">ind 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 expectin" rel="nofollow">ing to see your workin" rel="nofollow">ing for this walkthrough, not just the exercise at the end EXCEL 1. On openin" rel="nofollow">ing Excel, the centre of the screen has an array of cells which are identified by their coordin" rel="nofollow">inates – e.g. A1, D5, E10 etc. In Excel, the coordin" rel="nofollow">inates 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 clickin" rel="nofollow">ing). 3. The cell A1 is outlin" rel="nofollow">ined, or if it is not, click on it once. Click on various cells and see the highlighted cell move. ENTERING TEXT & NUMBERS 4. Highlightin" rel="nofollow">ing cell A1 again" rel="nofollow">in, type EXCEL 1. Notice that is appears in" rel="nofollow">in the cell and also on the FORMULA BAR just below the MENU BAR. There is a flashin" rel="nofollow">ing cursor at the Active Cell Formula Bar Tool 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" rel="nofollow">in A3, 2 in" rel="nofollow">in A4 and 3 in" rel="nofollow">in A5. Notice that text is left justified and numbers are right justified (although this can be changed usin" rel="nofollow">ing options in" rel="nofollow">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 strin" rel="nofollow">ing 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 Win" rel="nofollow">indows icon in" rel="nofollow">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 usin" rel="nofollow">ing the disk icon at the top left of the screen (every 10 min" rel="nofollow">inutes or so) QUICK WAYS OF ENTERING NUMBERS 7. To enter the series 1.0, 1.2, 1.4, 1.6….2.2 is goin" rel="nofollow">ing to be rather tedious but…select B5 and enter 1.0 and B6 and enter 1.2. Select B5 again" rel="nofollow">in (left click) and drag the cursor down to B6. Grab the FILL HANDLE (the small square in" rel="nofollow">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 contain" rel="nofollow">inin" rel="nofollow">ing the first few numbers in" rel="nofollow">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 usin" rel="nofollow">ing the toolbar, by usin" rel="nofollow">ing the right mouse button to brin" rel="nofollow">ing up an options menu or by holdin" rel="nofollow">ing down the and keys. Now select cell C7 and drag the cursor down to C12. Now PASTE the contents of the clipboard in" rel="nofollow">into these cells usin" rel="nofollow">ing 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 outlin" rel="nofollow">ined in" rel="nofollow">in (7.) sin" rel="nofollow">ince changin" rel="nofollow">ing the entry in" rel="nofollow">in C5 will propagate down the rest of the column (try it). Note that the FILL HANDLE can also be used in" rel="nofollow">in place of the cut and paste option here. MATHEMATICAL FUNCTIONS 9. Excel has many in" rel="nofollow">in-built mathematical functions such as SIN( ), COS( ), TAN( ), SQRT( ) (meanin" rel="nofollow">ing Ö), EXP( ) (meanin" rel="nofollow">ing 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" rel="nofollow">in the range values for you. Then type ) and hit . The sum of cells C5 to C12 is displayed in" rel="nofollow">in C14. 10. In C15 enter =COUNT(C5:C12). This counts the number of cells contain" rel="nofollow">inin" rel="nofollow">ing 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" rel="nofollow">in neighbourin" rel="nofollow">ing cells D14 to D18 to in" rel="nofollow">indicate what is goin" rel="nofollow">ing 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 neighbourin" rel="nofollow">ing column of x-values. Look at the contents of each cell in" rel="nofollow">in column G and notice that the constants 3 remain" rel="nofollow">in the same but the reference to the x-value in" rel="nofollow">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 positionin" rel="nofollow">ing the cursor over the divisions between the letter headin" rel="nofollow">ings, left clickin" rel="nofollow">ing and then draggin" rel="nofollow">ing the bar left or right. Text effects such as superscript may be added usin" rel="nofollow">ing FORMAT and then FONT options on the toolbar. 13. In I2 type 6.626E-34 (This is equivalent to writin" rel="nofollow">ing 6.626 × 10-34). Note that after you have hit , the number in" rel="nofollow">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, givin" rel="nofollow">ing you many options that will change the appearance of your spreadsheet (such as number and text formattin" rel="nofollow">ing, cell borders, cell colours etc). Select the NUMBER tab (in" rel="nofollow">in the SCIENTIFIC category), change the number of decimal places from 2 to 3 and then click OK. Note that changin" rel="nofollow">ing the precision in" rel="nofollow">in a cell in" rel="nofollow">in this way does not affect the accuracy of a calculation. 14. In H4 enter the text “Frequency, n (Hz)” and then in" rel="nofollow">in H5 to H9 generate a column of numbers begin" rel="nofollow">innin" rel="nofollow">ing at 5E12 and in" rel="nofollow">increasin" rel="nofollow">ing 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 usin" rel="nofollow">ing the INSERT menu followed by the SYMBOL option. In I4 enter “Energy (J)” and then in" rel="nofollow">in I5 in" rel="nofollow">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. Lookin" rel="nofollow">ing at the contents of each cell it should be obvious why – the constant, h, in" rel="nofollow">in our E=hn equation is changin" rel="nofollow">ing from lin" rel="nofollow">ine to lin" rel="nofollow">ine of the calculation. There are 3 ways to stop this: One is to simply change the in" rel="nofollow">input formula in" rel="nofollow">in cell I5 to =H5*6.626E-34 before applyin" rel="nofollow">ing the FILL HANDLE, although this can become rather cumbersome if we wish to edit our equation at a later poin" rel="nofollow">int. A second way is to type =H5*$I$2 in" rel="nofollow">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" rel="nofollow">in that cell from one lin" rel="nofollow">ine to the next. This is useful as we can now change a sin" rel="nofollow">ingle value in" rel="nofollow">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" rel="nofollow">in terms of angular frequency (rad s-1) rather than Hz (s-1), we could simply divide Planck’s constant in" rel="nofollow">in cell I2 by 2p, rather than change the equation in" rel="nofollow">in I5 and then use this to update all other relevant cells, which becomes in" rel="nofollow">increasin" rel="nofollow">ingly in" rel="nofollow">inconvenient the larger a spreadsheet gets. The major drawback with this approach, however, is that for more complicated equations, thin" rel="nofollow">ings 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" rel="nofollow">in the NAME field, type h and then hit OK. We can now simply use the letter h when we in" rel="nofollow">input equations and Excel will automatically assign whatever value is in" rel="nofollow">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" rel="nofollow">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 annoyin" rel="nofollow">ing when you want to defin" rel="nofollow">ine thin" rel="nofollow">ings like the speed of light or a radius, but you just have to work around it – for example, usin" rel="nofollow">ing ‘rad’ as a variable name for radius rather than ‘r’… SIMPLE MATHS PART II 17. In cells K4 and L4 in" rel="nofollow">input the headin" rel="nofollow">ings “Angle, q (deg)” and “cos2(q)”. In cells K5:K11 then in" rel="nofollow">input angles from 0° to 90° in" rel="nofollow">in 15° in" rel="nofollow">increments. Before we can calculate cells L5:L11 however we have to bear in" rel="nofollow">in min" rel="nofollow">ind that trigonometric functions in" rel="nofollow">in Excel require an angle in" rel="nofollow">in radians rather than in" rel="nofollow">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" rel="nofollow">in K5:K11 in" rel="nofollow">into radians we have several options for what we can enter in" rel="nofollow">in L5 before applyin" rel="nofollow">ing 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) Defin" rel="nofollow">ine a variable ‘pi’ in" rel="nofollow">in a different cell as discussed previously in" rel="nofollow">in (16.), preferably usin" rel="nofollow">ing 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" rel="nofollow">in-built function in" rel="nofollow">in Excel (v) Type =RADIANS(K5) and make use of another in" rel="nofollow">in-built Excel function Although method (v) is the easiest option, methods (ii)-(iv) all illustrate some useful thin" rel="nofollow">ings that may be of use to you in" rel="nofollow">in future. 19. Once you have converted the angles in" rel="nofollow">into radians (any way you like), then in" rel="nofollow">input =COS(L5)^2 in" rel="nofollow">into M5, notin" rel="nofollow">ing that the ^ symbol means ‘raise to the power of’. Fin" rel="nofollow">inally, apply FILL HANDLE to populate cells M6:M11 and then change the display precision of cells L5:M11 to 3 decimal places – as outlin" rel="nofollow">ined in" rel="nofollow">in (13.) – to make everythin" rel="nofollow">ing 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 selectin" rel="nofollow">ing 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 poin" rel="nofollow">ints are bunched up towards the origin" rel="nofollow">in. This is due to the fact that the data in" rel="nofollow">in this in" rel="nofollow">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 scalin" rel="nofollow">ing (for both x and y) select the AXES option within" rel="nofollow">in the LAYOUT tab on the toolbar. Fin" rel="nofollow">inally, you can change the range of the plot by right clickin" rel="nofollow">ing on the axis you with to modify, selectin" rel="nofollow">ing FORMAT AXIS… and then adjustin" rel="nofollow">ing the plot range. You can also reposition the tick labels and tick markers etc (to make thin" rel="nofollow">ings look less cluttered) in" rel="nofollow">in this option menu. 22. Repeat the procedure again" rel="nofollow">in 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 selectin" rel="nofollow">ing the second column. The poin" rel="nofollow">ints on the graph show the appearance of a cos2 (q) function, as you would expect. Now change the values in" rel="nofollow">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 changin" rel="nofollow">ing more quickly than the angular in" rel="nofollow">increment (or sample frequency) we have now chosen – this is a simple example of under-samplin" rel="nofollow">ing and is somethin" rel="nofollow">ing you have to be very careful of, particularly with trigonometric functions. A SIMPLE EXERCISE Start a new spreadsheet by selectin" rel="nofollow">ing the SHEET 2 tab (bottom left hand corner of the screen). Next consider the triangle below: The well-known cosin" rel="nofollow">ine rule for triangles enables the length of side c to be determin" rel="nofollow">ined if the lengths of a and b are known, along with the angle g , usin" rel="nofollow">ing the followin" rel="nofollow">ing formula: c2 = a2 + b2 - 2abcosg Now use the methods that have been discussed previously in" rel="nofollow">in this Excel primer to in" rel="nofollow">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 in" rel="nofollow">investigate the variation in" rel="nofollow">in the angles a and b with g. Use the cosin" rel="nofollow">ine rule throughout to do this (do not use the sin" rel="nofollow">ine rule!) A couple of poin" rel="nofollow">inters: (i) Defin" rel="nofollow">ine sides a and b just once usin" rel="nofollow">ing 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, plottin" rel="nofollow">ing three separate graphs to illustrate this. (ii) If you are unsure whether your answers are correct, consider some limitin" rel="nofollow">ing cases that are easy to check again" rel="nofollow">inst (for example, thin" rel="nofollow">ink about Pythagoras and also consider what happens when one in" rel="nofollow">internal angle is 0° or 180°… )