Spreadsheet Labs
Lab 1 - Introduction to Spreadsheets
Lab 2 - Creating graphs with a Spreadsheet
Lab 3 - Position-time and Velocity-time graphs on
a Spreadsheet
Lab 4 - Projectile Trajectories with Complementary
Angles
Lab 5 - Earth's Gravitational Well
Lab 6 - Earth to Moon Gravitational Wells
Lab 7 - One and Two Charge Potential Graphs
Return to Physics Home Page
_______________________________________________________________________________________________________
Physics Lab 1 - Introduction to Spreadsheets
Objectives: To gain proficiency in creating, saving, and
modifying a spreadsheet.
To create a spreadsheet to keep track of your physics grade.
Materials: personal blank 3.5” floppy disk for storage, computer with Microsoft Excel® spreadsheet program installed.
Procedure:
These exercises are intended to get you started on spreadsheets. We will be using the Microsoft Excel® 97 spreadsheet, but if you have a different spreadsheet at home, most are very similar to one another and so you should be able to figure out how to use a different one once you know one of them.
Basic uses of a spreadsheet:
A spreadsheet is designed primarily to take large amounts of
data and easily manipulate it in a variety of ways: evaluating formulas,
creating tables, charts, or graphs as you wish. It helps keep track
of information, performs many mathematical functions on data, and can be
used for mathematical modeling. Some examples are calculating grades,
keeping attendance, doing sports statistics, organizing candy sales or
other business functions, manipulating or analyzing physics data!, etc.
Complete the following exercises to become familiar with a variety of capabilities of a spreadsheet. Following the exercises, you will then create a spreadsheet to keep track of your physics grades. Save all of your files on your 3.5” disk (its a good idea to save every once in a while in case something goes wrong) and turn in a hard copy of your spreadsheet exercises when completed (to help you learn how to print).
Exercises
a. Open the Microsoft Excel® 97 program. Note that each rectangle
can be referred to by a letter-number combination.
b. Type “spreadsheet exercises” in d4. Press return.
(oops, I meant d1. Cut and paste to move the words to
d1.)
c. Make the spreadsheet count. In a3, type “Counting numbers”. Press return.
d. In a4, type 1. Return.
e. In a5, type =a4+1. The “=” sign tells the program that this is a formula to evaluate. Return. (you should now see “2” in a5)
f. Highlight from a5 to a20.
g. Select Fill Down from Edit menu. (You should now have a bunch of counting numbers.)
h. Make the spreadsheet count odd numbers only. In b3, type “Odd numbers”
i. In b4, type 1.
j. You figure out the rest studying e – g from above.
k. Make the spreadsheet add. In c3, type “a + b”. (Note that without the “=” sign, the program treats this as text instead of a formula).
l. In c4, type =a4+b4. Return. (“2” should appear in c4).
m. Highlight c4 – c20.
n. Select Fill Down from Edit menu. (You should see the sums appear).
o. Make the spreadsheet multiply. In d3, type “a x b”.
p. You figure out the rest.
q. While we have just used a few simple math functions, the spreadsheet will do almost any function with numbers you can imagine. For example, square roots, logarithms, if-then statements, business functions for interest, etc. Select the fx button and scroll through some of the many possibilities.
r. Make the spreadsheet average a column. First, lets insert 2 empty columns under A and B to open up some room. Click and drag on A and B to select both of the entire columns. Select Cells from the Insert menu.
s. In a22, type “column avg.”
t. In c22, type = average (c4:c20). Return. (The average value for the column should appear. The colon tells the program to include everything between c4 and c20 in the calculation. You could also average just a few different locations by listing them separated by a comma inside of the parentheses).
u. Fill Right under Edit menu to average columns d, e, and f. (Click on d22, e22, and f22 to see how the program automatically changes the (c4:c20) to (d4:d20) for the d column, etc. When filling or pasting, the spreadsheet will change arguments automatically to match the relative locations of the original version. This will become more clear as you gain familiarity with the program.)
v. Make the spreadsheet do a sum of a column. Type “column sum” in a24.
w. Look under fx button for correct function. Figure it out.
x. Make the spreadsheet do grades. In e25, type “grades”. In a27, type “student names”. Return.
y. Beginning in a28, type five imaginary student names, last name first. Don’t do it in alphabetical order.
z. Now alphabetize your list. Highlight the names. Press the A to Z button.
aa. Woops, just added a new student, John Banks. Type him in last and then realphabetize.
bb. In c27, type “hw 1”. In d27, type “hw 2”. In e27, type “hw 3”. In f27, type “test 1”. In g27, type “test 2”. In h27, type “test 3”.
cc. Now fill in some random grades based on 100 points each.
dd. In j27, type “hw avg.”
ee. In j28, type the correct formula. (Remember that formulas begin with =).
ff. Highlight from j28 down. Now select Fill Down from Edit menu.
gg. In k27, type “test avg.”
hh. In k28, type the correct formula.
ii. Highlight from k28 down. Now Fill Down.
jj. In l27, type “final avg.”
kk. We want hw to count 50% and test to count 50%. Figure out the correct formula and complete the table for final average.
ll. Woops! Someone did a test correction. Change their test grade and see what happens to the final average.
mm. Woops! You have a new student. Add her name to the end of the list. Realphabetize and see what happens (make sure the grades travel with the students by highlighting names and grades in one big block). #DIV/0! is an error code. What do you think it means? How will it be resolved?
nn. Redo the formula to make the hw count 25% and the tests count 75%.
oo. Save this spreadsheet on your disk as “spreadsheet exercises”
pp. Print out a hard copy of your spreadsheet exercises to turn in. (you made need to change the orientation to “Landscape” under the File menu – page setup. If you want the lines of the cells to print, go to File menu – page setup – and mark “Gridlines”).
Assignment
Create a spreadsheet to keep track of your physics grade. Save
this spreadsheet on your disk, and you will update it occasionally as you
earn more grades in the class.
Return to Physics Home Page
______________________________________________________________________________________________________
Physics Lab 2 - Creating graphs with a Spreadsheet
Objectives: To gain proficiency in creating a variety of graphs from tabular data and analyzing the meaning contained within those graphs.
Materials: personal blank 3.5” floppy disk for storage, computer with spreadsheet program installed, data from the bulldozer lab performed in class
Procedure:
a. Create a new spreadsheet.
b. Type in a1 “Sample Problem data”
c. Type in a3 “time(s)”
d. Type in b3 “position (m)”
e. Enter the time and position data in columns A and B starting at a4 and b4. Use the data below for this sample problem.
time (s) position (m)
0
0
2.1
4.3
3.7
8.5
5.8
15.1
8.1
19.1
10.6
22
12.4
28.4
13.9
30.5
16
38.8
17.8
41.8
20.6
47.1
f. Pull down the File menu and choose “Save As”. Give the saved file the name “Sample graphing problems”
g. Click on the Chart Wizard icon (the bar graph looking button on the top row).
h. Choose Chart type XY(Scatter), then choose the sub-type that plots just the data points (look at the available pictures to figure out which one).
i. Click Next.
j. Click Series tab. Notice that the X Values are found in the column from a4 to a14 ($A$4:$A$14) which is correct because we want the time on the x-axis. Note that the Y Values are found in the column from b4 to b14 (these are the position data). For practice, highlight the X Values box, and then click and drag from b4 to b14 on your spreadsheet. Notice that now the X Values have been changed to be found in b4 to b14 instead of the a4 to a14 from before. Since this is wrong (we want the time on the x-axis), highlight the X Values box and click and drag on a4 to a14 to put that back in (as it was to start with).
k. Click Next.
l. On the Titles tab, give the graph an appropriate title (like “Sample Problem 1”), and also give the (X)axis and (Y)axis appropriate labels like “time(s)” and “position (m)”.
m. Click to the tab Gridlines. To add x-axis gridlines, check the box in front of Major Gridlines. You can also check Minor Gridlines as well to see what they will do.
n. Click on the Legend tab. Try some of the different circles to see what they will do. Choose the one you like best.
o. Click on Data Labels tab. Try these out to see which you like best. Then click Next.
p. Place the chart as an object in Sheet 1. This will put the graph together with the data table which can be useful sometimes. Then click Finish.
q. You’ve made your first graph on the spreadsheet! Use your mouse to click and drag on the small black squares of the selected graph to resize the graph to your liking. If you place the pointer somewhere in the white space in the graph box, you can move the whole graph’s position on the page. Resize and relocate the graph to your convenience now.
r. Save this now by pulling down File menu and “Save” (note that “Save As” is not needed since you have already saved this file with a name).
s. By double-clicking on various parts of the graph, you have some options to change things. Double-click on “time(s)” label. Try out some of the options to see what they do. Do the same for “position (m)” and the title, as well as for the x-axis numbers, y-axis numbers, the data points, the y-axis gridlines, the x-axis gridlines, the white space in the box but outside the graph (chart area).
t. If after experimenting with some of these options to see what they do messes up your graph and you can’t return it back to the way you want it, remember that you’ve saved it and so can always retrieve the saved copy (do not “Save” a messed up copy because that will overwrite the previous good copy you saved).
u. With the whole chart selected (you should see little black squares on each corner and in the middle of each side of the chart box), you can also click on the Chart Wizard button again and have the same options screens as before if you want to change something.
v. It would be a good idea to double-click on “Plot Area” and then in the “Area” box change it to none. This will make the plot area gray background disappear and it won’t be so dark when you print out a hardcopy of your graph.
w. Remember to “Save” (under the File menu) once you’ve got it the way you like.
x. Note that the data looks pretty linear (not perfect, but then data isn’t always “perfect”). When we expect the data to be linear (or if it looks linear even if we didn’t expect it to be), it is usually desirable to find the best fit line to the data. To do this, first select the whole chart and go to the Chart menu on the top line. Select Add Trendline...
y. Choose the Linear fit (the polynomial fit would be useful if it was approximately parabolic – then the order is 2 because of the “x-squared” or “t-squared” term). Click the Options tab and check the box in front of “Display equation on chart” so that we can see the equation of the best fit line.
z. From your knowledge of physics, what pieces of information can you get from this equation?
aa. Now we want to create a velocity-time graph from this data. Type in c3 “velocity (m/s)”. Note that you may need to move your chart over to see column 3 if you placed the chart partly on top of column 3.
bb. How is velocity calculated from this position-time data? From your knowledge of physics, derive the formula. When you think you have it, type in c4 the formula for the first velocity value (remember to begin a formula with “=”). Does your result from the formula seem reasonable? If not, check with me about your formula before proceeding.
cc. Highlight from c4 on down, then under Edit menu Fill Down.
dd. Even though the last velocity in c14 seems reasonable and is actually OK for this example, there is a problem that in some situations would give very incorrect values for c14. Describe the problem and in what situations it would be far off the mark. Check with me when you think you have it. For good technique, do not calculate a velocity in c14 for this problem – delete the value showing so that the velocity data stops at c13.
ee. Make a graph of velocity vs. time by following the same procedure as you did for the position-time graph (starting at step g). Note that in Chart Wizard – Step 2 of 4 under the Series tab, you have the choice of removing the position graph (use Remove button when “position (m)” is highlighted) or having both position and velocity on one graph. Because the units for these two would be different on the vertical axis, remove the position data to create a separate graph for velocity. Don’t forget that the velocity series of data stops at c13 – check the selected data values carefully and modify if needed.
ff. Decide on the most appropriate trendline to use based on your knowledge of physics. Explain the meaning of the curve’s equation once you have it on the velocity graph.
gg. Save the file. (just a reminder – you should probably be saving more frequently than I am telling you. A good habit is to always save immediately after completing a major portion of a task, such as completing a graph).
hh. You are now almost ready to print, but first make sure both graphs fit on one sheet of paper using File menu Print Preview. If they don’t you can (a) resize the chart boxes to better make them fit; (b) use File menu Page Setup to change the paper orientation or use the Margins tab to adjust the page margins; (c) use a combination of both of the above.
ii. When Print Preview shows both on one page, Save the file, and then print it.
jj. Turn in for your completed lab the printout of both graphs along with the data on the same page, and your written answers to questions in parts z, bb, dd, and ff.
Assignment
Create a position-time and velocity-time graph from the bulldozer data
collected in class. Write a short description explaining your interpretation
of the graph as it relates to the bulldozer's actual movement. Include
any discussion about possible sources of error in this lab activity.
Return to Physics Home Page
______________________________________________________________________________________________________
Physics Lab 3 - Position-time and Velocity-time graphs on a Spreadsheet
Objectives: To create position-time and velocity-time graphs from a variety of recorded data and to analyze the meaning contained within those graphs.
Materials: Graph A: stopwatches, trundle wheel. Graph B: dynamics cart, two pieces of 4 foot board for a ramp, ticker-tape timer with tape, 5 textbooks to support the ramp. Graph C: Two 8 foot pieces of aluminum U-channel, steel ball, various washers and wooden blocks to use to shim up the U-channel as needed, stopwatches, masking tape.
Procedure:
A. Graph of a person running
1. Using the trundle wheel, mark off regular displacements from
a chosen reference point, and place a person with a stopwatch at each of
the marked locations. One person will begin running before the chosen
reference point, and when (s)he arrives at that reference point, (s)he
will yell “GO!” for all of the people with stopwatches to begin timing.
With the runner continuing to run with as nearly constant velocity as possible,
each person at a marked location will stop their stopwatch when the runner
passes their marked point. The runner will continue to run at constant
velocity until (s)he is past the last marked point. Record the position-time
data for the runner in a table.
2. Make a graph of the position-time data for the runner using a spreadsheet. Make sure to create a good graph: labels on both axes, appropriate scales marked on both axes, a title given to the graph, and the slope calculated and interpreted if the graph appears to be linear. Create a velocity-time graph with the time data points lining up exactly with those of the position-time graph.
3. a) label your position-time graph on the printed hardcopy as “Runner
A” on the graph’s line.
b) draw a new line (in pencil on the hardcopy printed
out from the spreadsheet graph) on the graph of what it would look like
if a second person were running at exactly the same speed as Runner A except
that they had a 10m head start on Runner A. Label this new line “Runner
1”.
c) draw another new line (in pencil on the hardcopy
printed out from the spreadsheet graph) on the graph of what it would look
like if a third person were running faster than Runner A and this new runner
hit the “0” position at exactly the same time as Runner A. Label
this line “Runner 2”.
d)draw a third new line (in pencil on the hardcopy
printed out from the spreadsheet graph) on the graph of what it would look
like if a fourth person were running at exactly the same speed as Runner
A at time “0” with a 5m head start, but then this runner got tired about
halfway through the run and slowed down more and more throughout the second
half of the run. Label this line “Runner 3”.
4. Now add the lines labeled “Runner 1”, “Runner 2”, “Runner 3” (in
pencil on the hardcopy printed out from the spreadsheet graph) onto the
velocity-time graph below the position-time one.
B. Graphing using the ticker-tape timer
1. Set up the two boards side by side as a ramp with the elevated
end 4 textbooks high. Place the fifth textbook on top of the other
4 and place the ticker-tape timer on top of the fifth textbook; this is
to elevate the tape coming through the timer to be nearly level with the
bottom of the dynamics car.
Measure about 3m of tape to be pulled through the time. Let the cart roll as you turn on the timer. Since the timer strikes 60 times per second, mark every 6 dots on the tape – this corresponds to a time of 0.1s between marks. Measure the distance between the beginning mark and subsequent marks and write the values on the tape.
2. Use the data on the tape to make a position-time graph for the cart using your spreadsheet program.
3. Identify on your graph (mark in pencil on the hardcopy) the
times when the cart seems to be moving at nearly constant velocity.
How can you tell from the graph? At times when the velocity is clearly
not uniform, tell if the graph shows the cart speeding up or slowing down.
How can you tell from the graph?
C. Ball rolling on U-channel
1. Set up the U-channels to be as nearly constant slope as possible
for the ball rolling on it. Use the washers and/or wooden blocks
available to shim the U-channel as needed. Mark distances on the
U-channel using masking tape.
2. Roll the ball from the top of the U-channels and take time data with the stopwatches as it passes your marked locations. Record this data in a table.
3.Use the data to create a position-time graph on a spreadsheet..
4. Create a velocity-time graph, lining up the time positions exactly under the ones for the position graph. If your position-time data does not show uniform motion, then the velocity-time graph should reflect this. Show in your report how you calculated the velocities.
Return to Physics Home Page
____________________________________________________________________________________
Physics Lab 4 - Projectile Trajectories with Complementary Angles
Objectives: Use projectile motion equations to program a spreadsheet to plot the trajectory of a projectile when launched at complementary angles. Draw conclusions from these plots.
Materials: Spreadsheet
Procedure:
1. First an overview.
a) You will be generating plots of dy vs. dx for the trajectory of
the projectile, and so you want to eventually express dy as a function
of dx and the input variables vi (the launch velocity) and theta (the launch
angle).
b) You will need to choose a launch velocity vi and keep that the same
throughout the program. The shapes of the graphs will not depend
on this value, although the limits of dy and dx that will be graphed will.
c) The value of theta will be plotted in pairs of complementary
angles on one graph, starting with 5° and ending with 85° incremented
in 5° steps.
2. Now it is time to do some physics. Draw a sketch of the projectile motion on a sheet of paper, labeling dy , dx , vi and theta . Write an expression for dx in terms of vi, theta, and the time t. Then write an expression for dy in terms of the same variables.
3. Next eliminate t from the expression for dy by solving the dx equation for t and substituting.
4. You should now have an expression for dy in terms of only dx, theta, and vi. This is the equation you will program into the spreadsheet to calculate dy as a function of dx.
5. Now choose a value for vi that you will use throughout this lab. Include your chosen value in the future titles of your graphs so that someone else can replicate your work if they so desire (example: graph titled “Projectile Motion at Angles of 5 and 85 with Launch Velocity of 25 m/s” would be descriptive if you chose vi to be 25 m/s).
6. Using your chosen vi, calculate by hand the maximum value of dx. Remember that this maximum value occurs at launch angle 45 . This will give you the maximum limit to program the spreadsheet for the independent variable dx.
7. You are now ready to program the spreadsheet. Create a new spreadsheet and give it an appropriate title in row 1 so that others know its intended function (including your chosen value of launch velocity). Then create two labeled cells a row or two down where you will input the values of the two complementary angles for this plot. By creating these two values as inputs into two different cells and then referring to these cells whenever you need the angle, you can easily update the spreadsheet to accommodate the next pair of complementary angles by simply changing these two cells instead of a plethora of cells. Trust me, this will make your life easier for this lab even if it seems a little complicated now.
8. Now you need to create a column of the values for dx. I suggest that you start with 0m and increment by 1m up to the maximum value you calculated in step 6 above.
9. You now will need to program two columns (dy1 and dy2), one for each of the pair of complementary angles, to calculate the corresponding dy for each value of dx. Remember to refer to the appropriate input cell for the angle when needed. One caution to be aware of: the trigonometric functions (sine, cosine, tangent, etc.) in many spreadsheets are expecting the angle argument to be expressed in radians instead of degrees. If so, you will have to have the program convert your input angle in degrees into radians before being used as the argument of a trig function.
10. Once you have the three columns (dx, dy1, and dy2) programmed and calculated, then you are ready to graph. Choose an x-y scatter graph with the dx as the independent variable and two series as the dependent variables in order to get two graphs on one set of axes.
11. Choose vertical and horizontal scales to accommodate all the trajectories you will be plotting – this will make for uniform axes, which will make it easier to compare one trajectory to another. Adjust the size and other features to create the best graph for displaying your data. Note that to avoid negative values of dy being plotted (the projectile doesn’t go underground), you can set the minimum vertical value to be zero.
12. Once you have the first pair of complementary angles plotted, then you want to save the spreadsheet program under a new name and modify the two angles to be used in the reference cells to get the next pair of complementary angles. If done right, everything should update automatically and you simply have to retitle the graph to reflect the new angles. Do this for each pair of complementary angles.
13. OPTIONAL. If you wish, you might want to plot all angles on one graph.
14. List conclusions that can be drawn from these graphs and your justifications for them.
Return to Physics Home Page
____________________________________________________________________________________
Physics Lab 5 - Earth's Gravitational Well
Objectives: Use Newton’s Law of Universal Gravitation to program a spreadsheet to draw the gravitational well experienced by objects on Earth.
Materials: Spreadsheet
Procedure:
Part I. First, some conceptualizing is necessary.
1. Write Newton’s Law of Universal Gravitation to
the right.
2. You will need some constants to use in the programming
of this equation. Assume a spaceship of 1000 kg for this gravitational
well. The shape of the well would be identical for any other object;
just the magnitudes would vary depending on the mass of the object.
Find and fill in the other constants you will need in the spaces provided
(include units).
mass of earth
= _____________
G = ________________
radius of earth = __________
3. The purpose of this drawing is to show how the force of gravity varies (in 2 dimensions) as the spaceship moves away from the surface of the Earth, and so you need a way to “measure” distances on your spreadsheet which become a part of the equation in #1. This will be accomplished by establishing a numbered grid in the first row and the first column of the spreadsheet. In the first row starting at A, number from 1 to 25 (which takes you to column Y) and in the first column, number down from 1 (already done when the first row was done) down to row 25. See below for a diagram illustrating this.
A B C .......
L Y
1 1 2 3 .....
12 25
2 2
3 3
12
Cell L12 will be important!
25
4. Visualize the above grid as a spatial overlay above the Earth (top
view looking “down”), with the Earth located at cell L12. As the
spaceship moves away from L12, it experiences less and less of the Earth’s
gravitational pull according to Newton’s Law of Universal Gravitation.
In order to calculate the amount of gravitational pull, you need to program
the formula for the force the spaceship experiences in cell B2 (the first
empty cell in the upper left) and copy that formula throughout the grid.
However, the formula needs to automatically update itself as to its distance
from the Earth at L12, and it will take a little practice to get the formula
right. Let’s first just practice writing distance formulas from cell
L12. If you haven’t done so already, now is the time to get your
spreadsheet up and running to try out some of these ideas.
Part II. Working with these ideas in a spreadsheet.
5. In order to get the distance from cell B2 to cell L12 (the
Earth), it is necessary to use the Pythagorean theorem. Since in
the formula for Newton’s Law of Universal Gravitation the distance d shows
up squared, there is no need to take the square root to get d and then
square it again. Calculate d^2 directly from the Pythagorean theorem
(see diagram in #3) by working through the ideas below.
6. For working with this grid, you may need to readjust the width of the cells to be convenient – I suggest a width of 4 to be small enough to see most of the grid but large enough to show the number values in the cells. Do one dimensional distance formulas first. What is the distance (measured in units of “number of cells”) from cell B2 to cell L2 (which is directly above L12)? Now on your spreadsheet, establish the grid shown in #3 and write a distance formula in B2 to calculate distance to L2 (remember that you will be using the numbers in the first row). Don’t forget that formulas start with “=”. Try copying (use “copy” and “paste”) that formula all across row 2 up to column Y. Check the numbers in some of the cells. Is your formula working?
7. My guess to the question asked at the end of section 6 is that
“No, my formula isn’t working.” If you select a few of the cells
where the formula was copied (like C2 or G2, etc.), you will find that
if you typed “L1-B1” as your formula for distance, BOTH the L1 and the
B1 are updated in the copying process! But you don’t want both updated
– in cell C2, for example, you want the formula to read “L1-C1” so that
the “L1” reference stays the same but the “B1” changes to “C1”; likewise
in G2 you want the formula to read “L1-G1”. Even after cell L2, for
example in P2, you can use the formula “L1-P1” which will be negative,
but in the Pythagorean theorem you will eventually square these distances
and so the negative sign will not be significant. In summary, you
want the “L1” part of the formula to NOT update but rather to stay the
same. You can force a cell reference to stay the same with $ in front
of each piece (the letter and the number piece) of the reference.
In your new formula for cell B2, try typing the formula “$L$1-B1” and then
copying it across the first row. Is it now working?
8. Yay! Now the “L1” stays the same across the board and
so the distances are measured in cell block units correctly! Now
try copying the formula in B2 into the first two rows by copying cell B2
and then pasting in the selected rows 2 and 3. Does this give the
horizontal distance between, for example, cell B3 and L3? Note that
you would need this horizontal distance as part of the Pythagorean Theorem
formula for the overall distance between B3 and L12 in the final formula.
Try the copying now. Oops. It doesn’t work. Look closely
at what was copied in B3. You don’t want “$L$1-B2” because B2 is
not one of the cell references that has the grid reference numbers in it
for distances. Cell B1 has the distance grid. So you need to
force the number to stay at “1” for the second half of the formula, but
you do want the letter part to update. For example, in cell G3 you
want the formula to say L1-G1 (not B1) to calculate that distance.
In order to force the number part of the second reference to not change,
use a $ again. Now in cell B2, type your new formula as “$L$1-B$1”.
This forces the “L1” to stay the same always and the “1” to stay the same
always, but since the “B” doesn’t have a $ in front, it will automatically
update as it is copied, which is what you want to have happen. Try
the new formula in B2 and copy it in the first two rows. Does it
now work?
9. Yes! Now both rows correctly give the horizontal distance between any given cell and L1, the horizontal location of Earth. Since it works for two rows, it should work for all of them. Try copying your B2 formula in all 25 rows and across all columns up to Y of the grid. Is the horizontal distance working for all of them?
10. Remembering how you programmed cell B2 for these horizontal distances, now wipe all cells empty (you can do this by selecting all cells and under “Edit” choose “Clear” and “all”). Now try the vertical distances. In cell B2, write a formula to calculate the vertical distance from B2 to B12 since the Earth is located in row 12. Then experiment with the $ in order to force parts of the formula to stay fixed as you copy it first all the way down the first column and eventually throughout the whole grid. Think about which portions of the formula should change as it is copied to different cells and which portions should stay the same. Do this experimentation now until you become comfortable with the use of the $ to write and copy this formula.
11. Once you’ve got the vertical part working, it’s now time to put the horizontal and vertical together using the Pythagorean Theorem to calculate the total straight-line d between any given cell and L12 where Earth is located (see diagram in #3). Actually, you will want to use the Pythagorean Theorem to calculate d^2 because that is what you use in the gravitational formula. Try writing the formula for d^2 between cells B2 to L12 in cell B2, using $ to force certain parts of the formula to remain unchanging when it is copied and allowing other parts to vary as necessary when copying occurs. To get the squared function in your formula, you can use the ^ above the 6 key. When you think you have the formula correct and copied correct, pull out a calculator and check a couple of cells to make sure. For example, pick cell G7 and by calculator calculate the d^2 to L12 and check with the computer’s calculation, and then do similarly for cell T20.
12. If the distance part is now correctly working, you’ve licked the hardest part of writing the formula in #1. However, we aren’t quite done with the distance formula. Look at the grid in #3. This represents a large region of space around the Earth, and so the total distance from B2 to L12 can’t reasonably be only a few meters (as your distance formula would indicate if each cell block represents 1 meter). So you need to now apply a scale factor, setting each cell block to represent some reasonable distance. I suggest that each cell block represent 1 Earth radius in length as a sort of natural scale factor. That means when the distance formula indicates “10” as the distance from L12, it will actually represent “10 Earth radii” in distance. To apply the scale factor, use the Earth radius (in meters) that you wrote in #2 above and multiply the whole formula for d^2 by this Earth radius squared (because it’s d^2 and not simply d). Use parentheses to enclose your complete formula in B2 and then multiply everything by Earth radius squared. If you express this rather large number in scientific notation, you can enter the number using “E” to indicate the exponent. Try this, copy the new formula throughout the grid, and check one or two spots by calculator to see if the scale factor is correctly applied. Of course these will be huge numbers that won’t fit in the cells (and so #### appears), but to see the actual number in a cell, you can select a cell and hit the “=” button which will show you the result of the formula’s calculation.
13. You are about there. You should now have the correct formula for a scaled version of d^2 in cell B2. Enclose this whole thing in parentheses (yes, I know that you are getting a whole series of nested parentheses, but doing it step by step keeps the formula correct) and precede it by the division slash. Now in front of the division slash, type in the rest of the formula from #1. Again, use the “E” for scientific notation numbers.
14. One last thing before you copy the formula throughout. Since this formula calculates the force of gravitation, the spaceship would have to provide the negative of this force to overcome gravity, and so at the very beginning of the formula, insert a negative sign so that you will actually be graphing the force needed to overcome gravity. This small step will result in a graph that is not “upside down”.
15. Copy your now complete formula throughout the grid......To give you something easy to check with, cell B2 should have “-49” and cell T20 should have “-76”. This will provide an easy check for you. There is still one problem at location L12 itself. Your distance there would be zero by your formula, and you can’t divide by zero. To correct this problem, assume that at location L12 you are not at the center of the Earth, but rather that you are on the surface of the Earth (hence one Earth radius would be your distance). By calculator, calculate what the force of gravity would be on the surface of the Earth for this spaceship, and manually enter that number in L12, overwriting the result put in by copying the formula throughout the grid. Remember to make the entry a negative number because this is the force needed to be overcome.
Part III Graphing the result.
16. Now that you have created the grid of Earth’s gravitational forces the spaceship would need to overcome, it is time to graph it. Highlight the entire grid (not including the first row and column, which are not forces calculated but rather serve to calculate distances only), and hit the graph button. In the graph choices, you will want to choose a 3-dimensional surface graph.
17. Eliminate all numbers on the x-axis and y-axis since they are meaningless in this context, and just keep the z-axis values which represent the values of the gravitational force. I suggest you eliminate all grid lines to better see the shape of the curve. Also put in appropriate titles and a label for the z-axis values.
18. Increase the chart size to make it easier to see once printed, and change all colors to white to improve contrast when printed on a black & white printer. Experiment with the look to give the best printout possible (note that you can grab a corner of the graph and move it around to get a different perspective). Use “print preview” under the “file” menu to view what it will look like before actually printing it. When you like what you see, print the result.
19. Do you now see why physicists sometimes talk about the “gravitational well” of planets? In order for something to escape Earth’s gravitational field, it has to escape this gravitational well. Based on this graph, explain why a person can’t throw a baseball from the Earth into outer space. General comments relating this toss attempt to the graph are all that are sought.
Return to Physics Home Page
____________________________________________________________________________________
Physics Lab 6 - Earth to Moon Gravitational Wells
Objectives: Use Newton’s Law of Universal Gravitation to program a spreadsheet to draw the gravitational well experienced by objects on a journey from the Earth to the Moon.
Materials: Spreadsheet
Procedure:
1. Read and successfully program the spreadsheet to draw the Earth’s gravitational well as described in that lab (Lab 5). You must be able to do that before you can proceed with this lab.
2. Now you will modify the gravity well (save the spreadsheet with a different name first in order to not change your original Earth Gravity Well document) to include the Moon as well as the Earth. First, move Earth to cell F12 and change the formula accordingly from L12 to F12. Next, extend the horizontal numbering up to 30 to have enough room to fit both the Earth and Moon on the grid. You will locate the Moon at cell Z12. For this spacing, you need to change your distance scale factor from 1 Earth radius to 20,000 km in order for the Earth-Moon distance to work out correctly.
3. Now you need to also modify the formula to include the gravitational force due to the Moon. Assume that the ship travels in a straight line from the Earth to the Moon for simplicity. In that case, the net force Fnet is given by
Fnet = (G*m*mE/dE^2) - (G*m*mM/dM^2)
mE = mass of Earth
dE = distance of ship from Earth
mM = mass of Moon
dM = distance of ship from Moon
Since you want the force to be overcome by the ship as opposed to the force acting on the ship, you again need the negative of Fnet. However, the best way to do this is to take the negative of the absolute value of Fnet by using the absolute value function in the spreadsheet. Simplifying the formula, you now want to program the force equation to be the following:
Fnet = |G*m*(mE/dE^2 - mM/dM^2)|
4. Once you have the correct formula in the uppermost left cell,
copy and paste it throughout the grid. Remember to modify cell F12
to be the gravitational force due to 1 Earth radius and cell Z12 to be
the gravitational force due to 1 Moon radius after you have pasted the
entire grid.
5. Now create the 3-dimensional graph as before. You will probably want to modify the scale on the z-axis in order to better see the Moon’s gravity well. I found that when I limited the minimum z-axis (the force) to be about 1/3 of the Earth’s well depth, the graph showed both the Earth and Moon wells reasonably.
6. Play around with the perspective by dragging the corners of the 3-D graph to get what you consider the best view. Also, I recommend turning off the colors legend for the z-axis to better get a black and white printout.
7. Can you estimate the “break even” point from your graph where the Earth’s gravity and the Moon’s gravity cancel each other out? When you think you have located that point, then go back to the data in the table and see if you can locate the distance from the listed data. Approximately what percentage of the Earth-Moon trip is completed when the spaceship reaches the “break even” point between the Earth and Moon?
Return to Physics Home Page
____________________________________________________________________________________
Physics Lab 7 - One and Two Charge Potential Graphs
Objectives: Use Coulomb’s Law to program a spreadsheet to draw the electric potential around one or two point charges
Materials: Spreadsheet
Procedure:
1. First successfully complete the lab drawing the gravitational well for the Earth (Lab 5). Then use the same procedure to program the shape of the field potential for one point charge. I suggest that you do not worry about the meaning of the magnitudes of the values – the shape of the graph is of primary importance. This means that you can choose an arbitrary value to represent the point charge’s value.
2. When you’ve completed the one point charge graph, reprogram the spreadsheet (and save under a different name) to graph the potential for two point charges in a manner very similar to that done for the Earth – Moon gravitational graph (Lab 6). Again, the values don’t represent any physical quantity directly, but rather are merely relative sizes when compared to one another.
3. When completed, trace a few equipotential lines on your graphs with a pen in color. Then draw a two-dimensional diagram of equipotential lines from the perspective of looking straight down from above onto your graphs.
Return to Physics Home Page
____________________________________________________________________________________