PURPOSE
To gain an introduction to the use of the spreadsheet to perform data manipulations, data analysis, plotting, and curve fitting.
DISCUSSION
As mentioned in the INTRODUCTION, the Worksheet associated with each experiment that you do is an Excel® spreadsheet document. If you have never worked with a spreadsheet before, the material below will provide you with a brief introduction. One of the goals of the Physics Laboratory is to teach you some facility with using a modern computer spreadsheet. This skill is one that will be important to you no matter what your future career aspirations are. Open the Worksheet, fill in the header information, and follow the tutorial below on the spreadsheet as you go along.
The Cell: The basic unit in the spreadsheet is the cell. You can think of the spreadsheet as a huge grid of cells. Each cell is located by its row and its column designation. The columns are labeled with letters, starting with A,B,..., etc. The rows are labeled with numbers, starting with 1,2,..., etc. Thus, the cell that is 5 columns over and 9 rows down is called cell E9. The usual input to a cell is a number that is typed in from the keyboard.
Calculations: The major utility of a spreadsheet is its ability to perform "live" calculations that involve data that has been entered in certain cells and providing the results in another cell. The term "live" refers to fact that the result will change according to the appropriate calculation if the input data is altered. For example, suppose we enter the number 2 in cell A10, and the number 5 in cell B10. You can select a specific cell with the mouse.
Do the following calculations: place the sum of the two numbers in cell C10; the difference in cell D10; the product in cell E10; and the quotient in cell F10. To accomplish these calculations, go to the indicated cell by selecting it with the mouse, and type in the following in the appropriate cells:
in cell C10 type "= A10 + B10"
in D10 type "= A10 - B10"
in E10 type "= A10 * B10"
in F10 type "= A10 / B10"
The "equals sign" tells the spreadsheet that you want it to perform the computation that follows it. Try these exercises and check to see if the anticipated results appear in the answer cells. Also notice that what you are typing on the keyboard appears in the contents display line located immediately above the spreadsheet.
To see why the spreadsheet saves a lot of work, change the entry in cell A10 to 3 and see what happens in C10:F10.
Cut, Copy, and Paste: These operations allow you to Cut an entry in one cell and put it on the Clipboard, which is a temporary storage place, to Copy an entry onto the Clipboard, and then to Paste the Clipboard contents into another cell. The Copy operation copies not only the numerical contents of the original cell, but also any indicated calculations or formulas. This feature allows one to perform repetitive calculations quite easily. In Excel, Cut, Copy, and Paste are found under the Edit menu or on the Home toolbar above Clipboard (depending on your version of Excel).
For example, suppose we want to reproduce the calculations done above in
cells A10:F10 into cells A11:F11.
To do this, simply select all of
the original cells, namely A10:F10, choose Copy,
then move down to select cells A11:F11, and choose Paste.
Pick any two numbers to play with
and enter them in cells A11 and B11. Notice how the calculations
are performed in cells C11 through F11.
To check on what is going on, select any of these cells, say D11, and look at the contents line just above the spreadsheet. You should see the indicated calculation for subtraction, namely "= A11 - B11", in the formula bar near the top of the spreadsheet. (You might have to turn on the formula bar under the View menu.)
When the cells were pasted, the
cell reference was kept relative to the source cells; that is, the
references in D11 are from A11 and B11 , not A10
and B10, as they were in the line that was copied.
If you do not want this kind of relative referencing to happen, there is a
way to specify a fixed cell location in a calculation.
For instance, suppose the spreadsheet
numbers need to be multiplied by the same number over and over again. Assume
that this number is entered in cell G10. Then, if the number in
cells A10 and A11 are to be multiplied by this same number,
the reference to cell G10 is made absolute by using dollar
signs, as in $G$10.
Enter 10 in cell G10. If you want to multiply the contents of
cells A10 and A11 by 10 and put the result in cells
G11 and G12, respectively,
you simply select cell G11 and enter "=
A10 * $G$10". Then Copy cell G11 and Paste
it into cell G12. The absolute reference is kept during the copy
and paste operation.
Significant Figures: On your data Spreadsheet, always use the correct number of significant figures. If the Spreadsheet will not let you write the correct number of decimal places in a particular cell, the Number Format needs to be changed. Select the cell where the result is entered, and go to the Format…Cells menu. On the Number tab, choose Number and enter the requisite number of decimal places in the indicator box.
Functions: Much more sophisticated operations than simple arithmetic can be performed by using built-in function commands. As a simple example, we know that taking the average of a collection of numbers is accomplished by adding them all up and then dividing by the number of entries. Excel has a built-in function that will automatically calculate the average of a group of numbers. Suppose we have the exam grades of 50 students entered in cells A12:A61 on the spreadsheet. The spreadsheet function can quickly find the average.
To see how this works, you first
need some data. To make up the imaginary grades, use the function that
generates random numbers, called RAND. Select cell A12 and
type "= RAND( ) * 100".
Note that the equals sign tells
the spreadsheet that we're doing a calculation; the function itself is
represented by RAND( ).
In this case, nothing is placed within the parentheses, but they still
must be present.
This formula will generate a number between 0 and 100, just like the scores on an exam. When you hit return, you should see such a number in cell A12. (It may, however, have way too many figures beyond the decimal point. If so, change the display to two figures beyond the decimal point using the Format menu, choosing Cells and then the Number tab, or using the buttons above Number on the Home toolbar.)
Next, we can generate a complete set of 50 grades by selecting cells A12 through A61 with the mouse. This column of cells should appear highlighted. Then choose Fill → Down under the Edit menu or above Editing on the Home toolbar. You should see an array of 50 random scores.
The average can be evaluated in cell B12 by typing the following
in that cell: "= AVERAGE(A12:A61)".
Note that the equals sign tells the spreadsheet that we're doing a
calculation; the function itself is represented by "AVERAGE
Excel contains a huge number of functions that can be used to make complicated calculations simple. You will encounter a few of these in some of the future worksheets. To see a complete list of them, hit the function button on the formula bar and look over all the possibilities.
Charts: Whenever an analysis requires a plot of the data or of some other results to be made, Excel has a simple routine for making graphs. The data to be plotted must be selected on the spreadsheet. The Analysis section of an experiment will direct you through these selections when required, however for now perform the following steps to produce a plot of some data and a best straight line fit to it.
Make up a series of data, say, the numbers 1 through 10 in cells C12:C21. This can be accomplished quickly by typing 1 in cell C12, and then selecting cells C12:C21 with the mouse. Under the Edit menu or under Editing on the Home toolbar, choose Fill → Series … and then click OK in the dialog box.
In cells D12:D21, some functional dependence is needed, so for simplicity‘s sake, pick a straight line as follows. In cell D12, enter "= 5 + C12 * 2". This formula takes the contents of cell C12, multiplies it by 2, and then adds 5. The result will be a linear function with a slope of 2 and an intercept of 5.
Select cells D12:D21 with the mouse; they should remain highlighted. Using Fill again, choose Fill → Down.
To make a plot of these data, use the mouse to select the entire data set from cells C12:D21. Go to the Insert menu. Either select Chart... or find the Chart section of the toolbar (again depending on your version of Excel). Choose the X-Y Scatter button: or . Then from the submenu, select the first icon: The chart should appear.
Now resize and move the chart so it lies below cell C22 and not extending beyond column G. To do this, click on the chart itself. Put the mouse at a random place on the border of the plot and drag it to move the chart. Put the mouse on the border in a corner or halfway along any side to resize the chart.
Next, add a trendline, i.e. a best straight line fit to the data, along with
the equation for that line.
Click on any one of the data points.
Then, depending on your version of Excel,
(i) Go to the Chart menu in the main menu bar and select
Add Trendline.... Under the Options tab in the pop-up
window, check the box Display equation on chart; or
(ii) right click the mouse to display the and select Add Trendline...
from the menu. Check the box Display equation on chart near the bottom
of the pop-up window.
When you have completed all of the steps
above, a print a copy of the Worksheet
and turn it in at the beginning of your first lab.