Tag Archives: Excel

The Countif bug (and how to avoid it)

Posted on 22/12/2017 by dougaj4

The Countif function counts the number of cells in a range that match some criterion.  If we enter 1,2,3 in cells A1:A3 and enter =COUNTIF($A$1:$A$3,A1) somewhere else, it will return 1: But if we enter three text strings with 16 … Continue reading →

Posted in Computing - general, Excel | Tagged , , , , , | 2 Comments

Excel Uservoice and Python for Excel

Posted on 18/12/2017 by dougaj4

The Excel Uservoice site is a forum for users to post suggestions for improvements to Excel.  A suggestion to add native Python support to Excel, as a replacement to VBA, has had by far the most votes since the start, and … Continue reading →

Posted in Computing - general, Excel, Link to Python, VBA | Tagged , , , , | 1 Comment

Section Properties with MeshPY, including torsion and warping

Posted on 09/12/2017 by dougaj4

I recently discovered robbie van leeuwen’s structural engineering blog, which has an open-source set of Python routines for calculation of section properties, including torsion and warping constants.  The program uses the MeshPY Library for generating the finite element meshes required … Continue reading →

Posted in Beam Bending, Concrete, Excel, Finite Element Analysis, Frame Analysis, Link to Python, Maths, Newton, Numerical integration, NumPy and SciPy, Strand7, UDFs, VBA, xlwings | Tagged , , , , , , , , | 11 Comments

Brent’s Method; Update and Examples

Posted on 06/12/2017 by dougaj4

Brent’s Method is a refinement of Newton’s Method for the numerical solution of any equation with one variable.  The User Defined Function (UDF) QuadBrent was described in:  The Inverse Quadratic Method 3 – Brent’s Method Following a recent question, I have … Continue reading →

Posted in Arrays, Excel, Maths, Newton, UDFs, VBA | Tagged , , , , , , | 5 Comments

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 … Continue reading →

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