I have recently corrected the CHSplineA function included in the CSpline2 spreadsheet. The earlier version was returning incorrect values for the slope and curvature of the spline (with output option 2), if the spacing of the points defining the spline was anything other than 1.
I installed Office 2010 a couple of weeks ago, and I have just succeeded in getting the help system to work. After pressing F1, or clicking the Help icon, I was getting a screen saying that the help system was being installed, then eventually (after a couple of minutes maybe) a box saying that the computer needed to be re-booted (yes/no) and a blank help screen in the background. After re-booting and restarting Office, the whole installation process started again, and if interrupted showed a screen saying there was no help available on this topic. After much searching, and eventually e-mails to Microsoft support, this is the process that fixed the problem:
Having gone through all that I find that Microsoft in their wisdom have made on-line help the default, so we get an annoying wait for help (even with a fast cable connection), rather than the instant help we used to get 20 years ago, on machines about 1000 times slower. Fortunately I remembered a post from Ron de Bruin at Daily Dose of Excel on this very issue. Click on the box in the bottom right hand corner, showing
“Connected to Office.com” and you can select “Show content only on this computer”, which then becomes the default. If you really want the on-line help you can click on the same box any time, and select it.
Just what was going on in the mind of whoever decided to make on-line help the default, and why they hid the local option away instead of putting it under the help menu, I can’t imagine, but never mind, it’s easily fixed thanks to Ron de Bruin.
So now I’m back to being as productive as I was 3 weeks ago.
A previous post on laterally loaded piles used a finite difference analysis to analyse the deflections and forces in a vertical pile subject to a lateral load at the top. An alternative approach to the same problem is presented in “Programming the Finite Element Method” by I.M. Smith and D.V. Griffiths, including Fortran code. The main advantage of their approach is that the pile and soil stiffness values are treated as varying linearly between each node, rather than being constant over each segment. Also the code provides for up to 6 integration points in each segment, allowing a smooth transition between elements with different properties.
I have converted the Fortran code to VBA, and incorporated it into the LatPile function presented previously. The spreadsheet also includes a new function, BoEFA, with additional flexibility for specifying node constraints and variable segment lengths, and with sign conventions better suited to horizontal beams. The spreadsheet, including full open source code, may be downloaded from LatPileB.zip
Screenshots below show typical input and output, including comparison of results of the same analysis in Strand7 FEA software. Click on any image for a full size view.
LatPileB input and output
LatpileB Bending Moments
LatPileB Shear Forces
LatPileB Deflections
BoEFA Input and Output
BoEFA and Strand7 Deflections
BoEFA and Strand7 Bending Moments
BoEFA and Strand7 Shear Forces
Following a question at the AlgLib Forum I have added the Hermite spline fit function to the AL-Spline-Matrix collection.
The revised spreadsheet, with full open source code is available for download in 2007/20010 version and pre-2007 version.
Typical output of the Hermite spline fit function (together with the two previous spline fitting functions) is shown in the screen shot below:
HSplineFit Function; click for full size view
In my first go at modelling a ball falling through a hole through the middle of the Earth I assumed constant density, and hence the acceleration at any point was proportional to the distance from the centre. That is of course far from the truth, the Earth has large variations in density through its depth, so the first step in creating a more realistic model will be to estimate the actual variation in gravity with depth.
For the basic data I have used a table from a presentation produced by the Washington Centre:
Earth Density Data
I have used this data (assuming a constant density for each layer) to calculate the mass of the Earth in 50 km steps from the centre out. The acceleration due to gravity at any radius R from the centre is then given by:
a = Gm/R^2
where G is the gravitational constant and m is the mass of the sphere of radius R:
Calculation of the acceleration due to gravity through the Earth
The spreadsheet shown above can be downloaded from Earth Density.xls. The result of this calculation is shown in the graph below. Note that the acceleration is far from linear with depth, and initially increases through the crust layer, then initially declines through the mantle, but increases again as the outer core is approached, reaching a maximum of 10.3 m/s^2 at the surface of the outer core, then reducing rapidly through the outer core, then faster still through the very dense inner core:
Variation in gravity to the middle of the Earth
Newton Excel Bach, not (just) an Excel Blog 