**Physics 581
Part 1: Teaching Dynamics with Excel97**

**Click
here for Physics 581 Part 2**

*Michael Fowler, UVa, Summer 1998.*

**Introduction**

In contrast to the *Galileo and Einstein* course, the material
presented here is designed to be directly useful to a teacher or student in a
more traditional high school or beginning university physics course on
mechanics. We still use some of the *Galileo and Einstein* material, but
we amplify many topics, and enrich the mix with some spreadsheets.

Really understanding Newton’s dynamics requires some appreciation of his greatest invention—the calculus. This is where spreadsheets are invaluable. By constructing a spreadsheet to find velocity as a function of time, taking differences of position at successive time intervals, and going on to do the same thing for acceleration, the student (in my experience) builds up a clearer idea of the meaning of derivative than that usually gained from formal mathematical manipulations. It takes some time to construct these spreadsheets, but I give very detailed instructions. This admittedly leads to the danger that the student will blindly build the spreadsheet without really understanding it, but comprehension will dawn with enough exercises: have the students plot different things, vary the speed dependence of the drag force, and, especially, vary the parameters (including the number of rows) until the spreadsheet gives nonsense, then try to understand why!

I have put the finished spreadsheets for some of the exercises on the web, so they can be downloaded and explored.

**Book:**

In this course, we just give a sample of some of the uses of spreadsheets in physics. If you want to see a wider range of more fascinating material, I strongly recommend purchasing "Spreadsheet Physics" by Misner and Cooney (about $28, I think). I bought it in 1992, and have used some of the ideas here, such as leapfrog integration, and the simple pendulum as a test of Euler versus Leapfrog. One unfortunate feature of the book is that it’s never been updated, so it’s set up for DOS-based Lotus 1-2-3, but the ideas are great. There was a template disk available, but I didn’t get that, so I don’t know if that would still work with Excel.

**Beginning
Dynamics: One-Dimensional Motion**

The first steps in dynamics are really understanding the concepts of *velocity*
and *acceleration*,

and all physics teachers know this is unbelievably difficult! The aim of the
first three lectures is to understand velocity and acceleration in
one-dimensional motion, with liberal *quantitative* use of the video
camera and the spreadsheet. We do this before introducing Newton’s Laws, but in
analyzing air resistance we assume that at constant terminal velocity the air
resistance drag force balances the weight, to find how drag force varies with
velocity (for stacks of coffee filters).

1: How Fast Does a Falling Ball Fall?

In the first lecture, we consider objects falling vertically under gravity. This motion has been studied since ancient times, and Aristotle tried to analyze the motion quantitatively. He was a brilliant man, but he got this one wrong. The question is, why? Galileo did much better, but that was almost two thousand years later. It’s worth pondering what Galileo did that Aristotle failed to do. It’s certainly worth doing Galileo’s experiment, as described in this lecture.

2: Analyzing a Video of a Falling Ball using Excel.

We show how to measure the motion of a falling ball much more directly, using a video camera and playing back frame by frame to track the ball. We then enter our findings into an Excel spreadsheet to find the acceleration caused by gravity.

3: Real World Effects: Air Resistance.

The video technique lends itself very well to measuring air resistance
effects. We drop small stacks of coffee filters, and find that they almost
immediately reach terminal velocity, and, we’re able to figure out that the air
resistance is proportional to the *square *of the speed. Including these
real world effects makes Aristotle’s point of view more understandable, too.

**Moving Up To
Two Dimensions: Projectiles, Planets and Newton’s Laws**

The idea of velocity is not too difficult to understand in two
dimensions—it’s represented by an arrow, a *vector*—but acceleration is
really hard! And, if you don’t thoroughly understand acceleration in two
dimensions, you don’t understand what Newton did—you don’t understand dynamics.
So this is *very important*.

4: First Ideas about Projectile Motion

The development of Galileo’s ideas on projectiles, and the dramatic connection of those earthbound ideas with the motion of the moon and the earth itself by Isaac Newton, is covered in my course on Galileo and Einstein—links 5, 6, 8, 9 below are to that course, links 7 and 10 supply additional material.

5: Vectors

6: Newton: from Projectiles to the Moon

7: Forces, Equilibrium, Frames of Reference and Newton's Laws

**Using Newton's
Laws to Solve Real Problems**

The motions of all particles from specks of dust to planets in orbit are
given with extreme accuracy by applying Newton’s Laws. If we know the forces a
particle experiences, we can immediately find its acceleration, that is, how
its velocity changes in time, and from that we can construct a map of its path
through space. So *in principle* we know how to solve the equations of
motion. But there’s a catch—writing down what we’ve just said mathematically
gives a second-order differential equation for the particle’s position as a
function of time, with (usually) a position-dependent force, and the equation
is usually going to be one we don’t know how to solve mathematically! The good
news is, though, that these equations *can *be solved numerically
(although that gets rapidly more cumbersome if we increase the number of moving
particles). And that’s where the spreadsheet comes in: at least for one particle
problems, in almost all situations an ordinary spreadsheet has enough power to
plot trajectories. As we shall see later, the method becomes unreliable for
very rapidly changing potentials, but this, too, is worth investigating,
because it provides insight into how far numerical methods can be trusted, and
how they can be improved.

11. Calculus Treatment of Falling ball with Air Resistance

In this first "real world" lecture, we do not use spreadsheets, but solve two air resistance problems (drag proportional to speed and speed squared) analytically. These solutions provide useful benchmarks to check our numerical methods for accuracy and reliability. We can find, for example, how the accuracy of our spreadsheet depends on the number of rows used.

12. Spreadsheet Treatment of Falling Ball with Air Resistance

This lecture gives extremely detailed instructions for constructing a spreadsheet solving the problem of a falling ball with air resistance. The complete spreadsheet is available for downloading below, but it is a valuable exercise to build it yourself! You will understand it much better, and be able to adapt it for other problems.

Download Spreadsheet for Falling Ball with Linear Air Resistance

Download Spreadsheet for Falling Ball With Air Resistance Proportional to Velocity Squared

13. Spreadsheet Treatment of Projectiles with Air Resistance

This is a much more interesting problem, and one with practical
applications. What is the trajectory of a projectile when air resistance is *not
*neglected? What angle of projection gives maximum range, for example? This
spreadsheet gives the answers!

Download Spreadsheet for Projectile Trajectory with Air Resistance

14. Spreadsheet Treatment of Simple Pendulum

This is not just a simple harmonic oscillator, but a pendulum with the string replaced by a rod, constrained to rotate in a vertical plane, so it can swing "over the top". To solve this mathematically takes elliptic functions, but this simple spreadsheet gives a very accurate account of the motion. And, you could add air resistance as an exercise.

15. From the Simple Pendulum to Planetary Orbits

Starting with the Simple Pendulum spreadsheet, we adapt it to a two-dimensional simple harmonic oscillator, then with minor changes to a spreadsheet for orbits under an inverse-square potential. Both the two-dimensional simple harmonic oscillator and the inverse-square potential have elliptic orbits, but we can see the important differences from these spreadsheets.

Spreadsheet for Planetary Orbits

16. More General Force Laws

It’s easy to adapt the planet spreadsheet to look at laws of force other than inverse square. We discover that the orbits become much more complicated. A perfect circular orbit is always an option, but it is unstable except for the inverse-square force! So the existence of the solar system means that the gravitational attraction is inverse-square. You can explore how the force law affects the orbits by downloading the spreadsheet below, but you will find that for attraction much stronger than inverse square, the planet rapidly falls towards the sun, than moves so fast that the numerical analysis becomes unreliable.

Spreadsheet for More General Force Laws

**Homeworks, etc.**