Solving the Lagrangian Point equation for the Moon

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:

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:
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:
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:

Posted in Excel, Maths, Newton, UDFs, VBA | Tagged , , , , , , | 3 Comments

Malinda sings A Song About Nothing

Malinda Kathleen Reese mostly sings popular songs, after passing the lyrics through Google Translate multiple times.

But this is something else:

Posted in Bach | Tagged , , | Leave a comment

ULS design of circular reinforced concrete columns

The RC Design Functions spreadsheet has been updated (to Version 8.04) with the addition of a CircU function for ULS design of circular reinforced concrete sections under combined bending and axial load.  The new version, including full open-source code, may be downloaded from:

RC Design

Typical input and output are shown in the screen-shots below:

Output of design moment capacity- axial load interaction diagram to AS 3600 with 40 MPa concrete:

The function has 3 options for the design concrete stress block: rectangular, bi-linear, and parabolic-linear.  For the non-rectangular stress blocks the strain limits and parabolic curve exponent are set to Eurocode 2 requirements.

For 32 MPa concrete to the latest AS 3600 code the rectangular stress block is conservative:

With 50MPa concrete the conservatism is increased:

… but with 80 MPa concrete the rectangular stress block is  significantly  unconservative for mid-range axial loads:

Using the Eurocode 2 partial load factors and rectangular block width factors the rectangular stress block is slightly unconservative for all concrete grades:

Comparing rectangular stress block results for AS 3600, Eurocode 2, and ACI 318, for 32 and 50 MPa concrete the AS 3600 results are significantly more conservative for intermediate axial loads, and the Eurocode results are less conservative above the balance load:

For 80 MPa concrete the AS 3600 and Eurocode 2 results are close up to the balance axial load, with the ACI results being significantly less conservative except for very  high axial loads:


Posted in Beam Bending, Concrete, Excel, Newton, UDFs, VBA | Tagged , , , , , , , , , , , , , | Leave a comment

ULS Design Functions – update for AS 3600

The ULS Design Functions spreadsheet (last posted here) has been updated to the new Australian Standard for concrete structures; AS 3600-2018.  The new version can be downloaded from:

ULS Design

Note that the biaxial version included in the download zip file is not yet updated for the new code.

The interaction diagrams below compare the new code with results from the previous version, which generates the same results as the 2017 version of the bridge code (AS 5100):

With 32 MPa concrete the new code generates higher results where bending controls (because the reduction factor is increased from 0.8 to 0.85), but where compression controls the capacity is reduced, if the reduction factor of 0.6 is applied.

Under some conditions the new code allows the reduction factor for compression to be increased to 0.65.  The higher reduction factor is applied in conjunction with the 100 MPa concrete in the screen shot below (click image for full size view):

In this case the new code gives significantly higher results over the full range of axial loads:

The new version also has a number of other recent updates as shown below:

Posted in Beam Bending, Concrete, Excel, Newton, UDFs, VBA | Tagged , , , , , , , | 2 Comments

Working with dynamic arrays in Excel

Many of the VBA functions available here return results as arrays, which must be entered by selecting the desired output range, then  pressing Ctrl-Shift-Enter (CSE).  This can be inconvenient (or at least untidy) when the size of the output array varies.  Some solutions to this, using VBA and/or Python were presented  here.  Microsoft have also been working on this, and for members of the Windows Insider  Program, dynamic arrays have been available for some time now.  Dynamic array formulas are entered as standard single cell formulas, but automatically resize to display the entire array (or “SPILL” if the output range contains any existing data).

The new dynamic arrays (currently only available in the Excel Insider version) have potential problems when spreadsheets created in an Insider version are opened in earlier versions.  Charles Williams at Fast Excel has been investigating these problems, and has recently published a detailed review of potential problems, including a free CheckDA tool that allows you to check that the workbook you create using Excel DA will not cause problems when opened in prior Excel versions.


Posted in Arrays, Excel, UDFs, VBA | Tagged , , , , , | Leave a comment

The Barrow Poets

The Barrow Poets  (according to this site) started out selling poems from barrows in the late 1950s, then moved into performing in pubs.  I saw them performing in London, probably in 1972.  In spite of their undoubted originality and creativity, it seems they are not worthy of a place on Wikipedia, but here is one person’s  version of their story, followed by some samples of their musical poetry  (or is it poetic music?):

I first encountered the fabulous Gerard Benson in the very early 1970s when the Barrow Poets played in a scrubby basement in the Sir Christopher Wren pub in the old Paternoster Square, by St Paul’s Cathedral in London, when I was barely old enough to buy a (legal) drink. While other young things were into Genesis or King Crimson, I was gripped by their spectrum of poetry and music, from their own compositions to Purcell, Byrd, Blake, Keats, Stevie Smith and lots of Anon.

With the endlessly energetic Gerard, small and roundish, reciting, singing and playing kazoo and saw, the visually contrasting William Bealby-Wright, tall and thin and slightly lugubrious, on the homemade cacofiddle – once described in the Guardian as “a kind of DIY, cymbal-augmented double bass, seemingly built by the Clangers” – and the other wonderful musicians and poets, they were electrifying. Later they played in grand venues such as the Royal Festival Hall, but nothing could match the immediacy of the basement bar…

More at ‘Gerard Benson and the Barrow Poets were electrifying’

Posted in Bach | Tagged , , , , | Leave a comment

How Reinforced Earth works

“Reinforced Earth” is the name of  a construction system widely used in retaining walls around the World.  Here is a short video demonstrating how the system works, which is both informative and entertaining:

Posted in Newton | Tagged , , , | 1 Comment