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
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
its positions as a function time.
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
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)
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
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
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
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
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
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:
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.