Back to Galileo and Einstein Home Page Physics 581: Teaching Dynamics with Excel97

Physics 581 Part 1: Teaching Dynamics with Excel97

Click here for Physics 581 Part 2

Michael Fowler, UVa, Summer 1998.


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.


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

8: More on Newton's Laws

9: Momentum, Work and Energy

10:More on Potential Energy

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.