Update to AL-Spline-Matrix

Has the free software at this site saved you time and/or money?
If so, please contribute to the Raw Impact charity group providing practical help to poor communities in Cambodia:

 Everydayhero – Cambodia 2018

  • All contributions gratefully accepted.
  • Contributions are tax-deductible for Australian tax payers

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 , , , , , , | Leave a comment

Raw Impact in Cambodia – Please Donate

Raw Impact is a charity group providing practical help to poor communities in Cambodia:

Sustainable and innovative projects with a hand-up model, focusing on simple yet powerful ways to change the game for the poorest of the poor by equipping and inspiring them forward.

In January next year I will be joining a Northridge Vineyard Church group helping to construct a Raw Impact project in Cambodia.

Please help Raw Impact to continue and expand their work with a charity donation through:

Everydayhero – Cambodia 2018 – Please Donate

All donations are welcome, and are tax deductible for Australian tax payers.

Posted in Bach, Newton | Tagged , , , | 1 Comment

Composite Beam Spreadsheet Update 1.01

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 , , , , , , , | Leave a comment

Tables and VBA

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

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

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

Hofstadter, Pahud, Bach

The latest ACO concert series features the Swiss flautist Emmanuel Pahud (interview here). The performance included J.S. Bach’s 6 Part Ricercar, from The Musical Offering, which was the inspiration for the final dialogue in Douglas Hofstadter’s Godel, Esher, Bach:

Achilles has brought his cello to the Crab’s residence, to engage in an evening of chamber music with the Crab and Tortoise. He has been shown into the music room by his host the Crab, who is momentarily absent, having gone to meet their mutual  friend the Tortoise at the door. The room is filled with all sorts of electronic equipment-phonographs in various states of array and disarray, television screens attached to typewriters, and other quite improbable-looking pieces of apparatus.  Nestled amongst all this high-powered gadgetry sits a humble radio. Since the radio is the only thing in the room which Achilles knows how to use, he walks over to it, and, a little furtively, flicks the dial and finds he has tuned into a panel discussion by six learned scholars on free will and determinism. He listens briefly and then, a little scornfully, flicks it off. …

On the text:

This Dialogue is an exuberant game played with many of the ideas which have permeated the book. It is a reenactment of the story of the Musical Offering, which began the book; it is simultaneously a “translation” into words of the most complex piece in the Musical Offering: the Six-Part Ricercar. This duality imbues the Dialogue with more levels of meaning than any other in the book. Frederick the Great is replaced by the Crab, pianos by computers, and so on. Many surprises arise. The Dialogue’s content concerns problems of mind, consciousness, free will, Artificial Intelligence, the Turing test, and so forth, which have been introduced earlier.  It concludes with an implicit reference to the beginning of the book, thus making the book into one big self-referential loop, symbolizing at once Bach’s music, Escher’s drawings, and Gödel’s Theorem.

From: Gödel, Escher, Bach Wiki 

And the music:


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