The hole through the middle of the Earth – filled with air

Posted on 13/08/2010 by dougaj4

Three air molecules go into a hole,
 Well, I say three; could have been four or five.
 Could have been nine or ten, doesn’t matter.
Could have been fifteen, twenty – fifty.
Round it up. Hundred. Let’s go mad, eh – two-fifty.
…
Alright, make it a lot simpler, all the air molecules on the planet go into a hole, right?

Hang on – they wouldn’t fit would they?  What do you think? What proportion of the Earth’s atmosphere could we fit in a hole through the middle of the Earth.  The atmosphere, if it was compressed to sea level pressure occupies about 4 billion cubic km, and a hole through the Earth of 1 square metre cross section would occupy about 0.013 cubic km, so even allowing for a fair bit of extra compression, we aren’t going to get much of the atmosphere into the hole are we?  Let’s do the numbers.

Using the AlgLib ODE solver we can easily write a function to calculate the pressure at any distance from the Earth’s centre, starting at normal pressure (say 100 kPa) at the surface.  The ODE and the function code are shown in the screen-shot below. 

ODE for air pressure down a hole to the centre of the Earth

 To keep things simple I have made the following assumptions:

  1. Air acts as an ideal gas all the way down the hole
  2. Temperature changes are ignored
  3. The acceleration due to gravity varies linearly from the surface to zero at the centre

1 and 2 are obviously fairly gross approximations, but we only want an order of magnitude result, so let’s see how they work out.

To check out the ODE solver I have started off going from the surface up to an elevation of 10,000 m:

Air pressure variation to 10,000 m

This gives a result of 27.2 kPa, which compares pretty well with the actual pressure (extracted from the Internet) of 26 kPa.  Also the ODE function was in close agreement with a simple Runge-Kutta solution carried out on the spreadsheet (see screenshot).

The next step was to go downwards in 100 steps of approximately 64 km each.  Here we run into a problem.  The AlgLib function fails to converge over this distance.  The problem is that it has an iterative error minimisation routine, but very big numbers cause it to crash.  To get around this I have used the simple Runge-Kutta method on the spreadsheet, without any error minimisation.  Using this we find a pressure at the centre of about 4.3E148 kPa (43 followed by 147 zeros!):

Pressure to the Centre of the Earth

Here is a graph of the pressure against depth on a log vertical scale:

Pressure distribution to the centre of the Earth (log pressure scale)

The total mass of air in the hole would be 2.6E164 kg, which is composed of about 1.5E200 atoms.

It is difficult to comprehend just how big these numbers are, but some idea is given by considering that the total number of atoms in the observable universe is estimated to be of the order of 1E80.  We would need 1.5E120 Universes to fill the hole.  To look at it another way, if every atom in the observable universe was a universe of equal size itself, and if every atom in every one of those universes contained a mass of 200 billion tonnes, then that would provide enough perfect gas to fill the hole so that the pressure at the surface was just 100 kPa.

It is fortunate that no real gas behaves remotely like a perfect gas at high pressures!

In the next post in this series I will look at how much air we would need using real gas behaviour.  I just need to find a source of information for compressibility of air at very high pressures.

Posted in AlgLib, Differential Equations, Excel, Maths, Newton, UDFs, VBA | Tagged , , , , , , | 4 Comments

Three blokes go into a pub …

Posted on 13/08/2010 by dougaj4

“…  Well, I say three; could have been four or five.
 Could have been nine or ten, doesn’t matter.
Could have been fifteen, twenty – fifty.
Round it up. Hundred. Let’s go mad, eh – two-fifty.
Tell you what, double it up – five hundred.
Thousand!
Oh, I’ve gone mad! Two thousand!
Five thousand!
(adopting auctioneer persona)
anyone five thousand,
six thou,
six thousand,
ten thousand!
Small town in Hertfordshire goes into a pub!
Fifteen thousand blokes!
Alright, let’s go – population of Rotterdam.
The Hague.
Whole of Northern Holland.
Mainland U.K.
Let’s go all the way to the top – Europe, alright?
Whole of Europe goes –
I say Europe. Could be Eurasia.
Not the band, obviously, that’s just two of them.
Alright, continents – North America!
Plus South America!
Plus Antarctica – that’s just eight blokes in a weather station.
Not a good example.
Alright, make it a lot simpler, all the blokes on the planet go into the pub, right?

And the first bloke goes up to the bar and he says “I’ll get these in.”

What an idiot.”

Ref: Bailey, Bill; some stand-up gig somewhere probably, date unknown

Posted in Bach | Tagged , , , | 1 Comment

AlgLib Complex Number Functions

Posted on 11/08/2010 by dougaj4

The AlgLib module ap.bas includes complex number functions which can be called from other VBA routines, but they cannot be used as User Defined Functions (UDFs)  because they use the user defined data type “Complex”:

Public Type Complex
    X As Double
    Y As Double
End Type

To  call these functions from the spreadsheet an interface routine is required to convert the spreadsheet data into a complex data type, and for those functions that return a complex number, convert that back into a type that can be returned to the spreadsheet.  One option would be to use the Excel Complex() function on the spreadsheet and convert the resulting string into the Alglib Complex data type.  The use of strings to represent complex numbers seems to me to be cumbersome and inefficient, and for Excel versions before 2007 it requires the installation of the Analysis Tool Pack.  For these reasons I have chosen to use ranges of two adjacent cells to represent complex numbers.  The short routine below converts a single row, 2 cell, spreadsheet range (or a 1×2 array) into the AlgLib Complex data type:

Public Function Vara2Complex(ByVal Za As Variant) As Complex
Dim Z As Complex

If TypeName(Za) = "Range" Then Za = Za.Value2
Z.X = Za(1, 1)
Z.Y = Za(1, 2)

Vara2Complex = Z

End Function

All the modified routines have AL_ inserted in front of the AlgLib function name.

A similar routine could be used to carry out the reverse process, but because all the complex number routines are very short I have simply modified them to return a variant array rather than a complex data type.  An example is shown below:

Public Function AL_C_Mul(ByRef Z1a As Variant, ByRef Z2a As Variant) As Variant
    Dim Result(0 To 1) As Double
  Dim Z1 As Complex
  Dim Z2 As Complex

  Z1 = Vara2Complex(Z1a)
  Z2 = Vara2Complex(Z2a)
    Result(0) = Z1.X * Z2.X - Z1.Y * Z2.Y
    Result(1) = Z1.X * Z2.Y + Z1.Y * Z2.X

    AL_C_Mul = Result
End Function

A spreadsheet including Excel UDF versions of all the AlgLib complex number functions (including full open source code) can be downloaded here: Al-Complex.xls

Examples of each function (and the equivalent Excel function, where available) are shown in the screenshot below (click to view full size).

UDF versions of AlgLib complex number functions

Posted in AlgLib, Excel, Maths, Newton, UDFs, VBA | Tagged , , , , , | 2 Comments

Buckling of columns with varying cross section

Posted on 09/08/2010 by dougaj4

Following a post in the Eng-Tips Forum about finding the buckling load of a stepped strut I have modified the Frame4 spreadsheet to carry out a buckling analysis of any straight member subject to axial load, including stepped or tapered cross sections.  The spreadsheet, including full open source code, may be downloaded from: Frame4Buckle.zip

The buckling load is found using the method of Vianello, described in a paper from the University of Ljubljana  (Note 20 Nov 14: The link to the University no longer works, but the iterative procedure used is described in Theory of Elastic Stability by Timoshenko and Gere).  This is an iterative method using successive approximations of the deflected shape:

  • Assume an approximation of the deflected shape.  In the spreadsheet a linear approximation is used from the fixed point(s) to the point of maximum deflection, which is assumed to be at the top of the column if the top is unrestrained in the X direction, or at mid height if the top is restrained.
  • Find the bending moments due to a unit axial load applied at the top in a column with this shape .
  • Find the deflections of a column subject to this bending moment distribution.
  • Normalise the deflections based on the maximum absolute deflection.
  • Update the column shape using the normalised deflections, and repeat the analysis.
  • Repeat until the difference in maximum deflection between successive iterations is negligible.
  • The buckling load is the inverse of the maximum deflection from the last iteration (before normalisation).

The Frame4 spreadsheet has been modified to generate the required input data and carry out the analysis simply and quickly for a straight member with axial load applied at one end.  The input required is:

  • End conditions: either 1 (fixed) or 0 (free) for base rotation, top X deflection and top rotation.  X and Y deflection at the base must be fixed, and Y deflection at the top must be free.
  • Cross section details: for a member of constant cross section specify the cross section area, second moment of area, and E (Young’s Modulus), and member length and number of sections in the analysis.  For a varying cross section enter the section details for every cross section, with the height to the top of the section.
  • If the number of sections is entered in the constant cross section data the constant cross section is used.  If this cell is blank or zero the varying cross section data table is used.

Typical input and results are shown in the screen shot below:

Frame4Buckle Input and Output, click image for full size view

The buckling shape may be plotted to a chosen scaled deflection:

Buckled shape

Calculated deflections and normalised deflections are listed on the “Output” sheet:

Analysis Output Details

Posted in Beam Bending, Excel, Frame Analysis, Newton, VBA | Tagged , , , , , , , | 27 Comments

A musical interlude …

Posted on 07/08/2010 by dougaj4

… provided by my daughter’s latest music video with the excellent English band The Perishers:

Posted in Bach | Tagged , , | Leave a comment