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

Solving cubic and quartic equations with Excel

Posted on 04/08/2010 by dougaj4

Although in earlier posts (such as this one) I have referred to some User Defined Functions (UDFs)  for solving cubic and quartic equations, I just realised recently that I haven’t actually talked about them here, and since they are in most cases the most practical way of dealing with these equations, that ought to be fixed.

An “on sheet” solution to quadratic, cubic and quartic equations can be found in the spreadsheet Polynom.xls by Alex Tomanovich, which can be downloaded from the ExcelCalcs site.

Polynom.xls Cubic solution

The solutions in polynom.xls provide an excellent explanation of the method of solution, but are not very flexible or practical for solving a large number of equations.  For that purpose I have written four UDFs:

  • Quartic
  • CubicC
  • Cubic
  • Quadratic

Each function, other than Cubic, returns the real and complex roots of a polynomial equation with coefficients specified in either a single column range or a single row range.  Cubic is for use when only the real roots are required, and is a little faster than CubicC.  The functions may either return a single specified root of the equation, or if entered as an array function, will provide all roots (including real and imaginary parts for complex roots) and the number of real and complex roots.  The spreadsheet may be downloaded from Polynomial.zip (including full open source code).  The screenshot below shows input and output:

Quartic.xls input and output, click for full size view

The cubic and quartic functions are based on Fortran code from The homepage of AK Kraska.

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