# Using Excel

You might think that calculating and plotting the orbit of a planet would be a task well beyond the fragile, confused mind of the beginning physicist, but that is not the case! In this activity, you will use an Excel spreadsheet to calculate and plot the orbit of a planet. The calculations aren't complicated, and they provide a nice review of kinematics and vector components at the same time.

 Parameters Graph Parameters Graph dt = 0.05 vo = 0.8 dt = 0.1 vo = 1.0 dt = 0.1 vo = 1.2 dt = 0.1 vo = 1.5 ## The Theory:

### Overview:

Suppose we place a small planet in the gravitational field of a large star. How can we calculate the orbit of the planet due to the gravitational force on it? It is relatively easy to perform an iterative calculation - that is, use the planet's current position and velocity to calculate its position and velocity a short time later, then use this position and velocity to calculate another position, and so on. Here's how the calculation goes:

1. If we know the planet's position, we can calculate its distance, r, from the star.
2. If we know the distance between the planet and the star, we can calculate the gravitational force on the planet.
3. If we know the gravitational force on the planet, we can calculate the planet's acceleration.
4. If we know the planet's acceleration, we can calculate how much its velocity will change in a short time, t.
5. If we know the planet's new velocity, we can calculate how much its position will change in a short time, t.
6. Go back to step 1.

### The Details: As an initial simplification, suppose that the star we are going to orbit is massive enough that we can consider it at rest at the origin. Now, place a planet in the star's gravitational field at point (x, y), and give it initial velocity components (vx, vy). The distance, r, between the star and the planet can be found easily from the Pythagorean Theorem: We can now calculate the gravitational force on the planet from Newton's Law of Universal Gravitation: Given the gravitational force on the planet, we can calculate its acceleration from Newton's Second Law: To simplify this calculation a little bit, let's assume the mass of our sun is such that GM = 1. Then:  To find the x- and y-components of the acceleration we can use similar triangles, as seen in the diagram at right: (The negative signs were introduced in the equations above to account for the fact that the acceleration components are in the opposite direction from x and y.)

Now, suppose the planet is allowed to move for a short time, t. The planet's velocity will change by an amount axt in the x-direction, and ayt in the y-direction, so the planet's new velocity components will be given by:

vx-new = vx + axt

vy-new = vy+ayt

Now that we know the planet's new velocity, we can calculate how far it will move in the short time t. To simplify things, if the time is short we can consider the planet's velocity approximately constant, and:

xnew = x + vyt

ynew = y + vyt

Even though this is a simple calculation, there is a fair amount of calculating to do to move a planet a very short distance - and the calculation cycle needs to be repeated many times in order to complete a single orbit. It makes sense to use a spreadsheet to do this repetitive calculation for us, and most spreadsheets will even graph the results!

Here is a screen shot of the top portion of the finished spreadsheet. (This spreadsheet has 200 rows.) It is designed so that you enter the starting y-component of velocity of the planet in cell B3. The planet starts from position (1, 0) (cells D7 and E7) with a velocity x-component of 0 (cell B7) . Here is a way to perform the calculations shown above using Excel. We assume that the (immovable) star is located at the origin, and the planet always starts from position (1, 0). The x-component of the planet's velocity is always 0, and you can adjust the y-component of the planet's velocity (in cell B4) in order to see its effect on the orbit.

The value in cell B3 represents the (short) time, t, between calculated positions. You may change it if you like.

To construct the spreadsheet, copy the values and formulas shown below into rows 1-7 of your spreadsheet, and also the formulas in cells B8-E8. Then use the copy feature of the spreadsheet to copy columns B through I - since each row calculates one position of the planet in its orbit, you will need at least 50 rows.

When you think the spreadsheet is working, use the Chart Wizard to make a scatter plot of the x- and y-positions (columns D and E) of the planet and Excel will plot the data points for you. I recommend making the chart in its own page, so you can have a nice, big picture.

Depending on the situation, your graph may contain too many or too few points. It seems easiest to construct a large spreadsheet (mine has 200 rows) and adjust the number of points plotted using the "Source" option in the Chart Menu. Simply edit the last number in the source line to reflect the number of points you want to see.

To adjust the spacing of the points on the graph, edit the time between calculations, dt (cell B3).

Here are the spreadsheet formulas needed to calculate orbits as discussed above. You need to type the formulas and values for rows 1-7, and cells B8 through E8. Then copy the formulas downward in each column until you have enough data points. Reference: The idea for this calculation originally came from the amazing Feynman Lectures on Physics many, many years ago. I calculated and plotted my first orbits (by hand and slide rule!) using this technique before spreadsheet programs were invented!

last update March 1, 2006 by JL Stanbrough