Now that we have a simple animated projectile motion (previous tutorial) let’s try to add on the chart the three instantaneous speed vectors associated with the projectile.
These speed vectors are: the horizontal speed, vertical speed and the total speed vector. The model works in all Excel versions but in 2007 it’s rather slow.
Projectile Motion Tutorial #3 – adding speed vector animation
by George Lungu
Adding speed vector animation:
Now that we have a simple animated projectile motion (left chart) let’s try to add the three
instantaneous speed vectors associated with the projectile (right chart). These speed vectors
are: the horizontal speed, vertical speed and the total speed vector.
Speed
Vector-Y
Total Speed
Starting with this plain vanilla model let’s
add speed vectors to the projectile
<excelunusual.com> 2
Creating a horizontal vector:
Let’s plot the data in the table below on a 2D scatter chart. You can adjust the numbers to get
a different shape arrow at the end of the stick.
-0.2 0 0.2 0.4 0.6 0.8 1 1.2
A => B => C => B => D
We will use three tables like this for the three speed vectors with the following transformations:
– We need to have a global scale factor for the user to adjust to his preference
– In addition to the global scaling operation mentioned above, each of the three vectors will be
individually scaled by the three speed components (horizontal, vertical and total)
– All three vectors need to be translated from the origin to the projectile coordinates
– The vertical speed component needs to be rotated by +90 degrees
– The total speed component has to be rotated by arc-tangent( v_vertical / v_horizontal )
<excelunusual.com> 3
Let’s create an area in the worksheet with the following variables :
Current coordinates:these are calculated from two consecutive slices of the trajectory table.
The position of the slices is selected by the “Index” which is dynamically generated by the “Fire” macro and is found in cell B20.
We could have use exact formulas to calculate speed but this approach is also valid for future models which use numerical methods to derive the position of the projectile.
Global scaling factor:it is just a constant adjustable by user depending of how large he wants the vectors to look
Current coordinates:these are a slice of the trajectory table.
The position of the slice is selected by the “Index” which is dynamically generated by the “Fire” macro and is found in cell B20
Cell F36: “=OFFSET(C26,B20,0)”
Cell G36: “=OFFSET(D26,B20,0)”
Cell F39: “=(OFFSET(C27,B20,0)-OFFSET(C26,B20,0))/B16”
Cell G36: “=(OFFSET(D27,B20,0)-OFFSET(D26,B20,0))/B16”
x * x
next, current
time
This is how the speed components were estimated
(t is the time step)
<excelunusual.com> 4
Speed Vector-X is calculated by taking the Speed Vector General
coordinates, multiplying them by the general scaling factor and by the
Speed_x and shifting them by the current coordinates.
Speed Vector-Y is calculated by taking the Speed Vector General
coordinates, multiplying them by the general scaling factor and by the
Speed_y, rotating everything by 90o and then shifting them by the
current coordinates.
Total Speed Vector is calculated by taking the “Speed Vector General”
coordinates and multiplying them by the general scaling factor and by
the total speed (we use Pythagoras to estimate it from Speed_x and
Speed_y), rotating everything by arctangent(Speed_x/Speed_y) and
then shifting them by the current coordinates.
Aux_line_vert is generated from the tip of the Total Speed Vector
and the tip of Speed Vector-X.
Aux_line_horiz is generated from the tip of the Total Speed
Vector and the tip of Speed Vector-Y.
<excelunusual.com> 5
All these five groups of data must be added to the chart as independent series so that we
can give them different colors and styles.
To be continued…
by George Lungu <excelunusual.com>