New User Special Price Expires in

Let's log you in.

Sign in with Facebook


Don't have a StudySoup account? Create one here!


Create a StudySoup account

Be part of our community, it's free to join!

Sign up with Facebook


Create your account
By creating an account you agree to StudySoup's terms and conditions and privacy policy

Already have a StudySoup account? Login here

MATH 140 Week 6 Notes

by: Brandon Bush

MATH 140 Week 6 Notes MATH 141

Marketplace > University of Maryland > MATH 141 > MATH 140 Week 6 Notes
Brandon Bush

Preview These Notes for FREE

Get a free preview of these Notes, just enter your email below.

Unlock Preview
Unlock Preview

Preview these materials now for free

Why put in your email? Get access to more of this material and other relevant free materials for your school

View Preview

About this Document

Its a physics document
Calculus II
Class Notes
Excel, Spreadsheets
25 ?




Popular in Calculus II

Popular in Department

This 17 page Class Notes was uploaded by Brandon Bush on Wednesday September 21, 2016. The Class Notes belongs to MATH 141 at University of Maryland taught by Swarnava in Fall 2016. Since its upload, it has received 41 views.


Reviews for MATH 140 Week 6 Notes


Report this Material


What is Karma?


Karma is the currency of StudySoup.

You can buy or earn more Karma at anytime and redeem it for class notes, study guides, flashcards, and more!

Date Created: 09/21/16
Exercise 1 Introduction to Excel I. Purpose The main purpose of this exercise is to introduce you to the computer spreadsheet program Excel and show you how it can be used to analyze and plot data. II. References In the references section of each exercise, you will find a reading assignment that you should complete before the lab. Please look at the references section for Exercise 2, and complete that reading before coming to next week’s class. III. Equipment Windows PC with Excel and a network connection IV. Getting Started Saving your files so you can find them again: You will be using Excel spreadsheet files to record most of the work you do in this course, and these files will initially be written to the hard disk of the computer at your lab station. Since the computer is shared with other students, you should create a folder for yourself inside the “My Documents” area and store your files in yourpersonal folder. The following instructions will help you set that up: 1. Double click the “My Documents” icon in the lower left corner of the Desktop. 2. In the “File and Folder Tasks” section of the My Documents window, click on “Make a new folder”. (Or, pull down the File menu and select New ►Folder.) 3. A new folder should appear, highlighted. Type your full name in as the name of the folder. While working in Excel, you can save your files in your personal folder by going to Excel’s “File” menu, and selecting “Save as…”. When you select this, a window should pop up showing the contents of the My Documents area. Inside the window, you should see the folder with your name. Double click on that folder. At the bottom of the window, you will see a place where you can enter a name for the file. To make it easy for you (and us) to keep track of your files and what is in them, name your file in the following way: yourname_excercisenumber_revisionnumber.xls. So, the first lab might have the name JaneDoe_Ex1_v1.xls. Click “Save” and your lab will be saved to disk. You are strongly encouraged to save your work from time to time during the lab period so that a computer glitch or power outage will not cause you to lose all your work. 1 Keeping Copies of Your Files At the end of each lab period, you will “Submit” your spreadsheet file on the ELMS web site so that it can be graded. You should also take a copy for yourself, either on a USB flash drive or by emailing the file to yourself. This is necessary for a few reasons. First, you may have to finish the last part of the in-class exercise if you ran out of time in class. Second, your copy serves as a backup in case the ELMS submission failed or is lost due to a computer problem or a human mistake. (There may also be a copyleft on your computer in the lab, but you shouldn’t count on that since it is a shared computer.) V. General Introduction to Spreadsheets In Physics 174 you will use computer spreadsheets to record and analyze your data. This section discusses what a spreadsheet is and how it works. Even if you have used some type of spreadsheet before, please readsections V and VI in order to make sure that you know the features that will be important in this course. If you haven’t used a spreadsheet before, please read these sections carefully. In either case, go ahead and use Excel on your computer to experiment with any features which aren’t clear from the examples in the text. Section VII is the beginning of the portion of this lab that you must turn in to be graded. The spreadsheet file that you will turn in at the end of the lab period should include all of the data, tables, and plots that you create during the exercise. (Any leftovers from experimentation in Sections V and VI are fine to include; they simply won’t be graded.) It should also include the answers to any of the questions that start with QUESTION X (where X is a number). Please put the answers to these questions in your spreadsheet, labeled clearly with the question number. Please use complete sentences and correct spelling when answering the questions. Spreadsheets - What they are and how they work. A spreadsheet is a computer program that turns your computer screen into a smart piece of paper. It removes much of the grunt work associated with repetitive calculations and lets you easily see the results of your work. We have chosen to use spreadsheets in the physics labs because they reduce the amount of time needed to look at and understand the data taken in the labs. Traditionally accountants have used spreadsheets to do bookkeeping and budgets, but they make outstanding tools for scientists as well. With a spreadsheet, we can enter raw data, manipulate it and plot it all with a few simple commands. Spreadsheets are especially useful because of their built-in ability to plot data. The key thing that makes spreadsheets so powerful is that whenever you change something (a number or a formula), every number gets automatically recalculated. So if you make a change in a formula or re-measure a quantity all numbers connected to it are automatically updated. The best way to learn how to use spreadsheets is to practice using them. This tutorial will explain the basics and give you a few exercises to do that will get you started. 2 The Basics A spreadsheet consists of a collection of cells, like a big table. The cells are labeled by their column and row location. For example A4 is in the first column, the fourth cell down. Here is an example of what an EXCEL 2010 spreadsheet lookslike on the screen. A cell can contain either text(e.g. a label), a number or a formula. (A formula may also be called an “expression”.) If you put text in a cell, it will display the text. See cell A1 in Figure 1 below. You can also enter numbers like 15 (see A2) or formulas (see B1 and B2). Excel interprets the contents of a cell as a formula if they start with an “=” sign. Cell B1 containsa very simple formula which is of course equal to 6. If you look in Figure 2you will see that the spreadsheet shows the number 6 instead of the formula =3*2. The formula in cell B2 is different. It says =2*A2. What this means is twice the value of the cell A2. Since A2 is currently 15, B2 displays the value 30. Cell A3 has a similar formula =A2+1 so it shows 16. One of the spreadsheet’s greatest features can be seen when the number 15 is changed to 20. B2 automatically gets changed to 40, A3 gets changed to 21, and B3 becomes 42. This is called automatic updating. 3 Figure 1- Here we show what you type into various cells of the spreadsheet: A B C D E F G 1 text =3*2 2 15 =2*A2 3 =A2+1 =2*A3 4 Figure 2- Here we show what the spreadsheet displays on the screen: Another useful feature of spreadsheets is the ability to replicate formulas. Suppose, for example, you want to extend the above spreadsheet so that the numbers in column A go from 15 and 16 all the way to 25. Now, of course you could type the number 17 into cell A4, 18 into cell A5, etc., but this is unnecessary. You can use the copy-paste feature to replicate the formula in cell A3 to the cells A4 to A12. When you copy and paste cells, unless you do something special, the formulas change, but in a way that is useful. Cell A3 had the formula =A2+1 in it (the equal sign was there to tell the spreadsheet that this is a formula and that A3 is a cell number instead of text). Now what you would like in cell A4 is not the same formula as A3, but you would like it to say =A3+1 (not =A2+1). That way it will become 17. When you tell the spreadsheet to paste the formula, the formula is automatically changed to refer to the cell with the same relative position with respect to the cell into which the formula is pasted. If you copy the formula in cell A3 to cells A4 through A12, the formulas will become =A3+1, =A4+1, =A5+1 etc. all the way to =A11+1. If you copy B2 to C2, the formula changes in a similar manner, from =2*A2 to =2*B2, and in this example (if cell A2 contains 15) the value of cell C2becomes =2*(2*15) or 60. The key is that you rarely have to type a formula more than once even if it is used frequently. Also if you have a row of formulas that you want to change, you can make the change in one cell and copy it to all the other cells. Spreadsheets are extremely useful for data analysis in physics because of their ability to plot data. Rather than having to draw a graph by hand you can just select the numbers you want to plot and the spreadsheet will do the work. When you make a change in the numbers or formulas, the graph changes automatically. Still another feature of the spreadsheet is the ability to calculate many statistical functions which are normally time-consuming to do by hand. Several examples, which are particularly useful in physics analysis, are: the average, the standard deviation and least-square fits to a straight line (known as a linear regression.). 4 VI. Introduction to Excel 2010 1. Moving around the spreadsheet You can move from cell to cell by using your mouse, the cursor keys, or by using the PgUp and PgDn keys. There are also sliding scales on the right side and bottom of the worksheet which you can move with the mouse to move around on the worksheet. You can also go directly to a specific cell by pressing the F5 key and entering the cell address. The cell addresses in Excel are in the form " sheet1:C23 ". This is cell C23 on sheet1. Excel lets you have many worksheets in the same “notebook” which can all be connected to one another. The small tabs on the bottom of the page let you switch from sheet to sheet . (It is also possible to have more than one notebook open at the same time. They cannot be connected, but the window menu at the top of the sheet lets you toggle between notebooks.) 2. Entering Information To put any information in a cell (text, number s, formulas), move the cursor to that cell and type the information. When you start to type, you will see that what you type appears on the top left of the page on the third line (called the display line, see last line on figure above). If you press enter after typing something it will enter it on the sheet or if you press the escape key it will abort the entry and leave the cell unchanged. When you are entering an expression in a cell, you can edit that expression by moving the mouse so that the pointe r on the screen is pointing to the spot you want to change (on display line). Pressing enter or moving the cursor to another cell enters the expression. If you want to change a previously entered expression you can either move to the cell and retype the whole thing or move the mouse to the display line, click the left mouse button, and then revise it. 3. Entering Text Putting labels on your data lets you make your spreadsheet readable to you and to others. While there are some limitations (such as it is really hard to get Greek letters), it is really simple to set up nice looking labels. If you just type words into a cell they will show up in the cell. For example if you type “This is text” in cell A1 it will show up. If the text is longer than the ce ll width (which is adjustable) the whole text will show up if there are empty cells to the right of the cell with the text in it. If the string is long and the cell next to it is occupied the string will show up cut off, but it is all still there. 4. Entering Numbers Numbers are simply entered in a cell. A number like 15 can just be typed and its value is entered in the cell. If the number is negative just start it with a minus sign. 5 - Significant Figures EXCEL does not automatically keep track of significant figures, which is a serious shortcoming in science and engineering. Instead, you can manually format cells to display the desired number of digits after the decimal point. To do this, click on the cell, then click on the Home tab. Find the Nu mber section of the menu (see Figure 3) and click on the buttons for increasing or decreasing the number of digits displayed - each click on the button changes the number of digits displayed by 1. Other options in the number section of the menu allow you t o change the number format to scientific notation, percentage, date, or other common formats. If 5 Figure 3. Buttons for changing the number of digits displayed in a cell. you need to set the number of digits displayed in front of the decimal point, you have to switch to scientific notation by going to the pull-down tab in the Number section. 6. Entering Formulas Formulas can be simple numeric like =2*3, or they can include more complicated expressions involving other cells a nd statistical and mathematical functions. A formula like =2*B5^3 follows the rules of programming where this means 2 times the contents of cell B5raised to the third power. Use parenthesis in a natural way - for example =1/((1/B3)^2+(1/B4)^2)^(0.5). The spreadsheet has mathematical functions such as Sine (=sin), Cosine (=cos), and Square Root (=sqrt), which are almost the same as in a normal mathematical expression. =sin(C3) takes the Sine of the contents of cell C3. Many other functions exist. The list of available functions can be viewed by clicking on the function button fx on the top menu, and choosing “all”. (If you want a description of the function, click on its name. A description will appear at the bottom of the function box.) Functions, suc h as the sine, take normal numerical arguments (in radians). Some statistical functions take lists of cells or a range as they are called. An example of this type of function is the average(=average). The average function will compute the average of a l ist of numbers or cells. So if you enter =average(5,6,7,8) the value of the cell will be 6.5. This type of function is most useful when you give it a list of cells. If you wish to find the average of a column of numbers say A1 to A20, you just enter =ave rage(A1:A20), where the colon between A1 and A20 is Excel's way of saying A1 to A20. Most spreadsheet functions are “smart” so that if your range includes empty cells they are not counted in the average or other statistical function. (Note: there is a distinction between empty and zero.) The principal spreadsheet functions that you will be using are: =average(…) =sqrt(…) =stdev(…) =count(…) =sum(…) =max(…) =min(…) =slope(…) =intercept(….) 7. Pointing at Cells When you first start using a spr eadsheet, you often find yourself typing cell numbers into equations and expressions. This is unnecessary and can lead to errors. The easiest (and perhaps best) way to put a cell number in an expression is to point at it. For example if you have a number in Cell A2 and are writing an expression in which you want the cosine of the number in 6 Cell A2. Simply type =cos( then move the mouse or cursor to cell A2. At this point your expression you are typing will look like =cos(A2. At this point do not pres s enter because you have not completed the expression. Just type the closing parenthesis and then you will have =cos(A2) . 8. Selecting Ranges You can also point at groups of cells. This is useful for expressions such as the average or standard deviation that expect ranges as arguments. It is also useful when you want to mark a region that needs to be moved or copied. Anytime you need a range of numbers, use the mouse to move the cursor to the first cell in the range and hold the left mouse button. N ow “drag” the cursor to the last cell in the range and release the button. You should now have the range highlighted and displayed in the display line. 9. Copying a formula EXCEL allows you to copy the contents of one cell to another. However, be warned! It is a “smart” copy, and will often change the contents during the copy. Most of the time you will find this an extremely useful feature, as you will see below. However, sometimes it can do unexpected things. Whenever you do a copy using EXCEL, be sure to check and make sure the program did what you wanted before you go on! Now, as an example of the program’s “intelligence”, suppose you want to find the Sine of a column of numbers, say A1:A20. To begin, enter in B1 the formula =sin(A1). Now copy the formula to the entire range B1:B20. To do this put the cursor on B1 and press the copy button (on the keyboard type either Ctrl -C, or move to menu, click on edit and select copy). Now it expects a cell or range to be the object to be copied. Use the techniques described above to select the output range B2:B20. (It's OK to copy the formula onto itself). Hit enter and your formula will be replicated in the entire range. Note that in cell B2 the formula is now =sin(A2), not A1 anymore. This way c olumn B has the Sine of the values in column A. If you had something useful in D1 as well you could select the range B1..D1 and copy the row down and all the expression would be copied. There is a special case in copying where you do not want the cell l ocation to change when you copy. There is an easy way to fix a cell location so that it doesn’t change when copying. This is done by specifying the cell name preceded with the dollar sign (e.g. $B$1). There are two dollar signs in the expression shown here because it is possible to fix the column and row or both. If you just wanted to fix the row, you would use B$1. The “B” would then change according to the “smart copy" rules during the copy. If you just wanted to fix the column, you would use $B1. An example where you might want to use this is: You have a column (A1:A20) of measured accelerations "a" and you want a column of forces and all objects have the same mass. If the mass "m" is in cell D1 your formula for the force "F" in B1 might be =$D$1*A1. This way when you copy the formula to B2 it will be =A2*$D$1. If you didn’t use $ signs the formula would have be $A2*D2 which would be wrong if the mass was in D1. 10 - Plotting Data You can use EXCEL to plot one column of numbers (the y coordina tes) versus the numbers in a second column (the x coordinates). For example, suppose you want to plot y = sin(x) versus x. To do this, you will need to put some x -values into one column and then put the sine of them in a second column. Start by entering the label “X (rad)” into cell D1. Next enter 0 7 into cell D2. Then in D3 enter =D2+0.3. Next copy this formula from D2, D3, ... D20. Next enter the label “y=Sin(X)” into cell E1. Now in E2, enter =SIN(D2). Copy this formula to cells E2,...E20 (see Figure 2). To plot this data, go to the top of the spreadsheet and click on the Insert tab (see Figure 2). Now use your mouse to select cells D1 to D20 and cells E1 to E20 (to select the group of cells just click on cell D1, hold the left mouse button down a s you drag the mouse to cell E20, and then release the mouse button). Then look for the section of the menu at the top of the spreadsheet that says Charts and click on Scatter (see the note below about why you should always use Scatter plots). You will be presented with a sub- menu of different types of scatter plots (just points, just lines, lines and points, etc.), choose any one of these by clicking on it. You should now see a plot and notice that the menu at the top of the spreadsheet has changed. Click on the Chart Layout menu and try selecting Layout #10, which has a nice format. All of your plots should have labels on the x and y -axes. In this case, Layout #10 just has default labels that say “Axis Title”. To change the axes labels , just click on them and type in “X (rad)” for the x - axis label and “y=Sin(x)” for the y-axis label. Your Spreadsheet should now look like Figure 4. If your plot is on top of the x and Sin(x) columns, just click on the chart and you can move it to the side with the mouse. Notice that when you click on the chart, Excel will highlight columns that are being plotted (see Figure 4). This is very useful because a common and confusing mistake is to plot the wrong set of numbers. To avoid this mistake, you should get into the habi t of clicking on your charts and letting EXCEL show you what cells it is using for the plot. There is another serious mistake to avoid when making plots. Although there are many different types of charts in Excel, only a Scatter chart will plot an x -value and a y-value as a point at location (x,y) in the Cartesian plane. Some of the other types of plots can fool you into thinking that they are plotting (x,y) points, but they are not. For example, a Line chart will actually plot the y -values versus the orde r in which they are given and it does not use the x - values at all. To be clear, you should only use Scatter plot for all of your plotting - never use a Line plot or a Bar chart or any other type of chart in EXCEL. 11 - Adding Another Curve to an Existing Plot Suppose you have already made a plot of your data and you want to add a second curve that shows some other measurements or a theoretical curve. One way to do this is to right click on the plot and then click on Select Data in the pop-up menu that appears. A new pop- up menu will open and you should click on the Add button. Once you do this, another pop- up menu will appear which will allow you to enter a series name and select the x and y columns for the second data series. Once you fill these in, just click OK and you should see your new plot with both curves. Sometimes you will make a plot and forget to add axes titles. One way to go back and add labels and axes titles to a graph that does not have any, is to click on the graph and notice that a new menu appears at the top of the spreadsheet called Chart Tools . Select the Layout tab and then select Axes Titles from this menu and figure it out from there. An easier way to add axes labels is to click on the chart and select the Design tab in the Chart T ools menu. The Design menu will appear and you can then pick from one of the Chart Layouts that has axes labels already in them (edit the default titles by clicking on the label and typing). In the example shown in Figure 1 we selected Layout 10. 8 Figure 4. Scatter plot of Sin(x) versus x. Note that the chart was clicked on and this caused Excel to highlight the x and y columns being plotted and display the Chart Tools menu. 12 - Adding Error Bars Many of the plots you will make in Physics 174 will need to have error bars. Unfortunately it is easy to add incorrect error bars using EXCEL 2010 and not so easy to add correctly sized error bars. Before you can add error bars, you need to make a plot of your data (see the previous section) and you need a column with your error bars in it. For example, in Figure 5 column F shows values for ∆y the uncertainty or error the y values. Once you have a plot and a column with values for the error bars, click on one of you data points on the plot. This will select all the points in that data series, EXCEL will also highlight the Chart Tools menu, and a small window will pop up. Ignore the pop- up window and instead go to the Chart Tools menu at the top of the spreadsheet and click on the Layout tab. You should now be able to see a button labeled error bars on the right side of the menu. Click on the error bars button, and go all the way to the bottom of the menu and click on More Error Bar Options. As soon you click on the More Error Bar Options, EXCEL draws some completely wrong x and y error bars on the plot and opens up a new window with some options in it (see Figure 5). Try clicking on one of the x error bars, and then click on one of the y error bars, and notice that the window toggles between options for the x error bars and options for the y error bars. Suppose you just want error bars in the y-direction (vertical) but EXCEL has drawn these 9 Figure 5. Adding error bars to a plot. To get to this view, the user has clicked on a data point on the chart and then selected the Layout tab, then clicked on the Error Bars button, and then selected “More error bar options”. The user then selected Custom on the pop-up menu and hit the specify value button and selected the numbers shown in column F under the “error Y” label. error bars in both x and y. To get rid of the x error bars, just click on one of the x error bars on your plot and hit the delete key. Now all you need to do is get EXCEL to plot the error bars in y with the correct height. To do this, just click on one of the y -error bars and examine the choices in the window (see Figure 5). Make sure that Both is selected so that the error bars go above and below the point. Next go to the Error Amount section, click on the Custom option and then click on the Specify Value button (see Figure 5). A small window will open up with boxes for “ positive error values” and “negative error values ”. Just click on the “positive error values” area, delete whatever is in it, and then select the cells that have the y error bar values (in column F). Repeat this process for the negative error values (just select the same column of error bar values that you did for the positive error values), then click OK and the error bars will show up. 13 - Modifying Charts, Adding Titles and Labels, and Copying charts If you want to change something in a chart, just try clic king on it - you can change the axis limits, the chart background, the type of chart, the curves being displayed, the grid, the tick marks, the maximum and minimum range being plotted, and the error bars. To change the x -axis label, the y-axis label or title on a chart, just click on it and type in the new label or title. Clicking on one of your points in the chart lets you change the symbols used in the plot, as well as their size and color, and the line -width and color of the line in the plot. If you forg ot to add an axes labels or a chart title, just right click on the chart, then go to theChart tools area on the menu 10 bar, click on the Layout tab and hit the chart title or axis titles buttons. It can take a fair amount of time to get a chart with the ax es properly labelled, error bars in place and everything else looking right. If you have to make a second plot of some other data, you could just go through the same steps outlined above to make a plot from scratch. However, there is an easier way. After you have made one plot, you can click on it (this selects it) and then click on the Copy button on the Home tab. Next click on an empty cell and hit the paste button. This creates a copy of your plot. Of course it is plotting the same thing as your first pl ot, but it is very easy to change what is being plotted. Click on one of the data points and EXCEL will highlight the cells that are being plotted by drawing a cyan box (x-coordinates) or blue box (y- coordinates) around them. To change what is being plott ed on the x-axis, just move your mouse over the border of the cyan box, click and hold, and then move your mouse until it is over the x - coordinates for the plot that you would like to create - the cyan box will follow - release the button when the box is over the coordinates you want to plot. You can choose a new set of y - coordinates the same way. 14 - Histograms In section VIII below we will walk you through how to use EXCEL to make histograms. A histogram is a plot with frequency (number of times that something specific happened) on the y-axis and some variable of interest on the x -axis. The range that is displayed on the x -axis is divided into intervals called bins. Usually, the bins represent equal intervals in the x variable. The y axis values rep resent the number of occurrences of data whose x value is lies within the bin interval. Often it is easier to see a pattern in the data by using binning that is relatively coarse, so that most bins contain more than one entry. Histograms are a very important tool in experimental physics. You will learn more about why this is so in the next lab. However, histograms have already probably played an important role in your life. After an exam has been graded, many instructors post a histogram that has as its x-axis the possible exam scores, and as its y-axis the number of students that obtained that score. Once you know your score, you can look at the histogram, and see how well you did compared to the other students in your class. By reading the histogram , you can answer questions such as: How many students did better than me? Did I do better than the average student? Am I in the top 10 percentile of students? 15 - The Solver (will be first used in Exercise 4) The Solver is an EXCEL routine that allows you to minimize or maximize the content2 of a cell by adjusting some other cells. In Physics 174 you will use a technique called χ -fitting, for example, to fit a straight line to some data. This will require that you minimize a cell containing χ (which “measures” the difference between your data and the fit) by varying the cells containing the slope and the intercept. To run the Solver, click on the Data tab and go all the way to the right side of the menu bar. You should see the word Solver. If you don’t see it, then you probably did not install the Solver on your computer (it is an add- in), and you will need to install it. If need to install the Solver, click on the File menu tab, then select Options from the list on the left, then select Add - ins from the new list on the left. Another window will open and you should now be able to find and add the Solver. Once you have the Solver installed, go back and click on the Data tab, go all 11 the way to the left and you should now be able to see the Solver bu tton. Click on the button and the Solver Parameters window will open (see Figure 6). Enter the cell that you need to minimize into the “ Set Objective”, select Min to minimize this cell by checking the box under the objective box, and then enter the cells t hat you need to vary into the box labelled “ By varying cells:” - you can put one cell or a range of cells into this box. Finally, it is very important to uncheck the box that says “ make unconstrained variables non- negative” - generally you should expect a fit parameter could be negative (see Figure 6). When you are all done, click on the Solve button at the bottom of the window and EXCEL will work out the minimum and display it in a new window. Depending on how large your data set is and the complexity of t he fit, this may take a fraction of a second or a few minutes. The routine is quite powerful and has many options which are useful but outside the scope of Physics 174. Figure 6. Solver parameter window showing that cell $E$10 will be minimized by varfying cells $D$1 to $D$2. Note that the “Make Unconstrained varaibles non-negative” box has been deselected. The Best Way to Learn about Excel By the way, the best way to learn about a spreadsheet program is to play with it! Feel free to figure out for yourself what the different tools and buttons do. Microsoft has put a lot into Excel and you may be surprised at how powerful and sophisticated it is. On the other hand, it does have some quirks which are best learned through first-hand experience. 12 VII. Tutorial Exercise on Excel In this tutorial exercise you will work through some examples of how to use Excel. This is the beginning of the part of the exercise which will be graded. Step 1 – If you haven’t already done so, turn on the computer and start Excel. Step 2 – On Sheet 2, type in your name and section starting in cell A1 Step 3 - Set up a folder on your computer in which to save your files: If you haven’t already done so, create a folder to hold your files by following the instructions in Section IV. Now, back in Excel, click on the “File” menu and select “Save as…”. When you select this, a window should pop up showing the contents of the My Documents area. Inside the window, you should see the folder with your name. Double click on that folder. At the bottom of the window, you will see a place where you can select a name for the file. To make it easy for you (and us) to keep track of your files and what is in them, name your file in the following way: yourname_exercisenumber_revisionnumber.xls. So, your file for this exercisemight have the name JaneDoe_Ex1_v1.xls. Click “Save” and your lab will be saved to disk. Step 4 - Enter some data Assume that you have performed an experiment to measure the speed of a cart rolling down an inclined plane. The cart has mass 0.25 kg and you made speed measurements at 1 second intervals from 0 to 10 seconds. Label cell A6 “Time (s)” and cell B6 “Speed (m/s)”. Continue entering the rest of the data from the following table: Time (s) Speed (m/s) 0 0.0 1 0.8 2 1.5 3 1.6 4 2.5 5 2.7 6 3.2 7 3.9 8 4.5 9 5.0 10 5.5 Step 5 - Enter the label “Mass (kg)” centered in cell B3 and put the value 0.25 in cell B4. Step 6 - Now create a new column starting with the label “K.E. (J)” in cell C6. Step 7 - In cell C7, enter the formula for the kinetic energy of the object with the speed shown in 2 cell B7. (Remember that kinetic energy is 1/2 mv ). 13 Step 8 - Check that you have fixed the cell location of the mass using $. In your formula in C7 you should have =0.5*$B$4*B7^2. Don't forget to type the equals sign. Step 9 - Copy the formula in C7 to the range C7:C17 and check that it looks OK. Step 10 - Now plot the speed versus time data on a graph (see the previous section for detailed instructions on how to plot the data). Be sure to include a title and axis labels, including appropriate units. Step 11 - Check that your spreadsheet and the graph look like the one shown below. If you haven’t already done so, save your spreadsheet. Step 12 –Make another plot of KE vs. time, with appropriate title, axis labels and units. Step 13 – Create a “text box” in your spreadsheet and type the answer to the following question into it. (To create a text box, clink on “Insert” on the main toolbar and find the textbox icon). Put “Question 1:” or “Q1:” at the beginning of your answer to identify it. QUESTION 1: In theory, for this experiment, the speed of the cart should be proportional to the elapsed time. Do the measurements support that theory? Step 14 - Use the graphics pad in EXCEL. To do this, click on the Review tab and then go the right side of the menu and click on Start Inking (see Figure 7). Note that the Start Inking tab will only appear if you have a graphics pad plugged in or are using a tablet. The Ink Tools menu will then come up. You can then click on one of the pens or highlighter buttons, pick up the electronic pen and start drawing on the pad. Now use the yellow highlighter to highlight the cells or textbox that holds your answer to QUESTION 1. Figure 7. After clicking on the Review tab, the Review menu opens. Notice the Start Inking button on the right. 14 Call your instructor over and get your check sheet signed. VIII. Using Excel to make Histograms In this exercise, you will see how to make a histogram with Excel. Here are the grades from the final exam of a class of twenty five students. Student # Score 1 80 2 79 3 83 4 90 5 75 6 83 7 80 8 82 9 80 10 70 11 85 12 80 13 76 14 73 15 80 16 81 17 79 18 100 19 93 20 75 21 76 22 76 23 71 24 95 25 95 (1) On sheet 3, enter this data into your Excel spreadsheet (2) Find the maximum and minimum data points by using the MAX and MIN functions and use the COUNT function to find the total number of data points, N. (see section VI for instructions on how to use functions in Excel). Do this in some cells below your main data table, and label what is what. (3) You now need to set up some "bins" which the numbers will be grouped into. Label a column “Bins”. Under this, enter 100, 95, 90, 85, … down to the minimum value in your data. 15 Tips for Choosing Bins Choosing bins for a histogram is more of an art than a science. The goal is to create a histogram which, at a glance, gives a “big picture” view of the data. There are a few basic principles that people tend to follow: - bins should start at about the maximum data value and end near the minimum data value - choose simple bins, such as integers or multiples of 2, 5, or 10, etc. - Don't make too many bins. If you do, many of them will be empty or just have one or two events in them. It is not a good idea to have less than about 5 events, on average, in each bin. So if you have N=25 scores you are trying to put into bins, you should choose no more than about N/5=5 bins. Since the scores cover values that start at Max and go down to Min, the Bin size is about 5*(Max-Min)/N. Actually, Excel will choose bins for you automatically if you don’t give it a list of values, but you will generally be better off choosing the binning yourself. (4) Now that you have your bins set up, click on "Data" in the main toolbar. Then click on "Data Analysis". When this window opens, click on "Histogram" and a new window should open. (5) Specify the Input Range, i.e. the list of data values that you want to histogram. The easiest way to do this is to click in that field, delete any existing text there (if it doesn’t already refer to the correct range),and then use the mouse to select the relevant cells in your spreadsheet. (6) Specify the Bin Range, i.e. the list of bin values that you created in step 3. (7) Click next to “Output Range” and set it to refer to some empty cell on your spreadsheet. Excel will create a little table there listing the number of events in each bin. (8) Then click on the "OK". Use the bin-frequency table to make a scatter plot of this histogram. (9) Take the scatter plotand make it larger, so that you can clearly read off the number of events in each bin. (10) Create a “text box” in your spreadsheet and type the answer to the following question into it. QUESTION 2: Take a close look at the frequency values that Excel calculated for each bin. What exactly do they mean? For instance, what is the number in the bin labeled “75”, and what does that say about the original list of exam scores? Call your instructor over and get your check sheet signed. 16 VIII. Finishing Up When you have finished the exercise, or at the end of the class period, be sure to Save your spreadsheet so that the file you turn in includes all the work you’ve done! Don’t worry if you didn’t quite finish everything - you have a week to finishanything you missed and turn in a revised spreadsheet before your lab section meets againin a week. Whether or not you finished everything, you must turn in what you have done before you leave the lab. Log on to the ELMS (Canvas) web site at to turn in your spreadsheet file. PHYS174 should appear in the “My Courses” panel; click on it to go to the course web pages. To submit your spreadsheet, click on the “Assignments” menu item and then click on the link for Exercise 1. Find “Attach local file” click on the “Browse…” button beside the text field and select your spreadsheet file. Then click on the “Submit” button to submit the assignment for grading. Note: if you click on “Save” instead of “Submit”, then your file is uploaded but not submitted; in this case you can upload a revised version later, but you must click on Submit (before the deadline) to actually submit your completed assignment (including any homework). Your TA and instructor cannot access your file until you Submit it. Take a copy of your spreadsheet file for yourself on a USB flash drive, or by emailing it to yourself. (You can also retrieve a copy of any file that you’ve submitted to ELMS) When you are done, turn in your check sheet. IX. Homework (Submit answers to Expert TA before the deadline) To submit answers to the Homework Questions, you must log on to your area in Expert TA. Questions and multiple choice answers on Expert TA may vary from those given below. Be sure to read questions and choices carefully before submitting your answers to Expert TA. Typically instructors will have set Expert TA to allow you five attempts to answer a question, except for true/false questions (just 1 attempt allowed) and multiple choice questions (no more than N-1 attempts if there are N choices). #1. A cell in an EXCEL spreadsheet contains $A$5. What does it mean? (pick one) (a) Cell A5 is formatted as a currency. (b) Cell A5 is a fixed cell. (c) This means that cell A5 is text rather than a numerical input. (d) This means the cell contains a formula. (e) This is EXCEL’s way to indicate the cell contains an error. #2. Given an x -coordinate and a y -coordinate for a point, which type of chart in EXCEL will actually plot the point at its Cartesian coordinates? (pick the best answer) (a) line chart, (b) pie chart, (c) scatter chart, (d) bar chart, (e) all of the above except the pie chart. #3. In EXCEL, what is the Solver used for? (a) Finding errors in a formula, (b) minimizing or maximizing a cell by varying other cells, (c) doing error analysis, (d) solving algebraic equations, (e) solving differential equations. #4. When you make a plot in EXCEL, your chart should always have (a) a title, (b) x -axis label, (c) y-axis label, (d) units on the x and y axes, (e) all of the above. 17


Buy Material

Are you sure you want to buy this material for

25 Karma

Buy Material

BOOM! Enjoy Your Free Notes!

We've added these Notes to your profile, click here to view them now.


You're already Subscribed!

Looks like you've already subscribed to StudySoup, you won't need to purchase another subscription to get this material. To access this material simply click 'View Full Document'

Why people love StudySoup

Jim McGreen Ohio University

"Knowing I can count on the Elite Notetaker in my class allows me to focus on what the professor is saying instead of just scribbling notes the whole time and falling behind."

Kyle Maynard Purdue

"When you're taking detailed notes and trying to help everyone else out in the class, it really helps you learn and understand the I made $280 on my first study guide!"

Jim McGreen Ohio University

"Knowing I can count on the Elite Notetaker in my class allows me to focus on what the professor is saying instead of just scribbling notes the whole time and falling behind."

Parker Thompson 500 Startups

"It's a great way for students to improve their educational experience and it seemed like a product that everybody wants, so all the people participating are winning."

Become an Elite Notetaker and start selling your notes online!

Refund Policy


All subscriptions to StudySoup are paid in full at the time of subscribing. To change your credit card information or to cancel your subscription, go to "Edit Settings". All credit card information will be available there. If you should decide to cancel your subscription, it will continue to be valid until the next payment period, as all payments for the current period were made in advance. For special circumstances, please email


StudySoup has more than 1 million course-specific study resources to help students study smarter. If you’re having trouble finding what you’re looking for, our customer support team can help you find what you need! Feel free to contact them here:

Recurring Subscriptions: If you have canceled your recurring subscription on the day of renewal and have not downloaded any documents, you may request a refund by submitting an email to

Satisfaction Guarantee: If you’re not satisfied with your subscription, you can contact us for further help. Contact must be made within 3 business days of your subscription purchase and your refund request will be subject for review.

Please Note: Refunds can never be provided more than 30 days after the initial purchase date regardless of your activity on the site.