Daily Download 18: Simultaneous Equations and Matrix Arithmetic Functions

Posted on 04/10/2012 by dougaj4

Today’s downloads collect several spreadsheets related to solution of simultaneous equations and matrix arithmetic, some of which were presented previously as part of the ALGLIB library (Daily Download 13).

Solving simultaneous equations described how to solve simultaneous equations using the Excel MInverse and MMult functions, and provided two VBA functions for solution of simultaneous equations.

In Solving simultaneous equations – Fortran dll the VBA function was converted to a compiled Fortran dll, offering much better performance, but see Distributing Silverfrost Fortran Applications for details of installation of the necessary run-time library, and note that this function may not work on Windows 7 systems*.
Download file: http://interactiveds.com.au/software/Simultaneous.zip

* Edit 8th Oct 2012 – “Windows Vista” changed to “Windows 7”; see also comment from cjberg at https://newtonexcelbach.wordpress.com/2008/12/01/linking-excel-to-fortran/#comment-4622

ALGLIB VBA matrix arithmetic functions were described in Installing AlgLib with Excel VBA, and c++ compiled versions in Compiled AlgLib Matrix Functions for Excel.  The functionality was further improved with a C# compiled version of the latest version of the library, including support for sparse matrices; see Compiled Matrix Functions for Excel.
Download files: http://interactiveds.com.au/software/AL-Matrix-xlsb.zip
http://interactiveds.com.au/software/AL-Matrix-xls.zip
http://interactiveds.com.au/software/AL-Matrixdll2.zip
http://www.interactiveds.com.au/software/Matrixdll-c%23.zip (latest version)
See also http://interactiveds.com.au/software/AL-Spline-Matrix03.zip and
http://interactiveds.com.au/software/AL-Spline-Matrix07.zip which also include the VBA matrix functions.

Solution of linear equations with compiled ALGLIB function

Posted in AlgLib, Excel, Link to dll, Maths, Newton, UDFs, VBA | Tagged , , , , , , , | Leave a comment

Daily Download 17: Numerical Integration

Posted on 03/10/2012 by dougaj4

Some simple numerical integration functions (using the trapezoidal rule and Simpson’s Rule) were included in the Eval2 spreadsheet, presented on Daily Download Day 15, but for purposes were better accuracy and/or higher speed are required more sophisticated methods will do a much better job.

The spreadsheet Eval-Integration.xlsb combines the Eval function with a variety of high performance integration functions, including functions from the ALGLIB library, and the Tanh_Sinh function written by Graeme Dennes (of which more below).
Download file: http://www.interactiveds.com.au/software/Eval-Integration.xlsb

The development of the integration routines started with Gaussian Quadrature, followed by Eval.xls and the ALGLIB integration functions.  Graeme Dennes’ Tanh-Sinh functions made their first appearance in Faster Integration with the Tanh-Sinh Method, and these have had several updates with a variety of functions for special cases, and extensive documentation.  Post on this method include:
Tanh-Sinh Quadrature update
Tanh-Sinh Quadrature Update 2
Tanh-Sinh Quadrature V2.0
Tanh-Sinh Quadrature V2.1
Tanh-Sinh Quadrature V3.0
and the latest version: Tanh-Sinh Quadrature V4.0

Download file: http://interactiveds.com.au/software/Tanh-Sinh and DE Quadrature by Graeme Dennes.zip

Tanh_Sinh Integration Function, with associated graph

Posted in AlgLib, Excel, Maths, Newton, Numerical integration, UDFs, VBA | Tagged , , , , , , , | Leave a comment

Daily Download 16: Intersections, interpolations, and rotations

Posted on 02/10/2012 by dougaj4

There is only one download file today:
http://interactiveds.com.au/software/IP2.ZIP

, but it contains a wealth of different functions dealing with 2D and 3D geometry, including intersections of lines, circles and spheres, simple interpolation functions, and functions for rotating points in 2D and 3D.

The original spreadsheet is described at: Intersections, interpolations, and rotations with updates at IP.xls updated and Another update to IP.xls.

Version 2 included: Finding Circle Centres in 3D , with later updates including:

Intersection points of two polylines

Perpendicular distance from points to a polyline

ArcCen Function

PView: Perspective projection of 3D Lines, showing IPPlanePlane output

Output of PView function

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

Daily Download 15: Evaluation of formulas in text

Posted on 01/10/2012 by dougaj4

VBA has an Evaluate function that returns the numerical value of any text string representing a mathematical formula, but this is not directly available from the spreadsheet.  Today’s download is a User Defined Function (UDF) to provide this functionality, together with examples of its application.

The original function was described in Evaluate Function and   Evaluate Function – update, together with an example using the function to perform numerical integration.
Download file: http://www.interactiveds.com.au/software/Eval.zip

A later version was published in More on the Excel Evaluate function, together with details of how to use the built-in Evaluate function with named ranges, without using VBA.  See below for the download files.

Finally this year the function was modified to deal with values using a comma as a decimal separator in Return of Excel – Evaluating commas 
Download files:  http://interactiveds.com.au/software/Eval2.xls,  http://interactiveds.com.au/software/Eval-Integration.xlsb

There is also a “unit aware” version of the Eval function, but this will be described in a few days when looking at unit conversion functions.

EvalInt Function

Eval function examples

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

Daily Download 14: Curve Fitting 1

Posted on 01/10/2012 by dougaj4

Today’s download topic covers fitting a straight line or curve to scattered data, using the “least squares” method.  Fitting a spline curve to pass exactly through a series of points will be covered in a few days.

Use of the LinEst function, and differences in the results compared with the chart based fitting function was described in : Chart Trend Lines and the Linest Function
Download file: http://interactiveds.com.au/software/LinEst%20Check.zip

The LinEst function can also be used to fit certain curves (such as polynomials, exponential, power and logarithmic functions) to a set of data, as described at: Using LINEST for non-linear curve fitting

This technique was extended for data with gaps in: Using LinEst() on data with gaps
Download file: http://www.interactiveds.com.au/software/Linest-poly.xls

Fitting high order polynomials can give unexpected results, as described at: Fitting high order polynomials.  The ALGLIB library was found to give much more stable results than using the LinEst function for polynomials of 5th order and higher.  VBA interface functions allowing the ALGLIB functions to be used from Excel are described at: ALGLIB linear and polynomial fitting functions.  These functions are included in the AL-Spline-Matrix download files below.

Non-linear regression using the Excel Solver was covered at: Non linear regression – 1 and using the ALGLIB Library at Non linear regression – 2; ALGLIB functions
Download files: http://interactiveds.com.au/software/NonLinFit-Solver.xls
http://interactiveds.com.au/software/AL-Spline-Matrix07.zip
http://interactiveds.com.au/software/AL-Spline-Matrix03.zip

Chart trend line and LinEst results

Using LinEst with polynomial curves

Regression for data with gaps

Fitting 7th order polynomial

ALGLIB Polynomial fitting functions

Non-linear regression with the Excel Solver

ALGLIB non-liner curve fitting output

Posted in AlgLib, Charts, Excel, Maths, Newton, UDFs, VBA | Tagged , , , , , , , | Leave a comment