Excel formula to plot (x,y) values for bullet trajectory ...

[M100C]

Hey All,
I am wanting to help my son with a science project. I need an Excel
formula to calculate (x,y) points along the trajectory of a bullet.
The muzzle of the gun would be at (0,0). Here's what I have so far:

Initial Velocity ($B$1): 700 yds/sec
Angle ($B$2): 2 degrees
Gravity ($B$3): -10.728 yds/sec^2 (converted from m/s^2)

I looked up trajectory in Wikipedia, and this link provides the
equation:

http://en.wikipedia.org/wiki/Trajectory#Derivation_of_the_equation_of_motion

So, I have created a column of x-values from 1 to 300 (yds). To
calculate the corresponding y-values, I am using the following Excel
equation:

y = -($B$3*(1/COS(RADIANS($B$2))^2)/(2*$B$1^2)*A7^2+A7*TAN(RADIANS($B
$2)))

where, A7 is the variable cell containing the x-value (of 1), but I'm
not getting the correct parabolic path when I copy this formula and
plot the points. Can anyone look this over and see the error in my
formula?

Thank you,
Chris

[ji...@specsol.spam.sux.com]

Well, to start with, the link doesn't really apply to real bullets in the
real world as drag is neglected.

What you want to research is ballistic equations, which do concider drag.

You might start here for some background:

http://en.wikipedia.org/wiki/External_ballistics

NASA has a page with the fundemental equations which do include drag and
some good information on what drag does to trajectory:

http://www.grc.nasa.gov/WWW/k-12/airplane/flteqs.html

[Androcles]

"M100C" <clb...@gmail.com> wrote in message
news:ee83e716-a60e-4722...@p21g2000yqm.googlegroups.com...

==============================================
Oh dear...
The height h (or y) is a single value, as is the range R (or x) so
I cannot visualize what A7 can possibly mean.

1)
If you read through the wackypedia article you will see that it says
"Assume the motion of the projectile is being measured from a Free fall
frame which happens to be at (x,y)=(0,0) at t=0. "
This tells us straight away that for a spreadsheet the first column
you need to create is the time column of t-values, not the x-value.
(x,y) will have different values at t = 1, t = 2, t = 3 and so on.

2)
The next thing to notice is
"Relative to the flat terrain, let the initial horizontal speed be v_h =
vcos(theta) and the initial vertical speed be v_v = vsin(theta). "
Ok, so the INITIAL speed is a function of the angle, but the
final speed isn't! The horizontal velocity gradually reduces with
time because of drag, and the vertical velocity reaches zero at
the top of the arc, then reverses as the missile falls, so it goes
from positive to zero to negative, and is also affected by drag.
In other words the angle should be changed at each time interation
or not used at all if using a spreadsheet.

3)
You are thinking about units conversion (yards to meters and
degrees to radians) instead of concentrating on the algebra of
the physics. Although it must be taken into account, now isn't
the time to be thinking about it as it will add confusion for
yourself and more importantly, your son. Whoever created
a function called "RADIANS()" did nobody any favours,
one can convert degrees to radians by multiplying by PI()
and dividing by 180, and who cares about degrees or yards
anyway?

4)
Here is how to do it:
Create a vertical column of time values, initial value
zero, subsequent values the previous value + 1.
Example:
0
=A1+1
(copy down)

Create a vertical column of horizontal velocities
Remember there is drag, so the velocity decreases.
Example
(initial velocity)
= B1-(some constant)
(copy down)

Create a vertical column of y-VELOCITIES
Remember that vy depends on gravity

Create a vertical column of x-POSITIONS
Remember that x = vt.
Create a vertical column of y-POSITIONS
Create an x-y plot of positions.

Forget wackypedia, that's for slide rule users.
Computers do a better job from first principles.

[Peter Webb]

"M100C" <clb...@gmail.com> wrote in message
news:ee83e716-a60e-4722...@p21g2000yqm.googlegroups.com...

There are two ways of doing this.

You are trying to do it the hard way, which is to produce the equation of
the curve for the bullet. The easier way (which better shows what is going
on) is as follows.

Work out the x and y components of the bullets initial velocity. If its
initial speed is s, these are s * cos(theta) and s * sin(theta) respectively
where theta is the angle from horizontal. Lets call the horizontal and
vertical components Vx and Vy.

So if the bullet starts at x=0, y=0 at t=0 (or wherever you want to start)
with velocities Vx and Vy, then 0.001 seconds later it will be at 0 + Vx *
0.001, 0+ Vy * 0.001, the new Vx will be unchanged, and the new Vy will be
Vy - 0.0001 * 9.8m/s.

0.0001 seconds later the same rule applies; its new x position is its old x
position + 0.001 * Vx, its new y posn will be its old y posn + Vy * 0.001,
and its new Vy will be the old Vy - 0.001 * 9.8 m/s.

So, first create a column in Excel containing:

Starting t (=0)
Starting x
Starting y
Starting Vx
Starting Vy

then every next column provides the new t, x, y, Vx (which doesn't change)
and Vy as follows:

next t = previous t+0.001
next x = previous x + previous Vx * 0.001
next y = previous y - previous Vy * 0.001
next Vx = previous Vx
next Vy = previous Vy - 9.8m/s * 0.0001

This models what is happening physically - in terms of the forces on the
bullet - and the parabolic arc will be formed "naturally".

This seems a lot clearer than simply trying to graph the parabola which you
expect it to follow, and produces (hopefully) almost exactly the same curve.

[Marvin the Martian]

Since X0=0

x = v *cos(angle) * t
For practical purposes, ignore aerodynamic drag.
y = v * sin(angle) * t - ( g * t^2)/2

Note how v * cos(angle) is the velocity in the x direction and v * sin
(angle) is the velocity in the y direction.

G is the acceleration of gravity... in feet it is about 32 ft/sec^2 so in
yards it is about 10.6 yards/sec^2. I suspect your 10.782 is better.

So, to make the spread sheet you'll need three columns: t, x, and y. If
you put the time increments in column A, and the rows of data start at
row 4

For the t column and row 4 (A4)
= 0

for the x column and row 4
=B$1$ * cos(B$2$) * A4

for the y column and row 4
= B$1$ * sin(B$2$) * A4 - (B$3$ * A4^2)/2

Now you can plot the trajectory on a scatter plot.

[Sam Wormley]

On 2/18/12 9:34 PM, ji...@specsol.spam.sux.com wrote:
> NASA has a page with the fundemental equations which do include drag and
> some good information on what drag does to trajectory:
>
> http://www.grc.nasa.gov/WWW/k-12/airplane/flteqs.html

NASA also has an excellent publication about the Attribution
of the present-day total greenhouse effect
http://pubs.giss.nasa.gov/docs/2010/2010_Schmidt_etal_1.pdf

[Rock Brentwood]

On Feb 18, 6:06 pm, M100C <clbe...@gmail.com> wrote:
> y = -($B$3*(1/COS(RADIANS($B$2))^2)/(2*$B$1^2)*A7^2+A7*TAN(RADIANS($B
> $2)))

>
> Can anyone look this over and see the error in my formula?

Your problem is simple, easy to see and easy to solve. Not everyone
knows Excel here, so your formula is virtually unreadable. But more to
the point: it's unreadable *to you*. The proof is that it's not doing
what you expect, therefore what you think you're reading is unrelated
to what it's actually saying. QED. The second proof is ... well just
look at it!

So, the solution, quite simply, is to translate the formula back into
normal ordinary math and see what you get. Then translate it back into
Excel and see if you get the same thing.

(Ideally, there should be either (a) two separate people involved in
each part of the Double-Translation Protocol, or (b) the same person
but on separate occasions.)

It's the same general method I use to reverse-engineer specifications,
code, binary, programs (which the way most people write programs is
just as bad as reading binary, so I sometimes just skip the code and
read the binary). Add to that: mathematical texts and ordinary
language text.

In flight-critical or other safety-critical systems ... in the US at
least ... one has the DO-178B standard which basically prescribes just
that: strict object-code validation. Your problem with the Excel code
is a perfect reflection of what goes on there. People write unreadable
code, they get tripped off, and pretty soon the vehicles aren't going
on the trajectories they're supposed to be following because the code
isn't doing what you expected it to ... because it's manifestly
unreadable.

If the same person is involved in the Double-Translation Protocol then
item (b) above applies. But if there's only time for "one separate
occasion" then add (c) the same person on one occasion in two separate
ways. So, you should first *separately* formulate just what the
solution is that you're trying to implement.

For typical text-book ballistics, one has 3 formulae:
a = constant, v = v_0 + a t, r = r_0 + v_0 t + 1/2 a t^2
for the vectors a, v and r representing acceleration, velocity and
position respectively. Just plug in the appropriate values from the
boundary conditions
r_0 = 0
v_0 = 700 yd/sec (i (cos 2 deg) + j (sin 2 deg))
a = (-10.728 yd/sec^2) j
i = unit vector pointing horizontally
j = unit vector pointing up
deg = abbreviation for the number pi/180.

The Wikipedia articles on ballistics are far more detailed (and much
better-referenced and far broader) than any textbook reference and are
not required for toy problems in high school or college Physics.

And just to add to this: they're far-better researched than any
textbook, with real-time error tracking/correction, unlike textbooks
and monographs ... even from Cambridge University Press which are
FILLED WITH ERRORS because their publishers are too worried about
making deadlines to actually REVIEW what they publish before
publishing it.

Textbooks are just as bad, if not worse. I've found numerous errors in
my old high school and college texts, for instance. Americal
Mathematical Society, just as bad. North Holland ... put out
monographs that didn't even try to get its math or proofs right.

You're not going to find in a typical text the details on bullet and
gun construction (and how the particular constructions used are
carried out so as to create bullets with stable trajectories, just to
give you an example. Those are all linked to from the Wikipedia, along
with references to the professional research literature.

So, don't let anybody tell you that the Wikipedia is somehow "wacky"
or "substandard" without taking to task their ancient, 20th century
obsolete Baby Boomer laden mentality ("media is something we're fed as
consumers by Old Media content providers, which we sit on our fat
aging asses and passively watch") by demanding of them: "inaccurate
compared to what? Cambridge University Press? AMS' monographs? They
don't correct or log their errors in real-time in cyberspace under
simultaneous review by thousands of knowledgeable ."

I shouldn't have to be constantly calling on them to *review their
books* before they publish them so that I don't have to keep sending
them error lists. I don't have time for that and I'm certainly not
going to be doing their job for them for free!

[Dirk Van de moortel]

M100C <clb...@gmail.com> wrote in message

ee83e716-a60e-4722...@p21g2000yqm.googlegroups.com

It's quite easy.
The time dependent equations of motion are:
x(t) = v0x t
y(t) = v0y t + 1/2 a t^2
where v0x is component of initial velocity in x-direction,
v0x is component of initial velocity in y-direction, so
v0x = v0 cos(p)
v0y = v0 sin(p)

Eliminate t to get the trajectory as a function of x:
y(x) = v0y/v0x x + 1/2 a/v0x^2 x^2
where of course
v0y/v0x = tan(p)

So, you formula should be
= A7*TAN(RADIANS($B$2)) + 1/2*$B$3/($B$1*COS(RADIANS($B$2)))^2*(A7)^2
The only error in your formula is the minus sign.
You also should have created a colums with x-values ranging from 0 to 3170 or so:
If you solve for y=0, you get
x ( v0y/v0x + 1/2 a/v0x^2 x ) = 0
giving
x1 = 0
or
x2 = - 2 v0x v0y / a = 3186.118

The graph is a perfect parabola.
See http://users.telenet.be/vdmoortel/dirk/Stuff/Trajectory.xls
The x2-value is in cell D2

Dirk Vdm

[Androcles]

"Dirk Van de moortel" <dirkvand...@hotspam.not> wrote in message
news:jhtq5r$2b4$1...@speranza.aioe.org...

[CWatters]

That approach is likely to be the one taught at school level.

eg Work out x(t) and y(t) seperately then plot an xy scatter.

[ji...@specsol.spam.sux.com]

Shove a greenhouse effect up your trolling, babbling ass.

I would wager you have never read this for comprehension and especially
the last paragraph:

"We conclude that, given the uncertainties, that water
vapor is responsible for just over half, clouds around a
quarter and CO2 about a fifth of the present-day total
greenhouse effect."

[Sam Wormley]

On 2/20/12 12:06 PM, ji...@specsol.spam.sux.com wrote:
> "We conclude that, given the uncertainties, that water
> vapor is responsible for just over half, clouds around a
> quarter and CO2 about a fifth of the present-day total
> greenhouse effect."

That's reasonable agreeing with many climate models.

[ji...@specsol.spam.sux.com]

Perhaps, but it certainly isn't the Chicken Little, panicked, doom-and-gloom
idiocy you keep posting.

Oh, by the way, shove it up your ass, troll.

[Sam Wormley]

I generally post about the physics of climate change, jimp. I
often refer folks to articles and papers relevant to the discussion.
Climate change is certainly having economic impact on our lives,
for example:

> Graphic Plot of thirty year trend of extreme weather in terms of
> Billion dollar events.
> http://lwf.ncdc.noaa.gov/img/reports/billion/timeseries2011prelim.pdf

Populations that depend on glacier melt for fresh water will be in
trouble in future decades if current trends continue.

[ji...@specsol.spam.sux.com]

Sam Wormley <swor...@gmail.com> wrote:
> On 2/20/12 1:29 PM, ji...@specsol.spam.sux.com wrote:
>> Sam Wormley<swor...@gmail.com> wrote:
>>> On 2/20/12 12:06 PM, ji...@specsol.spam.sux.com wrote:
>>>> "We conclude that, given the uncertainties, that water
>>>> vapor is responsible for just over half, clouds around a
>>>> quarter and CO2 about a fifth of the present-day total
>>>> greenhouse effect."
>>>
>>> That's reasonable agreeing with many climate models.
>>
>> Perhaps, but it certainly isn't the Chicken Little, panicked, doom-and-gloom
>> idiocy you keep posting.
>>
>> Oh, by the way, shove it up your ass, troll.
>>
>>
>
> I generally post about the physics of climate change, jimp.

No, you don't, swormley1.

What you generally cross post to sci.physics that is related to climate
change is arm-waving, media puff pieces, weather reports, and hysterical
doom-and-gloom speculation.

> I
> often refer folks to articles and papers relevant to the discussion.

I'm laughing my ass off on that one.

Do you really believe that nonsense?

> Climate change is certainly having economic impact on our lives,
> for example:

Only in increased taxes and tax money pissed away to "fight global
warming" which could have been used for something usefull, otherwise, no.

>> Graphic Plot of thirty year trend of extreme weather in terms of
>> Billion dollar events.
>> http://lwf.ncdc.noaa.gov/img/reports/billion/timeseries2011prelim.pdf

That "global warming" has anything to do with that is just arm-waving
speculation.

> Populations that depend on glacier melt for fresh water will be in
> trouble in future decades if current trends continue.

That is hysterical doom-and-gloom speculation.

Everything you posted to sci.physics in this post is 100% physics free.

BTW, shove it up your ass.

[hanson]

"Sam Wormley" <swor...@gmail.com> wrote in message
news:buKdnT9tKOt1N9_S...@mchsi.com...

> On 2/20/12 1:29 PM, ji...@specsol.spam.sux.com wrote:
>> Sam Wormley<swor...@gmail.com> wrote:
>>> On 2/20/12 12:06 PM, ji...@specsol.spam.sux.com wrote:
>>>>

Pennino quoted & wrote

"We conclude that, given the uncertainties, that water
vapor is responsible for just over half, clouds around a
quarter and CO2 about a fifth of the present-day total
greenhouse effect."
>>>
Sam wrote:
That's reasonable agreeing with many climate models.
>>

Pennino wrote:
Perhaps, but it certainly isn't the Chicken Little, panicked,
doom-and-gloom idiocy you keep posting.
Oh, by the way, shove it up your ass, troll.
>>

Sam wrote:
I generally post about the physics of climate change, jimp. I
often refer folks to articles and papers relevant to the discussion.
Climate change is certainly having economic impact on our lives,
for example:
>

Pennino wrote:
Graphic Plot of thirty year trend of extreme weather in terms of
Billion dollar events.
<http://lwf.ncdc.noaa.gov/img/reports/billion/timeseries2011prelim.pdf>
>

Sam wrote:
Populations that depend on glacier melt for fresh water will
be in trouble in future decades if current trends continue.
>

hanson wrote:
Nobody will be in trouble. Just construct the necessary
catch Basins for the rain/snow-melt water run off.
>
Naturally, Green Turds & Enviro Shits, such as you,
will object, like they always do, and just so did as in
the case of the Alberta to Houston Keystone XL
pipeline, over some trumped up and manufactured
enviro issue despite the fact that there are already
some 84 other, now existing, oil and gas pipelines,
which do criss-cross the same region & real-estate
and were there for last 50-90 years.
>
= Greenism is the politics of Hysteria, Misanthropy & True lies.
== GW, AGW & CC is nothing but Green Pornography.
=== There is nothing filthier than an environmentalist.
==== There is nothing more corrupt then an enviro.
===== There is nothing more immoral then an enviro.
====== There is nothing more perverted then a Greenie