Category Archives: Excel

Moving averages and User Defined Array Functions

Posted on 25/04/2009 by dougaj4

I have recently needed to work with moving averages on a large-ish data set (about 10,000 rows x 10 columns), and for reasons that I will describe in the next post, decided that a User Defined Function (UDF) would be … Continue reading →

Posted in Arrays, Excel, UDFs, VBA | Tagged , , , , | 8 Comments

More on Worksheetfunction vs UDF

Posted on 22/04/2009 by dougaj4

A recent post at Roy Maclean’s VBA Blog suggested that the difference between using a worksheetfunction call in VBA and writing a User Defined Function (UDF) to do the same job was not enough to be worth worrying about.  He tested … Continue reading →

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

A very un-useful “feature”

Posted on 20/04/2009 by dougaj4

A recent post in the Engineering Spreadsheets forum at Eng-Tips draws attention to a potentially dangerous Excel “feature”. The feature is called “Extend data range formats and formulas” and can be found under Options-Advanced (click on the multi-coloured button in the … Continue reading →

Posted in Excel | Tagged , | 1 Comment

Evaluating Pi …

Posted on 12/04/2009 by dougaj4

… to 15 decimal places in one easy step (or not too difficult step). In my previous post one of the examples given of the use of the Gaussian Quadrature function was to evaluate the value of Pi.  Let’s look at … Continue reading →

Posted in Excel, Maths, Newton, UDFs, VBA | Tagged , , , , | 4 Comments

Gaussian Quadrature

Posted on 10/04/2009 by dougaj4

The previous post on integration provided an Excel User Defined Function (UDF) to perform numerical integration using Simpson’s Rule or the Trapezoidal Rule.  A third method is Gaussian Quadrature which is often much more accurate and/or quicker than Simpson’s Rule.  The UDF GaussInt() … Continue reading →

Posted in Excel, Maths, Newton, UDFs, VBA | Tagged , , , , , , | 27 Comments