Update to AL-Spline-Matrix

Posted on 28/11/2017 by dougaj4

The AL-Spline-Matrix spreadsheet contains a large number of VBA user defined functions (UDFs) performing spline fitting and matrix operations, using the free and open source ALGLIB library. It was recently pointed out that the Excel interface code for the CSplineFitCW1da function had an error. The function fits a cubic spline to scattered data, with provision for weighting and specified constraints, but the code error resulted in the constraints not working properly. The code has now been fixed, and an example with constrained data is shown below.

The revised spreadsheet, including full open source code, (Version 1.33; 27 Nov 2017) can be downloaded from:

AL-Spline-Matrix03.zip (Excel 2003 version)

AL-Spline-Matrix07.zip (Excel 2007 and later)

The screen-shot below shows results with constraints applied at the minimum and maximum x-values, compared with unconstrained results, and the Hermite Fit function:

Posted in AlgLib, Excel, Maths, Newton, UDFs, VBA | Tagged , , , , , , | 1 Comment

Composite Beam Spreadsheet Update 1.01

Posted on 04/11/2017 by dougaj4

Following a comment here, I have modified the Composite Beam spreadsheet to correct the stage 1 stresses forces in the reinforcement as listed in Columns 6 and 7, when the tensile face is the top.  The output has also been modified to list from top face to bottom (as for the other output), rather than compressive to tension face.  The updated spreadsheet can be downloaded from:

Composite Design Functions

The version posted recently at Stress increments in prestressed concrete beams has also been updated, and can be downloaded from:

Composite Design Functions-Stress Inc

 

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

Tables and VBA

Posted on 25/10/2017 by dougaj4

I don’t use Excel Tables much, and not at all from VBA (where they are known as ListObjects), but that should probably change, so here are four links with comprehensive help on using Tables from VBA:

A fifth link added 14th Jan 2018:

Posted in Arrays, Excel, VBA | Tagged , , , , | 5 Comments

A Hard Rain

Posted on 21/10/2017 by dougaj4

I happened to be looking at an old post here; Various routes to and from Nottamun Town, featuring Bob Dylan’s performance of A Hard Rain’s Gonna Fall, which led me to Patti Smith’s performance of the same song at last year’s Nobel Prize ceremony, and this is a unique performance in very many ways:

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

Using Flash Fill

Posted on 14/10/2017 by dougaj4

There have been several posts here on using functions or user defined functions (UDFs) to extract text or numbers from a longer string (most recently, here), but for many purposes it is quicker and easier to use the “flash-fill” feature, introduced in Excel 2013.

The examples below show examples of extracting a number from the end of a text string, using data copied and pasted from Strand7 as an example:

In the first example it is required to extract the numbers from the text in column B.  Simply enter the first number in the adjacent column, then press Ctrl-E (or click Data-Flash Fill):

The column is immediately filled with the required values.  A small icon appears next to the top cell, which gives the options displayed above if selected.

It is also possible to use a non-adjacent column:

Note that in this case the values extracted are not in ascending order.

More complex examples also work:

In this case the values in rows 14 to 17 (5-1 to 5-4) have been extracted as a date.  This can be fixed by simply formatting the example cell as text:

Also if the example cell is changed immediately, the flash-fill automatically updates:

There are however times when more control is required.  The screen-shot below shows results from flash-fill (in column A), compared with the ExtractNums UDF in Column D (download here).

Posted in Excel, UDFs, VBA | Tagged , , , | 1 Comment