Chapter 7

Use a Spreadsheet to Find a Complete Orbit from Few Observations - Part A

The Procedure Followed in this Topic and its Continuation

To begin, a spreadsheet is employed to create a reference ellipse that is described by its foci and K, the sum of the distances from the foci to points lying on the ellipse. One focus is fixed at (0, 0), the other at (-4.5, 0).

Groups of adjacent points are then taken from the reference ellipse with the object of using them to re-obtain the reference ellipse. Group sizes of 2, 3, and 6 points are employed. 

Given one focus at (0, 0), attempts are made with the aid of the spreadsheet to find K and the other focus of the reference ellipse for these three cases. The thought being to see what effect the number of observations might have on the solution.

The dimensions of the reference ellipse are then scaled up to represent the path of an Earth orbiting satellite with the objective of determining the satellite's period and its positions as a function time. 

See  here for an iterative computational process and approximation method for finding position in an orbit as a function of time.

In this procedure, the spreadsheet is employed to model the satellite orbit. The model is then examined for adherence to the three empirical conclusions of Kepler.

Create a Reference Ellipse

The spreadsheet expressions are shown next:


Cell A1 indicates that one focus is at coordinates 0, 0.  Cell B2 indicates that the other focus is at -4.5, 0. Cell C2 is the sum of the lengths from the foci to points that are on the ellipse. 

Cells A7:A36 contain, not shown, given values for x ranging in increments of 0.25 from -5.75 through 2.0.

Initial y values are provided by the user to cells B6:B36.  Solver is employed to adjust these values until the x, y pairs become points on an ellipse.

The expressions in cells C6:C36 compute the distance from the focus at (0, 0) to the corresponding x, y coordinate pairs. The expression used in cell C6 is shown under it in blue text.

The expressions in cells D6:D36 compute the distance from the focus at (-4.5, 0) to the corresponding x, y coordinate pairs.

Cells E6:E36 contain the sum of the foregoing two distances less the constant K for each given x, y pair. Should the two distances sum to the value K, then the corresponding x, y pair will represent a point on an ellipse.  In such case the corresponding cell value would be zero.

The expression in cell E3 evaluates the average of the E6:E36 values.  The result is called the Average Absolute Error. 

(At times the expression E3 is changed by the user to = Max(E6:E36) to minimize the largest Errors.)

Solver is used to iteratively adjust the initial y values in such a way that the Error is minimized.  There may be, and in this case there are, given values for x, for which there is no value of y that will serve to zero the Error. 

In such event, when Solver has minimized the sum E3, some of the Errors E6:E36 will be much larger than the others.  At this point the user may reject the corresponding x, y pairs, as they are not coordinates of points forming the ellipse.  

The procedure begins by the user choosing initial y values.  As an ellipse is symmetric about the x-axis, if these are all chosen positive then it is likely that Solver will converge to y values that are on the positive side of the x-axis.  The initial y values are all set to +1 as is seen in the chart following:


Note that the Error is large.

Next Solver is employed.


See the two points at each end of the x-axis that are not on the ellipse.  These are removed by deletion and changing the range of E3 to (E8:E34).

A second application of Solver produces the desired set of ellipse coordinate pairs with a very small largest Error. See next chart.



Sets of coordinate pairs from this reference ellipse will next be considered as observations from which the task is to regenerate the entire ellipse and its remote focus.

Find the Ellipse that Fits the Observations

The same spreadsheet as was used to find the reference ellipse is used.  The essential difference is that B2, the x coordinate of the far focus and C2, K, are now adjusted by Solver to minimize the Error.  The Error is comprised of the Errors of the set of coordinate pairs that are now considered as observations.  Initial values, estimates, are required to start the solution.  B2 = -2.0 and C2 = +2.0 were used as the estimates.

Two observations are employed in the next example.  (One coordinate pair will not serve to locate  both K and the remote focus.)




The ellipse that is found is almost indistinguishable from the reference ellipse.

Would more observations improve the fit?  See results for three and six observations next.






The comparisons are not very conclusive.  Were the observations much less precise, the answer could be that the more observations that are used the better would be the result. 

This might have greater truth if suspect observations were given less weight in contributing to the Error than given to observations that had earned more confidence.

The Reference Ellipse as the Path of an Earth Satellite

Multiply the dimensions, in metres, of the reference ellipse by 10^7.  Convenient new units are megametres.  Assume the near focus of the ellipse to be at Earth's centre. Take it that the scaled up ellipse is the path taken by a small Earth satellite.

What is the Period of the satellite?  What is its velocity at any position in the path?  Taking perigee, nearest approach to Earth, as a starting position, what is the satellite's position versus elapsed time?

Begin answering these questions with determining the period P of the satellite. 

Newton, given Kepler's three conclusions, produced his Theory of Universal Gravitation and provided a refinement to Kepler's third conclusion that is seen next:

            (P/2π )2 =a3/(G(M+m))

The mass, m, of our satellite is presumed to be extremely small compared with Earth's mass, M, and is neglected.

The product G * M is Earth's Gravitational Parameter,  ~3.986004418 * 10^14.

The semi major axis of the orbit, a, is determined by scaling up that of the reference ellipse, i.e. to 3.25 * 10^7 or 32.5 megametres.

Carrying out the calculation, the Period, P, of the satellite is found as ~58,309.05755 seconds, or ~ 16.2 hours.

Position versus time and hence velocity is somewhat more difficult to calculate.  A procedure that requires either iteration using a root-finding algorithm or employment of terms from an infinite series can be found in the reference given in the first section of this topic.

This writer employed a different procedure; the 3D spreadsheet that is described in the second topic of this chapter was used with drag and buoyancy disabled.  The procedure was simple:  provide the altitude, guess a tangential velocity and then manually adjust that velocity until the resulting period of an orbiting object matches that of the scaled up ellipse. Then employ the spreadsheet orbit to determine positions and velocities versus time.

From the chart of the ellipse we note that there is a coordinate pair at (0, 1), the closest approach to the near focus. Scaled up, this corresponds to a position 10 megametres above Earth's centre. This point, for an Earth satellite is called perigee.  Earth's mean radius,  is ~6.371010 megametres. This amount is subtracted from the perigee position to provide the Earth altitude value for input to the spreadsheet macro.

To find the satellite path a starting tangential velocity of 8,000 metres per second was guessed.  Then, with about a dozen adjustments, and using a step size that was about 5 milliseconds the value 8,213.1272 for the velocity was found to result in a Period of 58,309.0505733 seconds, close enough to the value computed from Newton's refinement of Kepler's third law for a manual procedure. (The manual velocity adjustments could have been automated for an improved match.) 

The xy plot of the orbit of the satellite at equal time intervals is shown next:


The spreadsheet was designed to follow Newton's laws so it should not be surprising that the shape of the satellite orbit appears to be the same as that of the scaled up ellipse.  Given the gravitational parameter we have found the period of the satellite's orbit, its velocity at perigee that satisfies Kepler's third law and its velocity at intervals throughout its orbit.

Notice that the distances traversed in a fixed time interval are much smaller near the highest point of the orbit, apogee, than they are near perigee.


The satellite orbit produced by the spreadsheet match is examined for adherence to Kepler's first two conclusions.

Top Previous Topic Next Topic Topics