- The best Online Dissertations Tutors
- +1-713-309-9213
- [email protected]

EXERCISE 1: Excel OverviewSTART: WEEK 2 (week beginning 21st Sept)TO BE MARKED BY: End of computer lab session in Week 3So far we haven’t covered enough material in the lectures to do any meaningful statisticalanalysis. This week’s exercises therefore fall into two categories:Part 1: Critical analysis of plotted dataPart 2: Some simple Excel exercisesPart of your mark for the spreadsheet exercises will depend on how coherently presentedyour 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.117300.050.10.150.20.250.30.350.1 0.2 0.3 0.4 0.5 0.6 0.7(b)0246810121416180 20 40 60 80 100Displacement measured at time,t (mm)Time (s)Comments:Comments:(c)020004000600080001000012000150 200 250 300 350 400 450 500Wavelength (nm)Absorbance (atm-1 cm-1)(d)-1.5-1-0.500.511.50 2000 4000 6000 8000 10000 12000f (degrees)Sin(f)(e)Collision impact energy experiment to determine the unkownmass of an objecty = 24.264×2 + 20.093x – 10105000100001500020000250000 5 10 15 20 25 30 35Metres per secondJoulesCollision impact energy experiment to determine theunknown mass of an objectComments:Comments:Comments:(f)y = 0.001×5 – 0.0529×4 + 1.0805×3 – 11.303×2 + 55.519x – 15.3630204060801001200 5 10 15 20 25Number of individuals (n)Average response time (ms)(g)399399.5400400.5401401.5402402.54031 2 3 4 5 6YearNational Debt (£ Billions)(h)y = 0.9143x + 640204060801001201401601800 20 40 60 80Time (s)Velocity (m/s)2001 2002 2003 2004 2005 2006? Total distance travelled = 0.9143 kmComments:Comments:Comments:Part 2: EXCEL Spreadsheet PrimerSpreadsheets 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 onmany 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 righthand corner of the screen).This Primer is designed to get you ‘up to speed’ with certain aspects of Excel (although someof you may find the first part of this rather basic). It shouldn’t take most of you more than anhour to get as far as the exercise at the end. Note that demonstrators are expecting to seeyour working for this walkthrough, not just the exercise at the endEXCEL1. On opening Excel, the centre of the screen has an array of cells which are identifiedby their coordinates – e.g. A1, D5, E10 etc. In Excel, the coordinates are referred to asthe cell REFERENCE. The name, BOOK 1, is the default name until you save the filewith 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 TOOLBAR. 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 seethe highlighted cell move.ENTERING TEXT & NUMBERS4. Highlighting cell A1 again, type EXCEL 1. Notice that is appears in the cell and alsoon the FORMULA BAR just below the MENU BAR. There is a flashing cursor at theActive Cell Formula BarTool Barend of what you typed. If you now press , the FORMULA BAR empties andthe 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 areright justified (although this can be changed using options in the toolbar). Now highlightA7 and type =A3+A4+A5 and then hit . This is a formula and it tells Excel toset 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 shouldshow a value of 6.6. The spreadsheet will update automatically. Select A5, type 10 and then hit. A7 should now show 13.SAVE YOUR WORKClick on the Windows icon in the top left hand corner of the screen and then selectSAVE AS. Save to either a removable drive or your allocated space on the networkdrive. 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 thescreen (every 10 minutes or so)QUICK WAYS OF ENTERING NUMBERS7. To enter the series 1.0, 1.2, 1.4, 1.6….2.2 is going to be rather tedious but…selectB5 and enter 1.0 and B6 and enter 1.2. Select B5 again (left click) and drag the cursordown 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 nowhave all these cells highlighted and containing the first few numbers in the requiredseries. 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. Nowselect 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 thetoolbar, by using the right mouse button to bring up an options menu or by holdingdown 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 rightmouse button options or the and keys. The sequence of numbers shouldbe displayed. This method has an advantage over the one outlined in (7.) sincechanging the entry in C5 will propagate down the rest of the column (try it). Note thatthe FILL HANDLE can also be used in place of the cut and paste option here.MATHEMATICAL FUNCTIONS9. 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 beuseful 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 C14enter =SUM( and then select C5 and drag downwards until you reach C12. Notice howthe Excel fills in the range values for you. Then type ) and hit . The sum ofcells C5 to C12 is displayed in C14.10. In C15 enter =COUNT(C5:C12). This counts the number of cells containingnumbers or formulae but not text or completely blank cells. Note that 0 (zero) iscounted because it is a number. In C16 enter =C14/C15. This should now display theaverage 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 inneighbouring 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 alwaysmake sure data is clearly labelled.VERY SIMPLE MATHS11. In F4 enter the text “x value”. In F5 start a column of numbers from 1 to 12. In G4enter the text “y=mx + c”. In G5 enter the formula =3*F5 + 3. The * is the symbol formultiplication. Select G5, grab the FILL HANDLE and drag down to the bottom of theneighbouring column of x-values. Look at the contents of each cell in column G andnotice that the constants 3 remain the same but the reference to the x-value incrementsautomatically as you go to lower cells.SIMPLE MATHS12. In H2 enter the text “Planck’s constant, h (J s)”. You can change the width of acolumn by positioning the cursor over the divisions between the letter headings, leftclicking and then dragging the bar left or right. Text effects such as superscript may beadded 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 youhave hit , the number in the cell changes to 6.63E-34. To change theprecision of a cell display, right click on I2 and them select FORMAT CELLS… from themenu that appears. A new menu will now pop up, giving you many options that willchange the appearance of your spreadsheet (such as number and text formatting, cellborders, 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 thatchanging 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 ofnumbers 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 standardletter v. Greek symbols may be added to a cell using the INSERT menu followed by theSYMBOL 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 datacolumn. The answers are clearly not all correct. Looking at the contents of each cell itshould be obvious why – the constant, h, in our E=hn equation is changing from line toline 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 beforeapplying the FILL HANDLE, although this can become rather cumbersome if wewish 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 andyou’ll see that the $ signs around the I let Excel know not to change the value inthat cell from one line to the next. This is useful as we can now change a singlevalue in cell I2 and it will automatically update all other cells that use thisconstantFor example, if we realised that our frequency values were actuallyexpressed in terms of angular frequency (rad s-1) rather than Hz (s-1), wecould simply divide Planck’s constant in cell I2 by 2p, rather than changethe 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 complicatedequations, 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 nameto the contents of the cell I2. To do this, right click on I2 and then select DEFINENAME… from the menu that appears. A new menu will now pop up and in the NAMEfield, type h and then hit OK. We can now simply use the letter h when we inputequations 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 HANDLEapproach to update cells H6 to H9. This is a useful approach as it enables formulae tobe 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 arereserved as designators for ‘columns’ and ‘rows’. This is slightly annoying whenyou want to define things like the speed of light or a radius, but you just have towork around it – for example, using ‘rad’ as a variable name for radius ratherthan ‘r’…SIMPLE MATHS PART II17. In cells K4 and L4 input the headings “Angle, q (deg)” and “cos2(q)”. In cells K5:K11then input angles from 0° to 90° in 15° increments. Before we can calculate cellsL5:L11 however we have to bear in mind that trigonometric functions in Excel requirean angle in radians rather than in degrees. Position the cursor over the box marked L atthe top of that particular column, right click and then select INSERT from the menu. Anew 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 canenter 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 functionin Excel(v) Type =RADIANS(K5) and make use of another in-built Excel functionAlthough method (v) is the easiest option, methods (ii)-(iv) all illustrate some usefulthings 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 ofcells L5:M11 to 3 decimal places – as outlined in (13.) – to make everything look nice.GRAPHS20. To create a graph, select the range F5:G16 and then select INSERT on the toolbarand then the SCATTER option. Choose SCATTER WITH ONLY MARKERS (top lefticon) and a graph should appear – you can adjust the position and size of this graphwith the cursor. When the graph is highlighted, select the LAYOUT tab on the toolbarand use the various options to add a title and axis labels – you can also change thefont 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 pointsare bunched up towards the origin. This is due to the fact that the data in this instancespans a large range (on both axes). A log-log plot is more appropriate for this type ofdata and to change the axis scaling (for both x and y) select the AXES option within theLAYOUT tab on the toolbar. Finally, you can change the range of the plot by rightclicking on the axis you with to modify, selecting FORMAT AXIS… and then adjustingthe plot range. You can also reposition the tick labels and tick markers etc (to makethings look less cluttered) in this option menu.22. Repeat the procedure again to plot the data ranges K5:K11 vs M5:M11. To selectnon-adjacent columns, select the first column as you would normally and then holddown the key while selecting the second column. The points on the graphshow the appearance of a cos2 (q) function, as you would expect. Now change thevalues in cells L5:L11 to vary by 40° from one row to the next. Notice how the graphautomatically updates as you do this. Note also that the graph is now not obviouslycharacteristic of cos2 (q). This is because the function is changing more quickly thanthe angular increment (or sample frequency) we have now chosen – this is a simpleexample of under-sampling and is something you have to be very careful of, particularlywith trigonometric functions.A SIMPLE EXERCISEStart a new spreadsheet by selecting the SHEET 2 tab (bottom left hand corner of thescreen). Next consider the triangle below:The well-known cosine rule for triangles enables the length of side c to be determined ifthe lengths of a and b are known, along with the angle g , using the following formula:c2 = a2 + b2 – 2abcosgNow use the methods that have been discussed previously in this Excel primer toinvestigate the relationship between the length of side c and the angle g for any givenpair of lengths a and b. You should also investigate the variation in the angles a and bwith 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 0and 180 degrees (remember to then convert to radians) and see how c, a and bchange, plotting three separate graphs to illustrate this.(ii) If you are unsure whether your answers are correct, consider some limiting casesthat are easy to check against (for example, think about Pythagoras and also considerwhat 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.