Category Archives: UDFs

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

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

xlwSciPy 1.09 – update for xlwings 0.10 and Scipy 0.18.1

Posted on 02/10/2016 by dougaj4

The xlwSciPy spreadsheet (last presented here) has been updated for the latest version of xlwings and Scipy. The new spreadsheet can be downloaded from: xlScipy-xlw.zip including full open source code. The spreadsheet requires Python, including xlwings, Numpy, Scipy and Pandas … Continue reading →

Posted in Arrays, Curve fitting, Excel, Link to Python, Maths, NumPy and SciPy, Python Pandas, UDFs, VBA | Tagged , , , , , , , , | 2 Comments

VBA routines for splitting and joining text

Posted on 25/09/2016 by dougaj4

As mentioned in the previous post, I have written two short VBA routines to aid the process of splitting a column of text strings into separate columns, using either a space or any other chosen character as the delimiter.  These … Continue reading →

Posted in Excel, UDFs, VBA | Tagged , , , , , , , | Leave a comment

Non-linear Frame Analysis – Scipy solvers

Posted on 11/09/2016 by dougaj4

The frame analysis spreadsheet presented in the previous post has been updated to use the solvers included in the Scipy package.  There is now an option to use either the Cholesky solver, or an iterative sparse solver.  The main advantages of … Continue reading →

Posted in Arrays, Beam Bending, Excel, Finite Element Analysis, Fortran, Frame Analysis, Link to dll, Link to Python, Newton, NumPy and SciPy, Strand7, UDFs, VBA | Tagged , , , , , , , , , , , , , | 1 Comment

Non-linear Frame Analysis

Posted on 03/09/2016 by dougaj4

Another example using Fortran code published in Programming the Finite Element Method (5th ed. John Wiley & Sons, I.M. Smith, D.V. Griffiths and L. Margetts (2014)), this post provides a spreadsheet based frame analysis program including non-linear bending behaviour and … Continue reading →

Posted in Beam Bending, Excel, Finite Element Analysis, Fortran, Frame Analysis, Link to dll, Link to Python, Newton, NumPy and SciPy, Strand7, UDFs, VBA | Tagged , , , , , , , , , , | 5 Comments