Daily Download 24: Units for Excel

Posted on 11/10/2012 by dougaj4

The Units4Excel spreadsheet provides:

  • User Defined Functions (UDFs) to convert between any listed units, including compound SI units.
  • A UDF to evaluate any function entered as text, including evaluation of input and output units.
  • Recognition of all standard SI prefixes.
  • An extensive list of non-SI units, based on the Wikipedia unit conversion page.
  • The ability to add any other units to the list.

The download file is: http://www.interactiveds.com.au/software/Units4Excel.zip

The most recent posts with further details are:

Units for Excel 4: Scripting dictionaries
Units for Excel 5:

Function index

ConvertA Function, with alternative input options

EvalU Function

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

Daily Download 23: Trigonometric Functions in VBA and Fortran

Posted on 09/10/2012 by dougaj4

The trigonometric functions provided in Excel omit some functions, and the functions provided in VBA are still more restricted.  Notably VBA does not provide an ATan2 function, which is required to define an angle over a full 360 degree range, based on X, Y coordinates.

The download files below provide the full range of functions, including all inverse functions and hyperbolic functions.  The VBA version should work in all versions of Excel from 2000 up, but the Fortran version links to a dll that requires a 32 bit operating system, and does not work in 64 bit versions of Windows.

VBA version: http://interactiveds.com.au/software/TrigFuncsVBA.zip
Fortran version (32 bit only): http://interactiveds.com.au/software/Trig%20Funcs%20dll.zip

Further details of the files are given at Trigonometric Functions in VBA and Trigonometric Functions in VBA – Update for the VBA version and Trigonometric Functions – dll for the Fortran dll version.

Trig Function Examples

Hyperbolic Function Examples

Posted in Coordinate Geometry, Excel, Link to dll, Maths, Newton, UDFs, VBA | Tagged , , , , , | 8 Comments

Daily Download 22: Splines and Curves

Posted on 08/10/2012 by dougaj4

Since it was first published in 2009 my CSpline function has been the most popular download from this site.  CSpline (and various associated functions) is used to fit a series of cubic curves through specified points, allowing interpolation along a smooth curve.

The latest version of the spreadsheet is:
http://www.interactiveds.com.au/software/CSpline2.zip

The original function is described at Cubic Splines.  This was further developed in response to a reader query at Using Cubic Splines in Practice and Cubic Hermitic and Cardinal Splines, and most recently Spline Interpolation Alternatives provided some “on-sheet” methods with similar functionality.

Related spreadsheets were described in Daily Download 13: The ALGLIB maths library and Excel (covering the ALGLIB spline functions) and Daily Download 4: Continuous Beam Analysis (covering the use of cubic splines to model continuous beams, and the SplineBeam function).

The remaining curve generating spreadsheet is:
http://interactiveds.com.au/software/Catenary.zip

This includes a function to generate the coordinates of any specified catenary curve, with an option to find the tension force at any point.  For more details see A catenary function.

Fitting a cubic spline to five data points

Cubic Spline and alternative Hermite Splines

Interpolated and fitted curves with spikey data

Catenary UDF

Posted in AlgLib, Beam Bending, Charts, Charts, Coordinate Geometry, Excel, Newton, Numerical integration, UDFs, VBA | Tagged , , , , , , , , | 7 Comments

Daily Download 21: Assorted Solvers

Posted on 08/10/2012 by dougaj4

Today’s download starts with some simple linear interpolation methods used to solve polynomial equations, leading on to more sophisticated methods using quadratic or inverse quadratic interpolation. Also included is a solver for differential equations using the Cash-Karp Method, which is a refinement of the Runge-Kutta Method.

The current download files are:

Newton’s method:
http://interactiveds.com.au/software/Newtons%20Method.zip
Quadratic and Inverse Quadratic interpolation:
http://interactiveds.com.au/software/ItSolve%20Functions.xls
Brent’s Method
http://www.interactiveds.com.au/software/ItSolve.zip
Ordinary differential equation solver (Cash-Karp Method)
http://interactiveds.com.au/software/ODESolver.zip

More details are given in the following posts:

Approximate Solutions of Polynomial Equations
Function roots with the Inverse Quadratic Method
The Inverse Quadratic Method – 2
The Inverse Quadratic Method 3 – Brent’s Method
Using the AlgLib ODE (Runge-Kutta) Solver with Excel

Basis of the Newton-Raphson Method

Inverse Quadratic Method

Muller’s Method and Brent’s Method functions

Damped Vibration System differential equations

Damped vibration system solution

Buckling load of a column using ODE solver function

Posted in AlgLib, Beam Bending, Differential Equations, Excel, Maths, Newton, UDFs, VBA | Tagged , , , , , , , , , , | Leave a comment

Daily Download 20: Section Properties

Posted on 06/10/2012 by dougaj4

The download spreadsheets today provide functions to find section properties of shapes either defined by coordinates or selected from a list of shape types.  The section properties found are: area, first and second moments of area about the X and Y axes and about axes through the centroid (parallel to X and Y and principal axes), Ixyc and angle of the principal axes, position of the centroid, and in some cases the torsional constant.

The download files are:
Section properties function and in-cell charting: http://interactiveds.com.au/software/SecProps-array.zip

Section properties of defined shapes:
http://interactiveds.com.au/software/Section%20Properties03.zip
http://interactiveds.com.au/software/Section%20Properties07.zip

Section properties without VBA:
http://www.interactiveds.com.au/software/On-sheet%20SecProp.xls

Section Properties from vectors:
http://www.interactiveds.com.au/software/SecPropV.xlsb

Posts describing the functions in more detail are:

Section Properties from Coordinates
Section Properties UDF and UDF charting
Section Properties from Layers
Section Properties of Defined Shapes – Spreadsheet
Section Properties from Coordinates Without VBA
Area from Vectors
Section properties for groups of defined shapes

Section properties from coordianates and in-cell charting

Section properties without VBA

Section Properties from Vectors

Posted in Charts, Coordinate Geometry, Drawing, Excel, Maths, Newton, UDFs, VBA | Tagged , , , , | Leave a comment