Kepler's Laws

Worksheets demonstrating Kepler's Laws of Planetary Motion.

Click on the image to download the workbook

Kepler's First and Second Laws

The first two worksheets have animated graphics demonstrating the elementary ellipse parameters and -- my favorite -- a planet moving around the ellipse, getting faster as it draws closer to the sun and slower as the distance increases. The worksheet solves Kepler's equation using the same iterative method Kepler himself used nearly 400 years ago. But he did it by hand.

I think it's funny that people still teach Kepler's 2nd law with almost the exact same words as he originally phrased it. The "sweeps out equal areas in equal times" thing is because the mathematics in Kepler's time was mostly geometry-based, so he tended to think in terms of lengths, areas. It was a geometer's way to parametrize the change of speed of the planet as a function of time -- that's why it sounds odd. Our modern analytic geometry makes it so much easier to understand.

Kepler's Third Law

Did you ever wonder how astronomers know how much the planets weigh?

If you plot the average distance of each planet's orbit around their central body (e.g., the sun) versus its orbital period in log-log scale, you'll get a nice straight line with inclination 2/3.

This is, in fact, a modern way to formulate Kepler's Third Law of Planetary Motion. The original version, as he wrote in 1619 in his book Harmonices Mundi, reads something like: "The squares of the periodic times are to each other as the cubes of the mean distances". Or, more concisely, T²=kR³, where T is the planet's orbital period, R is is mean distance from the central body and k is a proportionality constant. It took Kepler (1571-1630) nearly his whole life to achieve this result; nowadays we can reproduce it in an Excel worksheet in a few minutes.

Isaac Newton later found out, when developing his theory of gravitation, that k is in fact GM/4π², where M is the mass of the central body and the mass of the planet is negligible compared with M.

In other words, Kepler's third law can be used calculate the mass of the central body. That's how astronomers know the mass of the sun and other planets that have satellites.

The last worksheets in the workbook have log-log plots of the periods and distances for the planets in the solar system, the jovian system (Jupiter and its satellites) and the saturnian system. You can verify that the inclination of the line is very close to the value of 2/3 predicted by theory. The y-intercept is used to compute k, which, in turn, is used to calculate the mass of each central body.

To Dos

The current version is 2D-only. I'd like to move to 3D.

• Make a 3D version of the Orbits sheet, showing the geometry of the situation as astronomers define.
• Make a 3D version with two planets and the radius vector from one to the other
• Make a "planetarium"-style sheet, showing the apparent motion of a planet as seen from the other

Besides, we have been limited to planets and elliptic orbits. It would be nice to have some comets:

• Make a sheet with a comet in a parabolic orbit
• Make a sheet with a comet in hyperbolic orbit

Parabolic and hyperbolic orbits are more challenging because Kepler's equations are ill-behaved when the eccentricity is near 1. This forces us to use some interesting mathematical tricks to get decent convergence.

Contribute: If you implement any of this (or any other interesting stuff, for that matter) and you don't mind sharing it, I'd like to have it! Send me and I'll add to the workbook with the proper credits.

Mini-FAQ

Q1. How do you make the scrollbars change the cells' values? Is that some hidden VBA code?

A1. No. Actually, I think that the beauty of it is that there's no VBA code at all.

In fact, having scrollbars or other form-like controls change the values of some cells has been a standard (yet surprisingly little known) Excel feature for many years.

Here's how you use it: right-click any toolbar icon -- a pop-up menu will let you choose other toolbars. Left-click on the "Form" option; this will show the (normally hidden) "Form" toolbar. There you can select several controls, including the scrollbars: click on the appropriate control icon and the mouse cursor will turn to a thin cross. Drag and drop a rectangle on the worksheet and a control with that size will appear. Right-click it with to get the context menu and ask for the "Format control" option. A dialog box will appear that allows you to define the control's maximum an minimum values, increments, and, most importantly, which cell it is linked to.

As soon as you link a control to a cell, anytime you click on that control, causing it to change its value, will trigger a recalculation (assuming automatic recalculation is globally enabled, which it probably is, since it's the default). If some graph depends on values that ultimately depend on some cell linked to that control, it will be redrawn as well.

In Excel versions 2003 and above, it is useful to surround the graph with cell values that change to force it to be contained in the bounding box that will be updated. Otherwise, the graph will be only partially redrawn (or not at all!) at every value change, making animations become visually ugly.

Incidentally, OpenOffice has a similar feature that works almost exactly the same way, but the graph refresh takes seconds, making it way too sluggish for animations. Besides, OO's graphing capabilities lag far behind Excel's -- I have no idea why OO's XY graphs still don't support something as simple as non-ordered X values. That's the reason I don't make avaliable OO versions of this worksheet.
top