Frame analysis with spring releases; 2D

Posted on 25/11/2016 by dougaj4

I have updated the Frame4 spreadsheet to allow a rotational spring stiffness to be specified for members with a rotational end release.  The new file can be downloaded from:

Frame4.zip

See Installing C# dll files, reminder for details of installing the supplied dll files, which provide much better performance for large frames. If these are not installed the spreadsheet will default to the built in VBA solver.

The previous version allowed insertion of beam end releases with zero rotational stiffness, which resulted in additional nodes being inserted in the frame model.  This allowed the addition of springs to the model, in effect inserting additional zero length spring members, connecting the rotational freedom at released ends to the rest of the model, by inserting the following values into the frame stiffness matrix:

frame4-8-1

using the following code:

'Add hinge stiffness factors
        k = BeamA(i, 5)
        If k > 0 Then
            For j = 1 To 2
                HingeK = BeamA(i, j + 2)
                If HingeK > 0 Then
                    gj = BeamER(k, (j - 1) * 2 + 8)
                    gk = BeamER(k, (j - 1) * 2 + 9)
                    KGCA(gj, gj) = KGCA(gj, gj) + HingeK
                    KGCA(gk, gk) = KGCA(gk, gk) + HingeK
                    KGCA(gj, gk) = KGCA(gj, gk) - HingeK
                    KGCA(gk, gj) = KGCA(gk, gj) - HingeK
                End If
            Next j
        End If

The screenshot below shows the input for the spring restraints:

frame4-8-2

I have compared results from the spreadsheet with those from the FEA program Strand7, using the model shown below:

frame4-8-6

The screenshot below shows near exact agreement for both beam end actions and deflections:

frame4-8-3

The spreadsheet also allows a lot of the frame deflected shape, and plots of arch actions and/or deflections along any slected beams.

Strand7 graphical output:

frame4-8-7

Spredsheet deflected shape plot:

frame4-8-4

Spreadsheet bending moment plots:

frame4-8-5

 

Posted in Beam Bending, Excel, Finite Element Analysis, Frame Analysis, Link to dll, Newton, Strand7, VBA | Tagged , , , , | 1 Comment

Can you spare a dime

Posted on 23/11/2016 by dougaj4

I enjoyed this rendition of a classic song by Martin Simpson:

I also enjoyed this comment:

daniel gorney1 year ago
Bravo. A very fine rendition. My daddy, Jay Gorney, who wrote the music, would have enjoyed it as much as I did. Keep on keeping on. A hell of a song. Daniel Gorney

They used to tell me I was building a dream
And so I followed the mob
When there was earth to plow or guns to bear
I was always there right on the job

They used to tell me I was building a dream
With peace and glory ahead
Why should I be standing in line
Just waiting for bread

Once I built a railroad, I made it run
Made it race against time
Once I built a railroad, now it’s done
Brother, can you spare a dime

Once I built a tower up to the sun
Brick and rivet and lime
Once I built a tower, now it’s done
Brother, can you spare a dime?

Once in khaki suits, gee we looked swell
Full of that Yankee-Doodly-dum
Half a million boots went slogging through Hell
And I was the kid with the drum

Say, don’t you remember, they called me “Al”
It was “Al” all the time
Why don’t you remember, I’m your pal
Say buddy, can you spare a dime

Once in khaki suits, ah gee we looked swell
Full of that Yankee-Doodly-dum
Half a million boots went slogging through Hell
And I was the kid with the drum

Oh, say, don’t you remember, they called me “Al”
It was “Al” all the time
Say, don’t you remember, I’m your pal
Buddy, can you spare a dime

Songwriters: E. Y. HARBURG, JAY GORNEY
Posted in Bach | Tagged , | Leave a comment

More on Lookups

Posted on 22/11/2016 by dougaj4

Following my recent post on nearest lookups, I came across a post from Charles Williams on the opposite problem; how to do an exact VLookup  on a large amount of data without taking all day, but still retaining the ability to return an error message if there was no exact  match.  The link is well worth a look:

VLookup tricks: why 2 VLOOKUPS are better than 1 VLOOKUP

Posted in Excel | Tagged , , | Leave a comment

Installing C# dll files, reminder

Posted on 04/11/2016 by dougaj4

Installing the Frame4 spreadsheet on a new computer I had some problems registering the dll files required for the compiled solver routines, so here is a reminder of the procedure:

  • Copy the alglibnet2.dll and ALMatrixLib.dll files to the appropriate folder (see table below)
  • Run the correct version of the .Net regasm program, which generates a file “ALMatrixLib.tlb”:
    regasm /codebase C:\Windows\System32\ALMatrixLib.dll /tlb
  • Create a reference to the tlb file

There are detailed variations in this procedure, depending on whether the computer is running 32 or 64 bit Windows and Excel, and these are summarised below:

clib3-1

Having registered ALMatrixLib.dll it may be necessary to link to it from the Visual Basic Editor, Tools-References menu:

clib3-2

Note that the registered files appear in the System32 folder, even when they were copied to SysWOW32.

For more details see: Using Regasm.exe and Installing dlls on 64 bit Windows for more details.

Posted in Excel, Frame Analysis, Link to dll, VBA | Tagged , , | 5 Comments

Nearest Lookup Function

Posted on 21/10/2016 by dougaj4

The Excel Lookup functions (including VLookup, HLookup and Match) all allow for an “exact” or “closest” match on numerical data, but the closest option has a number of problems:

  • The data must be sorted
  • For VLookup and HLookup the data must be sorted in ascending order, but if it isn’t the function may return an incorrect result, rather than #N/A.
  • For Match the data may be sorted in either ascending or descending order, but if the actual order is either unsorted or different to that indicated the function may return an incorrect result.
  • The terminology used for the argument defining the match type is non-intuitive, and inconsistent between the Lookup functions and the Match function.
  • The default option (ascending sort) may produce incorrect results, whereas the option for an exact match will always return either a valid result or #N/A.
  • With ascending sorted data the functions will return the last value less than the lookup value, rather than the closest match.
  • With descending sorted data the Match function (with Match Type = -1) will return the last value greater than the match value, rather than the closest match.
  • The match will only look at data in a single column.  There is no built-in function to return the closest point in 2D, 3D, or higher dimension space.

Some of these problems may be avoided by using the Round function on the lookup value, then doing an “exact” lookup, but this can also return misleading results in some circumstances, and does not handle multi-dimensional data.

To deal with all these problems I have written a Nearest() user defined function (UDF) that works on unsorted numerical data with any number of dimensions, and will return:

  • The coordinates of the nearest matching point
  • The row number of the nearest matching point
  • The distance from the lookup point to the matching point

Optionally a “maximum error” distance may be specified, and the function will return “No match” if there is no point within this distance.

A second UDF, Dist(), returns the distance between any two multi-dimensional points.

The spreadsheet, including full open-source code, may be downloaded from: Nearest.xlsb

The screen shots below illustrate the problems with the Lookup functions, and use of the Nearest UDF.

With sorted data and equally spaced data Vlookup returns the highest value less than the lookup value, rather than the nearest match.  Using the Round function on the lookup value in this case returns the correct results:

nearest1-1

If the data values are not equally spaced VLookup on the rounded number no longer returns the correct result:
nearest1-2

With an unsorted list VLookup returns #N/A when the lookup value is less than the first data value, but if it is greater it returns the value before the first data value greater than the lookup value:
nearest1-3

Setting the VLookup “Range_lookup” value to FALSE (i.e. an exact lookup), returns #N/A in all cases in this example, because none of the lookup values have an exact match in the data.  Rounding the lookup value to an integer returns a match in all cases in this example, but not always the closest match.  The Match/Index combination with “Match_type” set to -1 (descending sorted list) returns a value when the lookup vale is less than the first value in the lookup data, but this is not necessarily the closest match.
nearest1-4

With an array of 2D (or more) coordinates the Nearest UDF returns the coordinates that are closest to the lookup points.  The function will also return the row number of the matching coordinates or the distance from the lookup point to the nearest mach.

The lookup can also be carried out with the Index and Match functions:

  • Generate a list of distances from the lookup point to each of the data points.
  • Find the smallest distance with the Min function
  • Use Match with the exact option (Match_type = 0) to find the row number
  • Use Index to find the coordinates of his point>

Note that using the Nearest UDF all these steps are incorporated in the UDF, and no additional calculation is required:
nearest1-5

The Nearest UDF has a MaxErr option that requires matching data to be within a specified distance of the lookup point. Reducing this value to 0.05 with the example data returns “No match” because the closest data point is 0.054 from the lookup point:
nearest1-6

Posted in Coordinate Geometry, Excel, Maths, UDFs, VBA | Tagged , , , , , , , , | 2 Comments