Description
2. Introduction
An early and ancient application of physical laws was the analysis of objects in
motion, particularly objects falling to earth under the influence of gravity.
In this assignment you will recreate just such an analysis, except you will use one of
the most modern software tools available: a spreadsheet.
The computers in the computer labs (and probably everywhere else on campus)
have Microsoft Excel installed already, but you can do the assignment on your own
computer if you prefer.
If you don’t have Excel you may use a different spreadsheet program like LibreOffice,
which is free. Please don’t buy Microsoft Office if you don’t have it already.
Whichever spreadsheet you choose to use, it must work like Excel so that when your
TA checks your spreadsheet, it looks and works as it should.
In this assignment you will create a spreadsheet model (which is a representation, or
simulation) of an object that falls under the influence of gravity. The object starts at
some initial height and has a positive (upward) velocity. You will calculate the time it
takes the object to reach its apogee (highest point), the time it takes to return to earth,
whereby the height is 0, and the instantaneous velocity of the object at periodic
intervals during its return to earth.
3. Objectives
The purpose of this assignment is to have you use a software tool that is commonly
used by engineers to solve problems: a spreadsheet. Below I present the problem to
be solved, but don’t worry if you don’t understand too much of it. The purpose of
this assignment is to have you use Excel, not to understand and solve difficult
problem.
Keywords: Microsoft Excel, spreadsheet, H: drive, number ranges, named ranges,
this assignment is to have you use Excel, not to understand and solve difficult
problem.
Keywords: Microsoft Excel, spreadsheet, H: drive, number ranges, named ranges,
charts / graphs / plots.
4. Background
These are formulas you will need to use in the assignment below. You don’t need to
understand how they work.
The time it takes a projectile with a positive (upward) velocity to reach its apogee is
given by this expression:
tap = -vy∕ g
where
tap is the time to apogee
vy is the vertical velocity
g is the acceleration due to gravity
The time it takes an object to fall from a certain height to the ground is given by this
expression:
t = √(-2d∕g) (sorry, my editor won’t let me make a full
square root symbol)
where
t is the elapsed time
d is the distance from the object to the ground
g is the acceleration due to gravity
The vertical height reached by a projectile after a certain amount of time when the
projectile has an initial vertical velocity is given by this expression:
y = y0 + v0t + 1∕2 gt2
where:
y is the instantaneous height of the object
y0 is the initial height of the object
v0 is the initial velocity of the object
t is the elapsed time
g is the acceleration due to gravity
The velocity of a falling object after a given amount of time is given by this
expression:
v = v0 + gt
where
v is the velocity
v0 is the initial velocity
g is the acceleration due to gravity
t is the amount of time that the object is allowed to fall
It is interesting to note that that these formulas are independent of the mass of the
object itself (i.e., there is no m in the formulas). These formulas hold for objects of
any mass. Or in other words, objects fall at the same speed regardless of how heavy
they are.
5. Assignment
Just because you don’t know Matlab yet doesn’t mean you can’t solve an
engineering problem that involves a lot of calculation. A spreadsheet is ideal for this
kind of problem.
Just because you don’t know Matlab yet doesn’t mean you can’t solve an
engineering problem that involves a lot of calculation. A spreadsheet is ideal for this
kind of problem.
Create a folder for CSE1010 if you don’t have one already. Use this folder for all your
work for this course. Inside the CSE1010 folder, make a folder called HW1. Each
homework project you work on will have its own files that need to be kept separate
from all the other homework projects.
Create a new spreadsheet in whatever spreadsheet program you are using. Save the
empty spreadsheet in the CSE1010 / HW1 folder. I called mine FallingObject.xlsx.
5.1 Identifying information
In the first 6 rows of column A, enter information like this (below). Use your own
name, the current date, you lab section number and your TA’s name.
You can change the width of the A column if you want to. To do that, click on the
small vertical line separating column heading A from column heading B, and drag
the line to the right.
5.2 Initial values
The next few rows will contain the initial values that will be used for the remaining
calculations. Add information in rows 8 through 11 like this:
Make sure that Initial values is bold, and that the numbers in column B are centered
and have 1 decimal place. To change the decimal places, select the three numbers
with the mouse and right click anywhere in the selection area. Click on the menu
item Format Cells, then under Category select Number, and set Decimal places to 1.
5.3 Name the initial value cells
If you click on cell B9 (the cell that contains -9.8), you can see in the cell name box
(just above the column A heading) that the cell is named B9. This makes a lot of
sense. However, if you click inside the name box you can enter a new name. Type
the capital letter G and then hit Enter. Now that cell can be referred to in two ways: by
its coordinates, which are still B9, and the name G.
the capital letter G and then hit Enter. Now that cell can be referred to in two ways: by
its coordinates, which are still B9, and the name G.
Continue with the next two numbers. Name the cell that contains 5.0 Vinit and the
cell that contains 1.0 Yinit.
5.4 Calculated values
Skip a blank row and then enter the heading Calculated values (this should be in cell
A13). Make sure it’s bold.
In the subsequent 4 rows, enter these names and units (s for seconds, m for meters),
but leave the numbers empty:
Instead of entering numbers you will enter a few formulas.
• In B14 enter the formula for tap = -vy∕ g. You named a cell Vinit, which
corresponds to v0, and you named a cell G, which corresponds to g. Thus, type this:
=-Vinit∕G (that’s “equals minus Vinit slash G”).
• Name cell B14 Tap.
• In B15, enter the formula for the height at apogee. Start typing from the equal
sign. The formula is y = y0 + v0t + 1∕2 gt2, where:
y0 is the same as Yinit
v0 is the same as Vinit
t is the same as Tap
g is the same as G
to multiply, use the asterisk symbol, which is Shift-8 on the keyboard
(thus, the product ab in Excel is a*b)
to square something use ^2 after it (thus, a2 in Excel is a^2)
• Name cell B15 Yap
• In B16 enter the formula for t = √(-2d∕g), where:
to take the square root of something, type SQRT(something)
d is the same as Yap
g is the same as G
• Name cell B16 Tg
• In B17 enter a formula that adds the time-to-apogee and the time-to-ground.
Use the cell names, not the cell coordinates. More hints: start the formula with =, use
+ to add. You do not need to name this cell.
Format the numbers in column B to have one decimal place. (Remember how? Select
the cells, right click on the selection, choose Format Cells, choose the category
Number, change Decimal places to 1, click OK.)
Here’s what my spreadsheet looks like now:
Here’s what my spreadsheet looks like now:
If your values are different, go back and find the errors.
5.4 Periodic values
Make rows 19 and 20 look like this:
These are bold, and center the headings in row 20. The first column is the time, the
second column is the height of the object, and the third column is the velocity of the
object.
5.4.1 Time values
Since we determined already that it takes 1.2 seconds for the object to hit the ground,
the values in the T (s) column should range from 0 to 1.2, and I have decided that
we should use 0.05 second increments. Here’s the easy way to do it:
• Enter 0 in A21.
• Enter 0.05 in A22.
• Use the mouse to select both cells, A21 and A22.
• Locate the small blue square in the lower right corner of the selection
rectangle. This is called the drag handle. See it here:
Grab the drag handle and drag it downward until the selection rectangle extends to
row 45. Excel will automatically fill the cells with numbers that go in 0.05 increments
up to 1.2. Center these numbers and format them using 2 decimal places.
Select the entire range of numbers 0.0 through 1.2 and name this range T. After you
have selected the range, you name it the same way you would name a single cell.
5.4.2 Height values
The formula for an object’s height is y = y0 + v0t + 1∕2 gt2. This formula must be
entered in each cell in the Y (m) column. This is similar to what you did in section
5.4.2 Height values
The formula for an object’s height is y = y0 + v0t + 1∕2 gt2. This formula must be
entered in each cell in the Y (m) column. This is similar to what you did in section
5.4, except that instead of using Tap for the time, you will use T. Enter the formula
once in cell B21 (it will start with =Yinit…). You should see the result 1.0 in that cell.
Click on the cell and drag the drag handle down to cell B45. The formula has now
been copied into all those cells. Center the numbers in the cells, and format them
using 1 decimal place. It should look like this:
Check if these numbers make sense: The “time to apogee” that was calculated
previously is 0.5 seconds. Here under the T column it seems that the object’s
maximum height is 2.3 meters at 0.5 seconds. Is 2.3 meters correct? The value
calculated previously for “height at apogee” is in fact 2.3 meters. These values look
correct.
5.4.3 Velocity values
We know the initial velocity of the object, we know the acceleration due to gravity,
and we know all the different time “snapshots” at which the object is falling. All that is
left now is to fill in the periodic velocities.
This formula must be entered into each cell in the V (m∕s) column:
v = v0 + gt
I think you can figure this out by now, right? (Hint: type it into one cell, drag it to the
rest.) Here is my complete table:
I think you can figure this out by now, right? (Hint: type it into one cell, drag it to the
rest.) Here is my complete table:
5.5 Plot height vs time
In order to make the data more understandable by humans, it is customary to make a
chart (or graph, or plot) of the data.
Use the mouse to select all the numbers including the headings in both the T (s) and
Y (m) columns, which should be rows 21 through 45 and columns A and B. Find the
menu option to Insert a Chart and choose Smooth Marked Scatter (or as close to that
as your spreadsheet program has).
menu option to Insert a Chart and choose Smooth Marked Scatter (or as close to that
as your spreadsheet program has).
The chart will appear in the middle of the window. Move it closer to the data
columns. Click on the title of the graph where it says Y (m) and change it to say
Height of a projectile over time. Change the axes to read Meters and Seconds.
Here’s what my spreadsheet looks like with the chart:
Note that the graph of the points looks a lot like a trajectory — in fact it is identical to a
trajectory plot. However, we can’t call this one a trajectory because we’re plotting
height on the Y axis vs. time on the X axis. If it were a trajectory, then the X axis
would have to be a horizontal displacement and not time.
5.6 Plot velocity vs time
Choose all three columns and create a smooth marked scatter chart similar to the
previous one that contains the height and the velocity. Actually all you want is the
velocity, so after you make the chart, click once on the blue dots (the ones for height)
and delete them all, leaving just the points for the velocity.
Name the chart Velocity of a projectile over time.
Name the vertical axis Velocity and the horizontal axis Time.
Name the vertical axis Velocity and the horizontal axis Time.
Place this chart next to the previous chart, or below it if you think that looks better.
5.7 Answer some questions
Type short one or two sentence answers for the following questions.
Copy and paste (or type) this question into cell A47:
Cell B14 says that the time to apogee is 0.5 seconds. At this time the vertical
velocity should be 0. Why is cell C31 not equal to 0?
Type your answer to the question starting in cell A49. Hint: change the decimal
places of some of the numbers to something greater than 1. You may see the
problem.
Copy and paste this question below your previous answer:
Cell B17 says that the time to ground is 1.2 seconds. At this time the height of the
object should be 0. Why is cell B45 not equal to 0?
Type your answer to the question below it.
6. Due date and submitting the project
This project is due by 11:59pm on Sunday, September 9, 2012.
Submit your spreadsheet file on HuskyCT. Your TA will show you how to do this
during lab next week, but you can probably figure it out on your own.
7. Asking for help
If you need help and you choose to ask for help by sending email to your TA or to
me, be as specific as you can with the subject line and with the question you ask.
The subject line “Help” is ok, but I won’t look at that until I have a block of time to do
it. However, if I see a subject line like “How do I make a cell bold?” I will probably
answer it right away.
8. Value and grading
This project is worth a maximum of 15 points. Your TA will grade you on the
following points:
• [2 points] Identifying information: All the information required must be
present.
• [3 points] Formatting: The spreadsheet must look similar to what I show you
here. Come as close as you can using your spreadsheet program. You may stick with
the default font that your program uses, but be sure the headings are bold, the
numbers are centered and have the specified number of decimal places.
• [3 points] Formatting: The spreadsheet must look similar to what I show you
here. Come as close as you can using your spreadsheet program. You may stick with
the default font that your program uses, but be sure the headings are bold, the
numbers are centered and have the specified number of decimal places.
• [3 points] Correct formulas: The formulas entered in the cells must be
correct.
• [3 points] Correct values: The values entered by you and generated by the
formulas must be correct.
• [2 points] Correct charts: The plots must be correct.
• [2 points] Questions and answers: You must have answered the questions
and come reasonably close to having a correct answer for each.
You are free to go above and beyond what is required here, but you will not receive
extra credit for it.