Embedded Files
<-- Return to Excel Statistics Page
Go to Setting up the Spreadsheet -- The Input Section
Go to Setting up the Spreadsheet -- The Output Section
Go to Planetary Final Results Section
Calculate Planetary Orbits in Excel
Keith Greiner
August 9, 2020
This essay is about how to calculate and graph a representation of the orbits of planets. While the graph is not sufficient for planetary navigation (of course) it should give you an idea of how the orbits relate to one another. If you’re not interested so much in orbits of planets, just think of it as an exercise in creative or critical thinking, with numbers, in an Excel sheet. There are three sections to this essay. First is this introduction and a description of an Excel input section. Second, is discussion of an output section that is here. Third, is a discussion of some planetary data that is needed for the Excel sheets. This third section includes information on how to graph the output. The third section is here.
The calculations and the resulting essay were motivated by my curiosity about the planetary orbits and my discovery that no book in the local Barns and Noble store showed how to calculate the orbits. All of the books on the bookstore shelf, bragged about their lack of math. The collective titles could have been "Astronomy without Mathematics". That's unfortunate. Many of us want to see the math, and we want to see it described in a way that communicates to a broad audience. Mathematics isn't all that hard. Really. In many cases, it's the explanation that makes it difficult.
Mathematics is an important tool of creative and critical thinking, whether the math is of the type used in accounting , or process management, or marketing, teaching (even the teaching at the elementary level), or space exploration. Still, too many people are willing to declare that they are unfamiliar with even the most basic of mathematical concepts. This unfamiliarity directly affects all of us in many ways. It is one reason why so many people doubt that climate change is real. Fortunately, the lack of familiarity can be changed by improving educational curricula, and by including mathematics in more of our mass media, and personal conversations. The subject of space and the planets is often seen in mass media fiction, and in the news. We all talk about it. So, why not include mathematical topics in those conversations. The world would be better off if those conversations also touched on mathematics.
And that's where Microsoft Excel comes in. Excel is an amazing tool that has made mathematical calculations within the reach of millions of people who, otherwise, would be lost to mathematical and statistical reasoning. In many respects, it can, and should, be said that Microsoft Excel and the Office suite of Word, Excel, Access, and Outlook dramatically changed how this world thinks. People give much credit to the computer, when they should also give credit to the programs, like Office, that work on those computers.
The operational goal, here, is to show how to calculate and draw planetary orbits. The result will be two-dimensional and three-dimensional ellipses that show the relative size and shape of the two orbits. Graph images are shown in this section. This essay does not include the right ascension and declination -- that will be another project. In this text I have tried to include as many steps as possible. Undoubtedly, some will be missed. I plan to review it from time to time in an effort to discover and make improvements. However, the goal is to be as complete and succinct as possible. Because the text is specifying as much detail as possible to make sure each little piece is described, you may find this is not an easy read. Go slow, read carefully, and review the links that are provided. and you can use the instructions to create your own spreadsheet. I could just include a complete spreadsheet, but the process of your creating your own Excel sheet is an effective learning experience. Go for it. You can do it.
Planetary orbits are simply ellipses. Most people have (or should have) studied ellipses in middle school and high school. This project uses the parametric equation for an ellipse that is:
x = a * cos(t)
y = b * sin(t)
where "t" is the angle of a circle from 0 to 360 degrees, but expressed as radians. Most programs like Excel, VBA, and c++ require that the angle be expressed in radians. One degree is 0.0174533 radians. Radians can also be calculated as r = pi/180. I could have just as easily used the standard formula for an ellipse, but I chose this one. The difference between the two formulae is described in an essay on how to draw an ellipse in Excel.
Any planetary orbit project needs to translate the seemingly obscure terms that astronomers use to describe planetary orbits into the terms that are used in those middle school and high school text books. You will see later on, that NASA data refers to a planet's "semi major" axis, but in terms of an ellipse, this is the terminology is "a". NASA data refer to the a term of "orbital eccentricity". In terms of an ellipse that is the "eccentricity". NASA data refer to "orbital inclination" that's an angle that we can use to plot a three dimensional representation of the orbit. The Excel sheet also calls for a value called "Degrees Rotation". That is the angle of a line drawn between the two focal points of the ellipse and can be used to rotate the ellipse. NASA will call it the "Longitude of the Perihelion." Knowing these terms, we can use high school algebra to derive the other values that are needed. Some of the calculations are represented in algebraic terms. Others are shown as Excel code. If you are not familiar with Excel code, now is a good time to become more familiar with it.
The mathematical step that is least likely to be familiar with readers is matrix multiplication. That is a fascinating area of mathematics that is may not be included in middle school and high school texts: but it should be. In this application, a two column vertical list of "x" and "y" values can be multiplied by a 2 x 2 matrix with a result being a new vertical list of rotated "x" and "y" values. Do not fear matrix multiplication, however. Excel can handle it easily. There are some special steps involved, because the =MMULT(Range1, Range2) function is known as an "array function". Read the links that talk about matrix multiplication, and read the Excel help screens as needed. Some of the other calculations on this Excel Statistics Guide site are array functions, so your knowledge of how to use the =MMULT(Range1, Range2) function has many other applications. Practice with the function and you should be good to go.
The information in this essay is based on essays that were previously made available on this site. They include the following:
How to Draw an Ellipse in Excel
Data Rotation via Matrix Multiplication
Three-Dimensional Rotation via Matrix Multiplication
Setting Up the Excel Spreadsheet -- The Input Section
Because the planetary orbits are elliptical, we begin with some information about ellipses:
Here, we have an ellipse that is an elongated circle. The center is at (0,0). The distance from the center to the far left is “a” and is the same as the distance from the center to the far right. That is the semimajor axis. I may also refer to it as “radius a”. The distance from the center to the top is “b” and is the same as the distance from the center to the bottom. That is the semiminor axis. I may refer that it as “radius b”. The two points labeled FP1 and FP2 are focal points. Here, I will focus on the left-hand focal point. In our solar system, that is where we find the sun: in most renditions of the solar system. The other alternative would be to show the sun at the right-hand focal point FP2. The Sun may be only at one of the two focal points. Whichever focal point does not have the sun assigned, is simply ignored. The distance from the center to either focal point is "c".
In the Excel sheet images, I refer to the distance "c" as FP1a or simply as FP1, or simply as FP or “Focal Point”. The distance "a - c" is referred to as FP1b.
The spreadsheet includes several other input fields
In rows 11, and 12, we have a value for “offset”. The x offset is the value of c, and is used to shift FP1 to the right, so it is on the (0,0) center point. That is necessary if I wish to rotate the ellipse/orbit around the sun. The y offset value could be used to do the same thing in the vertical direction. It is included, as a space that may be developed further in the future and is not used for this project.
In rows 14, and 15, we can enter a value for degrees of rotation. This is necessary if the ellipse is to be rotated around the (0,0) point. It is the Longitude of the Perihelion (LOP), or the difference between a planet's LOP and the Earth's LOP. It is necessary for further exploration, but is not used in the current planetary analysis because the examples of Earth and Mars have nearly perfect circle orbits.
In rows 17, and 18, we can enter the inclination of planetary orbits. That adjusts for the fact that the planetary orbits don’t line up perfectly, but are inclined, with Earth having a zero inclination. By comparison, Mars has an inclination of 1.85 degrees. Enter the degrees and the sheet converts the value to radians.
First, below is Excel Image 1a, that shows the input section.
Next is Excel Image 1b that shows the Excel code for Image 1a.
Spreadsheet cells that have a background color of tan are input cells.
Top-most rows and and left-hand columns on the spreadsheets are highlighted in yellow. You may notice that this is not what you see when you create a spreadsheet in Excel. I did this, because this text frequently uses a spreadsheet that is copied from Excel and (with an intermediary stop in Word) is pasted into this web page. It is impossible to copy and paste the borders of an Excel sheet for this purpose. Yet, we need need that row and column information in order to examine the Excel code. Therefore, in these examples, I have set up the first column and the first row with the appropriate label, and have presented the row and column values so that you can use them as a reference.
Rows 21 and 22, and cell range C21:D22 contain a rotation matrix. In matrix processes, a vertical 2xn matrix may be multiplied by a 2x2 array. If the 2x2 array contains the sin(…) and cos(…) values shown in Excel Image 1b, the left hand array is rotated around the origin by the number of degrees specified in cell D14 after having been converted to radians in D15.
Rows 24 through 66 show the output from the calculations that are set up in the input section shown above. A discussion of the output section is at this link.
After the Excel sheet has been set up for input and output, you will need some planetary data to enter into the input section. The needed information is at this link.
Go to Setting up the Spreadsheet -- The Input Section
Page updated
Google Sites
Report abuse