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
Using the Excel Spreadsheet -- Planetary Data Needed for Calculations
Now, in order to use the Excel sheets for this analysis, we need to select and translate data about planetary orbits so that values published by NASA, and others, so they will work in this analysis. We begin by finding the values needed for the input section. Some can be obtained, or derived from NASA data, as with the derivation of b and c values shown below.
At the internet address https://nssdc.gsfc.nasa.gov/planetary/planetfact.html you can look up and compile orbital elements. Excel Image 3 shows the values I obtained from the site, followed by my translation to the variable names used in the ellipse calculations. Values “a”, “e”, and “inclination” are obtained directly from the site. Values “b”, and “c” are derived. See the Excel code in rows 37 through 46 show the calculations. The derivations of “b” and “c” are shown below: the lines indicate that I made the notes on Excel sheets.
Using the Excel Spreadsheet -- Getting the Final Results
Taking the values for Mars, and entering them into the Excel spreadsheet, we have an input section that looks like the following:
… and the output section looks like this.
...
If you build a scatter chart graph that includes the x and y columns, and then add a scatter graph of the xs and ys columns you can see how the ellipse is shifted so that the left hand focal point is shifted to the center. Excel might not put it exactly in the correct spot, and Apple Grapher.app provides a better example. You can get the data into Grapher.app by first copying two columns to their own sheet and then exporting them to a .csv file what can be imported into Grapher as a point set.
Below is a screen print from Excel that shows plots of the ellipse for Earth and the ellipse for Mars. Earth is the green circle in the middle and Mars is the blue circle on the outside. Because Excel cannot show three dimensions, we can only see this from two dimensions. Notice how the two ellipses are shifted off-center. That shifting puts the left-hand focal points for both ellipses onto the sun. This graph was made using the following steps.
Create the two x, y series' using the spreadsheet designed in this essay and substituting the a and b values for Earth and Mars. There are two steps. Earth first, then Mars.
Copy and paste ranges for xr and yr into a different spreadsheet with headings for Earth and Mars.
Select the two columns of data for Earth, including the header row.
Select: Insert--> Chart--> Scatter--> Smooth Line Scatter. At least those are the steps on my version of Excel on September 7, 2017. The actual steps may vary, based on the version of Excel that you are using.
At this point you should see the orbit for Earth. Mine appeared as a red ellipse and I changed the series color to green.
Right-click on the graph that has already been drawn and select "Select Data".
Under the "Series" box, select the "Add" button.
In the insert menu, select the column for the Mars xr column (data only) in the input box for "X Values".
In the insert menu, select the column for the Mars yr column (data only) in the input box for "Y Values".
Enter the word "Mars" in the box for "Name".
In the "Series" box, select the first range that should be named "Earth" but currently isn't, and then go to the "Name" box and remove the existing contents and enter "Earth"
Click "OK" and you should see both ellipses graced. Change the color of the "Mars" ellipse to blue and resize the box as needed.
Add titles, and hopefully you see something similar to the image shown below.
In a similar way, create the three dimensional data. Be sure to insert the angle of inclination. Paste each series into a spreadsheet, without the headers and save it as a .csv file. If you have done this correctly, you can open Apple Grapher.app and import each of the two three dimensional data sets into Grapher.
Below is a screen print from Apple Grapher.app that shows both the Earth orbit and the Mars orbit in a three dimensional graph. The three dimensional graph uses output xr, yr, and z. Those are in the right-most section of the output section.
If you look closely at this graph, you may notice that the earth orbit seems to be slightly higher than the Mars orbit in one area, and lower in another area. That is because the three dimensional presentation includes the z axis data and accounts for the angle of inclination between the Earth orbit and the Mars orbit. By rotating that around in Grapher, you get an interesting understanding of our relationship with the orbit of Mars.
Page updated
Google Sites
Report abuse