Introduction
Spreadsheets have a wide variety of uses in both the business and academic worlds. The study of
physics is no exception to this, and spreadsheets are a useful tool in the laboratory for both modeling
and computation. This lab will introduce you to the spreadsheet program Microsoft Excel and will
provide you with an opportunity to use what has been learned.
Equipment/Supplies
1. Computer equipped with Microsoft Excel software
Section 1
This section contains basic information and terminology regarding the use of spreadsheets. It is
intended for those students who have no previous experience working with spreadsheets. For those
students with a working knowledge of spreadsheets, this section can serve as a review.
Basic terminology
1. A column is a complete vertical set of cells. It is designated by a letter .
2. A row is a complete horizontal set of cells. It is designated by a number.
3. A cell is one item in the spreadsheet. It may contain text, a number, or a function. It is identified by a
letter/number pair (e.g., A2 or DD38), where the letter is the column and the number indicates
the row.
4. A range is a set of cells that may be horizontal, vertical or rectangular. It is designated by the first
and last cells in it, separated by a colon (:).
5. A function is literally a mathematical function. It may contain numbers, operators, basic functions,
and references. Note: a function must always start with an equals sign.
6. An operator is one of the familiar mathematical operators: +, –, *, /, and ^.
7. A basic function is also from math, examples are sum(), exp(), sin(), atan(), and sqrt().
8. A reference is the value in another cell, named by its column and row as a pair, e.g., A1 or D3.
References will often serve as the “variable” in our functions.
9. Syntax for special functions:
Function Excel notation Syntax
p PI() p/2 => PI()/2
exponential EXP(x) ex = EXP(x)
Scientific notation 3E-3 3×10-3 => 3E-3
Numbers or expressions as text Enclose in quotes “sin(PI()/2)+6”
Physics 251 Laboratory Spreadsheets Fall 2002
In the figure above several cells are shown as examples. Cell A3 has text typed into it. Cells C3 and E3
have constants (numbers). Cells D3 and F3 have functions. The text in cells C4:F4 shows how the
functions and constants in row 3 were typed (without the quotation marks, though). There are entries
in range C3:F3. There are no entries in B1:B4. The entire area shown is A1:F10. Note that for
rectangular ranges the first and last cells are the upper left and lower right respectively.
To enter information in a cell just click in it and start typing. What you type will appear in the cell and
on a line near the top of the window (Edit Bar). If you need to edit what you have typed, work in the
Edit Bar. Use the mouse move the cursor around in the Edit Bar, the arrow keys will not work, as
these will move you to a new cell. If you type a function, the function will be evaluated when you hit
return (or arrow to a new cell).
You can highlight a cell with a function in it, click and hold the little box in the lower right
corner and drag. This will “fill” a whole range with the equation you want, furthermore, the
spreadsheet will automatically increment the row # or column letter as it goes.
The example above shows the usefulness of automatically incrementing rows. In cells A2 & A3, I typed
the numbers 1&2. Highlighting both cells and dragging the corner down I get a list of counting
numbers. In cells B2 and B3, I typed the dates for two consecutive Wednesdays. Again, highlighting
both and pulling down I get a column with the dates for the next 5 Wednesdays. Incrementing can be
done with formulas as well. In cell C2, I typed in the equation =A2^2+3, then “pulled down” to
calculate the equation for all the numbers in column A. Double clicking on a cell shows the function.
Physics 251 Laboratory Spreadsheets Fall 2002
Note that when I pulled down, the spreadsheet automatically incremented the index from A2 to A6.
Sometimes we do not want the cells to increment automatically, to prevent the spreadsheet from
incrementing use a ($) before the column letter to stop columns from incrementing or before the number
to prevent the rows from incrementing. If you are using a cell to store a constant, put ($) before both
the column letter and the row number.
Using only the ideas mentioned so far, we can already get a glimpse of how to do physics with a
spreadsheet. In the following example, it is illustrated how to calculate a basic 152 problem: the height
of a ball that is thrown upwards with initial velocity v0. We remember the kinematic formulas for the x
and y motion of an object subjected to gravitational acceleration as 2
0 0
1
2 y = y + v yt + ayt and
0 0x x = x + v t .
To plot the function, highlight the numbers in columns B and C, click on the chart icon on the toolbar at
the top, select XY Scatter plot and click OK. The right column will be used as the independent variable
(x) axis and the column on the left will be used as the dependent variable (y) axis. Adding a trendline
and displaying the equation on the graph are options in the chart menu.
Physics 251 Laboratory Spreadsheets Fall 2002
Height of Projectile
-10
0
10
20
30
40
50
60
0 2 4 6 8
Time (sec)
Height (meters)
Height of Projectile
y = -4.9×2 + 30x + 10
-10
0
10
20
30
40
50
60
0 2 4 6 8
Time (sec)
Height (meters)
Physics 251 Laboratory Spreadsheets Fall 2002
Section 2
In this section, you will apply the information in section 1 to create several spreadsheets.
1. Create a spreadsheet that reproduces the ball thrown upwards as in the example in section 1 but on
this spreadsheet add x motion to it. Remember to add constants for initial x velocity and position.
Make plots of x vs. t, y vs. t and x vs. y
2. Make a spreadsheet that produces a “wiremesh” plot of the potential of a dipole. Remember the
equation for the potential of a dipole is
0 1 2
1
4
q q
V
pe r r
æ – ö
= +ç÷
è ø
.
Steps:
1. Keep everything in the region of the x-y plane bounded by x = [-2,2] y = [-2,2].
2. Offset your grid so the charges don’t sit precisely on grid points. Set your first point at
(2.001,2.001).
3. Set up constants, k=9e9, q1, q2, x1, x2, y1, y2 (the charges and locations of your two charges)
and delta (the step size in x and y – should be between 0.1 and 0.3).
4. Create a horizontal range representing x values.
5. Create a vertical range representing the y values.
6. Create a formula that will calculate the potential due to q1 at the origin. Fill your x-y region and
check the results. Check to be sure the automatic increment feature is working the way you
want it to.
7. Modify your formula to move q1 to a value on the +x axis, recheck. Does the graph make
sense?
8. Now, modify to move q1 to a point with positive x and y, recheck. Does the graph make sense?
9. Add in the other charge.
10. Excel has a “surface plot” feature in the chart wizard.
11. Plot and print wiremesh plots of your charges in quadrants I &III and I &II.
The following is an example of a wire mesh plot with charges at (0.5,0.5) and (-0.5,-0.5).
Physics 251 Laboratory Spreadsheets Fall 2002
1
5
9
13
17
21
S1
-8000.00 S12
-6000.00
-4000.00
-2000.00
0.00
2000.00
4000.00
6000.00
8000.00
Physics 251 Laboratory Spreadsheets Fall 2002
Spreadsheets Lab
Results
Section 2
Print out the following plots from the first exercise:
x vs. t
y vs. t
x vs. y
Print out the following plots from the second exercise:
A wiremesh plot of the potential versus position for a dipole with charges at (0.5,0.5) & (-0.5,-0.5)
A wiremesh plot of the potential versus position for a dipole with charges at (1,1) & (-1,-1).
Explain to “The Boss” why programs like Excel are useful in understanding or solving physics
problems. ______________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
What was good about this lab and what would you do to improve it?
_____________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
NAME_________________________________________________DATE__________________
Physics 251 Laboratory Spreadsheets Fall 2002
0 comments