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

Daily Download 13: The ALGLIB maths library and Excel

Posted on 30/09/2012 by dougaj4

Today we move onto maths related downloads, starting with the ALGLIB maths library, which has featured here quite frequently:

Original post with link to site: ALGLIB

Installing AlgLib with Excel VBA
Download files: http://interactiveds.com.au/software/AL-Matrix-xlsb.zip   http://interactiveds.com.au/software/AL-Matrix-xls.zip

Linking AlgLib C++ to Excel VBA
Download file: http://interactiveds.com.au/software/AL-Matrixdll.zip

Compiled AlgLib Matrix Functions for Excel
Download file: http://interactiveds.com.au/software/AL-Matrixdll2.zip

ALGLIB Statistics Functions
Download file: http://interactiveds.com.au/software/AL-Stats.zip

AlgLib Complex Number Functions
Download file: http://interactiveds.com.au/software/AL-Complex.xls

AlgLib Spline Functions
2D Spline Interpolation with ALGLIB
Download files: http://interactiveds.com.au/software/AL-Spline-Matrix07.zip  http://interactiveds.com.au/software/AL-Spline-Matrix03.zip

Note that all the spreadsheets listed above link to Ver 2.6 of the ALGLIB library, which is the latest release in VBA.  At some stage I will link to later versions, which are available in C++, C#, VB.Net, and various flavours of Python, but the currently available VBA library remains a valuable resource, greatly extending the maths functionality available directly from Excel, and offering far better performance for maths intensive operations such as matrix functions.

Update Dec 2013:

Functions linking to the C# version of Alglib can be found at Compiled Matrix Functions for Excel; download file: http://www.interactiveds.com.au/software/Matrixdll-c%23.zip

For those wishing to use any of the ALGLIB routines in commercial applications, please read and observe the Licence Agreement

Matrix Functions

Matrix Functions

Statistics Functions

2D Spline Functions

Posted in AlgLib, Arrays, Excel, Maths, Newton, Numerical integration, UDFs, VBA | Tagged , , , , , , , | 7 Comments