This post was prompted by a recent question at Fitting high order polynomials, asking for Excel methods to solve the equations for the radius of the Moon’s Lagrangian Point 1. All the methods described in this post have been added to the ItSolve Functions2 spreadsheet, which can be downloaded from:
ItSolve.zip
The question asked for a solution to two equations:
- Simplified: r = R*(Mm/(3*Me))^(1/3)
- Detailed: Find r so that (Me/(R+r)^2)+(Mm/r^2)=(Me/R^2)+r*(Me+Mm)/R^3
The simplified equation may be easily solved by entering as a spreadsheet formula:
=O7*(O6/(3*O5))^(1/3)
The spreadsheet also includes a User Defined Function (UDF) called Eval, that will evaluate a formula entered as a text string, as shown below:
The Eval UDF returns the same result as the spreadsheet formula:
The simplest way to solve the more complex formula is to use the built in Goal Seek function (on the Data Tab), under What-if Analysis. To use Goal Seek first enter a guessed value for the radius, r, then enter a cell formula to evaluate the function:
(Me/(R+r)^2)+(Mm/r^2)-((Me/R^2)+r*(Me+Mm)/R^3)
as shown below:
Then call the Goal Seek function (Data – What-if analysis – Goal Seek):
Goal Seek will adjust the value in the selected changing cell (O15) so that the “set cell” (O18) evaluates to the selected value (0):
The Goal Seek function is built-in, and is the most convenient for a one-off solution, but it is slow and cumbersome to use for a large range of data. The UDFs QuadBrent and QuadBrentT are much quicker and more convenient to use on tabular data. The input for the QuadbrentT function is shown below:
Entering the function returns exactly the same result:
Finally the function may be rearranged as a quintic polynomial, that can be solved with the RPolyJT function:
RPolyJT returns all 5 solutions of the quintic polynomial as an array function, but in this case there is only one real solution, which is the first value in the results array.
More details of the UDFs described above can be found at:
Newton Excel Bach, not (just) an Excel Blog 
Thank you for this information. It turns out that the equation I gave you (and which you used) is for the L2 point (beyond the Moon). The equation for L1 (between earth and Moon) is (Me/(R-r)^2)+(Mm/r^2)-((Me/R^2)+r*(Me+Mm)/R^3).
The same, except the numerator for the first term is (R-r)^2.
LikeLike
This is NOT my best day! I presented an incorrect formula for L1. The correct formula for Lagrange Point 1 (between Earth and Moon) is:
(Me/(R-r)^2)=(Mm/r^2)+(Me/R^2)-r*(Me+Mm)/R^3.
(where Me is Earth mass, Mm is Moon mass. R is Earth-Moon distance, r is distance of L1 from Moon.
(Sorry about that!)
LikeLike
Thanks for letting me know. I have corrected the wording in the spreadsheet example, and added the formula for Point 1. (now uploaded)
Note that when using the QuadbrentT function you just have to update the text formula on the spreadsheet and the solution will automatically update.
When using Goal Seek with the Excel cell formula you first have to update the formula, then run Goal Seek again to adjust the formula result to zero.
For the corrected Goal Seek example for Point 1 I have used the Eval Function to evaluate the text formula. To do that you have to add the cell for the r value to the end of the range listing the known values.
I have left extracting the quintic polynomial coefficients for the Point 1 formula as an exercise for you 🙂 (but it doesn’t really have any advantage over the QuadbrentT function anyway).
LikeLike