Update history
Current version of PopTools is 3.2 (build 5)  Download
Version 3.2 (build 5) Released September 22, 2011
 Bug fix: Last week’s release had included a reference to an ActiveX control that isn’t always installed and therefore caused installation problems on some computers.
Version 3.2 (build 4) Released September 18, 2010
 Bug fix: Likehihood profile routine failed on Excel 2010. This version should also work better on other Excel versions.
Version 3.2 (build 3) Released September 27, 2010
 Bug fix: Fixed problem with array version of formula that returns ~Normal integers (NormalIntA). It now returns a vector of integers instead of floating point numbers.
Version 3.2 (build 2) Released September 26, 2010
 Feature: FormulaText(Cell) now returns the address of Cell by default. Turn it off by calling FormulaText(Cell, FALSE).
Version 3.2 (build 1) Released September 7, 2010

Feature: Added a routine to label pathway diagrams and construct transition matrices from pathway diagrams. Available under “Matrix tools/label pathway diagram”.

Feature: Added a new demo file (“Pathway.xls”) to demonstrate the new functions.
Version 3.1 (build 8) Released August 30, 2010
 Feature: Added a routine to repeat the last Monte Carlo run. The save settings checkbox on the Monte Carlo dialog now saves the settings in the active workbook. A button to the right of the checkbox creates a button to repeat the last analysis using the saved settings, and a convenient button to open the Monte Carlo dialog. The button created to rerun the analysis invokes a procedure in PopTools called ‘LastMonteCarloRun’ which can also be invoked from your own VBA code.
Version 3.1 (build 7) Released Jun 20, 2010

Feature: SVDsolve now works for nonsquare matrices.

Feature: New function ProjectPB(matrix, vector, steps=1) simulates Poisson births and Binomial survival. Assumes first row represents births. Arguments are: matrix = population projection matrix, vector = state vector of population state at step 0, steps = number of steps to simulate. The return value is a vector of the population state after the given number of steps (default = 1).
Version 3.1 (build 6) Released Jun 9, 2010
 Bug fix: Fix error in stochastic sensitivity routine when scalar perturbation specified.
Version 3.1 (build 5) Released Jun 8, 2010

Feature: New routine for computing sensitivity of parameters in general stochastic models  available under Simulation tools/Sensitivity analysis. Demo file included  Sensitivity (stochastic).

Bug fix: Fixed routines that depend on Solver so that it works under Excel 2007.
Version 3.1 (build 0) Released Jul 20, 2009

Feature: New functions for power analysis (demonstrated in demo file noncentral.xls):

TNC(T, DF, DELTA)  computes the cumulative probability at T of the non central Tdistribution with DF degrees of freedom (which may be fractional) and noncentrality parameter DELTA.

BetaNC(PROB, X, A, B)  returns the noncentrality parameter given cumulative probability (PROB) of X for the noncentral beta distribution with parameters A and B.

BetaNCcdf(X, A, B, LAMBDA)  returns the CDF of the noncentral Beta distribution with parameters A and B, and noncentrality parameter LAMBDA.

QPois(P, LAMBDA)  returns the quantile of the Poisson distribution having probability P and parameter LAMBDA.


Feature: Function dPercentile introduced as alternative to Excel’s PERCENTILE function – which uses an unusal algorithm. The PopTools function use the algorithm recommended by NIST.

Feature: Allowed silent install for sysadmins (documentation).

Bug fix: Split the addin across more code modules to reduce chances of problems caused by large modules.
Version 3.0 (build 6) Released Sep 1, 2008

Feature: Improved speed of sampling Hypergeometric distribution.

Feature: Random Cauchy deviates  dCauchyDev(location, scale) and CauchyA (array version).

Feature: Random Pareto deviates  dParetoDev(a, b) and ParetoA (array version).

Bug fix: Removed persistent status bar notification after DLL loaded.
Version 3.0 (build 5) Released May 30, 2008

Bug/feature: MonteCarlo routine now allows more iterations (theoretically up to 2,147,483,647  a four byte integer).

Bug fix: Function wizard now lists all arguments to dPertDev (‘Weight’ argument was missing).

Bug/feature: Function to colour a graph series now includes an option to draw a black border on all points and has improved error checking.
Version 3.0 (build 4) Not released
Version 3.0 (build 3) Released Mar 3, 2008

Bug/feature: MonteCarlo routine now allows more iterations (theoretically up to 2,147,483,647  a four byte integer).

Bug fix: MonteCarlo demo available via menu system.

Bug fix: dNormalInt now returns an integer.

Feature: Main DLL now loaded from subdirectory.

Feature: Option to save distance matrices to file available from ‘Distance matrix’ dialog.

Bug fix: Dialogs now accept input in R1C1 style.
Version 3.0 (build 2) Released Feb 7, 2008

Bug fix: On Windows Vista, the original help file does not work. A comprehensive new help file been written to work on all windows platforms that support the CHM format (including Vista). It is available as a commercial product from this site.

Feature: A routine to generate a table of contents for large spreadsheets has been added to the ‘Auditing tools’ menu. It scans the names of all sheets in a workbook and adds them as hyperlinks in a contents sheet.

Feature: A function COMBINATIONS() that returns all combinations of a matrix elements has been added to the ‘Sampling’ menu. This is useful for working with the conditional probability tables of Bayesian Belief Networks using the software Netica.

Feature: Function BINOMCL(N, K, alpha, method=0) returns the upper and lower 1  alpha confidence limits of a binomial proportion (K successes from N draws) using the Adjusted Wald method (method=0) or, if method>0, using Jeffrey’s prior (Ref: Statistical Science 2001, Vol. 16, No. 2, 101?133).

Feature: Function SHAPIRO(data) returns the ShapiroWilks test for Normality of a data vector. The function is available from the menu ‘Extra stats’ and returns a two column by three row array containing the Wstatistic (ro1 1 col 1), the Pvalue (row 2 col1), and any errors encountered as an array formula.

Feature: A routine to retrieve appointments from the MS Outlook calendar was added to the ‘Auditing tools’ menu (useful for keeping track of time spent on projects).

Bug/feature: The PERT function  when entered via the menu system  has a default weight argument of 4, which should sample from the same distribution as @RISK.
Version 2.7 (build 5) Released Sep 25, 2006

Feature: Added custom function for sampling the Discrete(x, p) distribution. Here, x and p are column (or row) vectors in which p represents the probability (or frequency) of the value x. The function allows sampling of arbitrary, userdefined, discrete distributions. The function name is DiscreteDev and it can be entered via the RANDOM VARIABLES dialog. For an example, see the “Random variables (fast)” demo file.

Feature: MonteCarlo routine now allows setting of random seed at beginning of a run.

Bug fix: Bug in R code for fitting distributions fixed.
Version 2.7 (build 4) Released Sep 18, 2006

Feature/Bug fix: Removed function RANDFIX. Use of this function was unreliable because of the way Excel processes calculations. The random number generator can now be fixed only via the menu system (POPTOOLS/FIX RANDOM GENERATOR). The state of the generator can be queried inserting the function =RANDSTATE() in a worksheet. It is good practice to use something like this in a worksheet if you plan to fix the generator: =IF(RANDSTATE(),”Random generator fixed”, “Volatile random numbers”).

Bug fix: Generator of geometric deviates (dGeomDev) gave incorrect distribution. Fixed.

Feature: R scripts are now stored in a directory and loaded using the menu system. The program parses the directory when opened, so you can add your own scripts to this directory. By using similar headings to those in the example files, it is possible to ask PopTools to add in appropriate links to worksheet ranges when the script is loaded.

Feature: Hypergeometric random variables are now available via function dHyperDev(Samples,Affected,Population) with alias HyperDev and array version HyperDevA. Samples is the number of trials, Affected is the number of some type, and Population is the population size  all positive integers.
Version 2.7 (build 3) Released Sep 15, 2006

Feature: MonteCarlo routine remembers input settings.

Feature: New MonteCarlo demonstration file.

Feature: Web based demonstration files now available under ‘Demos’.
Version 2.7 (build 2) Released Sep 11, 2006

Feature/Bugfix: Function dBetaDev (and alias BetaDev) now returns a random deviate from the Beta(alpha, beta) distribution. This is a different parameterisation from previous releases, which used the mean and standard deviation as input parameters. The old functionality is still available as the function dBetaMSDev (and alias BetaMSDev). Use the dialog box (POPTOOLS/RANDOM VARIABLE) for guidance.

Feature: Utility for generating ~Discrete(x, p) deviates added to the form interface, POPTOOLS/RANDOM VARIABLE. This uses the existing MultinomialDev function to draw from the bins of the Discrete distribution.

Feature: Colour scale function now ignores cells containing error values.

Bug fix: This version installs on Excel 2007 beta release. The new version of Excel has a highly modified ‘menu/toolbar’ section and enhanced security features. Under Excel 2007 beta, this version of PopTools installs under the ‘AddIns’ tab. The PopTools setup file adds “C:\Program files\Poptools” (or equivalent) to the list of trusted locations, allowing the macros of the XLA (poptools.xla) to run. If you do not like this behaviour, it can be adjusted through the Excel interface after installation.

Feature: Array formulae for latin hypercube sampling from some distributions have been included in this version. The functions are not yet available via a dialog box, because I am yet to determine whether PopTools can properly implement orthogonal sampling for multidimensional problems with the current design. These functions provide limited functionality for teaching purposes. A demonstration file shows how they are called.
Function LHS(X, Y, length) returns a column of uniform random deviates on [X, Y] with the given length.
Function BinomialLHS(n, p, samples) returns a column of ~Binomial(n, p) random deviates of ‘samples’ length.
Function BetaLHS(alpha, beta, samples) returns a column of ~Beta(alpha, beta) random deviates of ‘samples’ length.
Function NormalLHS(mu, sigma, samples)) returns a column of ~Normal(mu, sigma) random deviates of ‘samples’ length.
Version 2.7 (build 1) Released Apr 22, 2006

Bug fix: PCA function used antiquated algorithm. This version uses SVD of the data matrix. Ordination demo includes routines to compare output with NIST tests and R statistical program.

Bug fix: Routines to standardise matrices (ColCentStand and RowCentStand) now compute sample standard deviation (ie, divide by n1) rather than population standard deviation (divide by n).
Version 2.6 (build 9) Released Oct 1, 2005

Bug fix: Fixed routine to return Gamma deviates with mean (or shape parameter) from 0 to 1, which previously used incorrect translation of Fortran source to Pascal.

Feature: Added function GammaDevMeans(Range) which returns an array of random deviates with each deviate drawn from a Gamma distribution with mean (shape parameter) equal to the value in the corresponding cell of Range.

Bug fix: Fixed error in function dNormDist which returned incorrectly scaled result for cdf.
Version 2.6 (build 8) Never released
Version 2.6 (build 7) Released Jun 22, 2005
 Bug fix: Fixed routine that returns life table statistics.
Version 2.6 (build 6) Released Feb 24, 2005

Bug fix: Fixed bug in calculation of eigenvectors introduced in version 2.6.4. Dependent calculations, such as matrix sensitivity, were also affected and have been fixed with this release. Included demonstration file ‘EigsTest.xls’ to test the output of the EIGS function.

Feature: Included routine to export a matrix to R and conduct analysis of properties (of projection matrices).
Version 2.6 (build 5) Never released
Version 2.6 (build 4) Released Dec 1, 2004

Feature: Improved routines for creating code to connect to R, including addition of a “Connect to worksheet” function.

Bug fix/Feature: Changed the SVD and dSVD functions so that the arrays returned (USV) are stacked on top of one another rather than side by side. The previous routine was limited by the allowed number of columns in an Excel worksheet (256). This new routine allows for up to 256 columns (and many more rows) on Excel XP and up.
Version 2.6 (build 3) Never released
Version 2.6 (build 2) Released Feb 25, 2004

New functions for data tables have been added under the “Auditing tools” menu. I find these useful for documenting data stored in Excel files: 1. “Comments to range”. Creates a new workbook containing comments from a selected range.

“Range to comments”. Takes cell values and places them as cell comments in a new range.

“Comments to HTML”. Produces an HTML file displaying cell comments of a selected range.

“Format table for printing”. If a worksheet has column headers in Row A:A and data below that you can use this function to quickly improve the appearance of the printed output of the data. Don’t use it unless the data is arranged as described._

“Denormalise a table”. Takes column headers from a data table and copies the headers into a column beside the data value. This is useful for exporting data for statistical programs. Be careful with dates which may not always be output in the correct format (to fix).

Bug fix: The “life table statistics” menu produced an error.
Version 2.6 (build 1) Never released
Version 2.5 (build 9) Released Nov 28, 2003

Bug fix: The “R connection” routine failed to insert a named range in the workbook. Fixed.

Bug fix: The “R dataframe” routine did not quote strings. Fixed.

Feature: The “R dataframe” routine now parses the column names and adds a comment if an illegal R name is found.
Version 2.5 (build 8) Released Oct 21, 2003
 Bug fix: Previous two versions returned incorrected eigenvectors. The bug was caused by incorrect handling of the interaction with the DMath library.
Version 2.5 (build 7) Released Oct 20, 2003

This version uses Jean DeBord’s DMath (Delphi math) library rather than TPMath (Pascal math). The major difference is that DMath has safer handling of memory allocation for matrices and vectors.

Several routines in previous versions created input boxes on the fly by creating worksheets and adding VBA code to their code modules. This added flexibility to PopTools but does not work in Excel XP. The current version has hard coded input forms and should be more reliable. Routines affected are PPS sampling, simple random sampling, odds ratio and relative risk.

The sensitivity analysis function for population projection matrices now has an option to calculate sensitivities for zerovalued elements. An option to set the new boolean argument to the function is provided in the interface.

An appropriate citation of the product for scientific papers has been added to the About box.

MonteCarlo routine now requires an output cell before starting the iteration.

Two routines added to make it easy to interact with the statistical program R (see http://cran.rproject.org/). Both can be found under the “Extra stats” menu.
Version 2.5 (build 6) Released Oct 20, 2003
 Available briefly. Withdrawn.
Version 2.5 (build 5) Released Apr 30, 2003
 Bug fix: Oops! Last version(s) failed to compile on Excel 97. Fixed.
Version 2.5 (build 4) Released Mar 19, 2003

Bug fix: dPCA function caused Excel to crash if columns > rows. Fixed

Bug fix: dChiTest returned incorrect results for some 2x2 tables. Fixed
Version 2.5 (build 3) Released Oct 10, 2002
 Bug fix: Last version(s) failed to compile on Excel 97. Fixed.
Version 2.5 (build 2) Released Oct 7, 2002

Bug fix: The routine that selected samples with probability proportional to size had an array indexing bug which led to repeated selection when “replacement” was false.

Bug fix: The Jacknife demonstration was returning #NAME errors because the DeleteElement function was failing.

New mirror site established.
Version 2.5 (build 1) Not released_

Bug fix: Fixed the routine that converts Excel range values into matrices for the TPMath library. It failed to clean up after itself if there was a dependency on invalid input, causing a memory leak.

Improved the numerical simulation routine. There is now an option to save a range of values that depend on the output range after each iteration.
Version 2.4 (build 9) Released July 1, 2002

Bug fix: Minor fixes to the routines for entry of random variables into a worksheet (the code that actually generates the variables is unchanged).

Several other fixes which I have been too busy to record.
Version 2.4 (build 8) Released May 16, 2002
 Bug fix: Version 2.4.1 and up have a bug whereby the maximum allowable array size is calculated incorrectly on Excel 97 and 2000. This version fixes that bug.
Version 2.4 (build 7) Never released.
Version 2.4 (build 6) Released May 7, 2002
 Bug fix: Previous release failed to fix problem in the numerical engine (RANDEVS.DLL). This version has been compiled with compiler optimisations turned off, which fixes the problem at the expense of a slight reduction in the speed of numerical calculations.
Version 2.4 (build 5) Released May 6, 2002

Bug fix: Previous release of the numerical engine (RANDEVS.DLL) had several problems on Windows 98, probably related to a compiler error. The current version of the DLL has been recompiled but no algorithms have changed.

Bug fix: Numerical projection routine failed to save the first step of the iteration. Fixed.

Bug fix: MULTINOMIALDEV function can now be entered from the menu system.
Version 2.4 (build 3 & 4) Never released.
Version 2.4 (build 2) Released April 3, 2002

Bug fix: The previous version (2.4.1) released last week does not handle large matrices. This release includes a version checking routine that should allow most routines to work with large matrices in Excel 2002 (XP) and up. The maximum array size allowed should now be 268369924 in Excel 2002, and 5461 in earlier versions of Excel.

All random variables are now generated using Delphi versions of Alan Miller’s Fortran routines. Source code for these is available at this site (Delphi) and Allan Miller’s site (Fortran 90). [Note Sep 2005: The Delphi routine for Gamma deviates from this site produces incorrect results when the shape parameter is less than one. This has been fixed in version 2.6.8]

Updated the dialog box for entry of random variables – now allows entry of the new multinomial deviates.
Version 2.4 (build 1) Released Mar 24, 2002

Epidemiological tools added to “Extra stats” menu  formulas for relative risk (dRR) and odds ratio (dOR) with confidence limits.

New routine for sampling with probability proportional to size (PPS) added to “Abundance” menu.

Added ability to generate multivariate Normal samples from a population with a specified covariance matrix to the “Random variable” menu.

Fixed bug in the “Schnabel” routine.

New array function MULTINOMIALDEV(NUMEVENTS, CELLPROBS) returns a sample from a multinomial distribution. Specify the total number of events as NUMEVENTS and the cell probabilities as a vector CELLPROBS (which should sum to one). The result is a vector of realised values. See the second sheet of the “Random variables (fast)” demo.

Enhanced “Monte Carlo” routine. If the “Test value” range has more than one column, each row of the “dependent cells” will be compared against each of the columns in the corresponding row of the “Test value” range, which is useful for power analysis.
Version 2.3 (build 7) Released Jan 14, 2002

Bug fix: New version of function to generate negative binomial deviates (previous version was unreliable with large values).

ODEIntegrate function now allows specification of a vector of time points at which a solution is required. See the updated ODE integrate demo sheet.
Version 2.3 (build 6) Never released except on bugs page.
Version 2.3 (build 5) Released Oct 8, 2001

Updated the eigenvector calculations to use a new version of the TPMath library.

Bug in drawing of life cycle diagram fixed (routine crashed when reproductive value calculated).

The main web site had to move because CSIRO Wildlife and Ecology merged with Tropical Agriculture to become CSIRO Sustainable Ecosystems. A redirect from the old site is in place.

Numerical projection routine now has option for multiple time iterations.

Sensitivity analysis recoded – now allows entry of arbititrarily sized matrix of parameters.

Added facility to define dynamic range names.

Added more documentation of worksheet functions to help file and this site.

New (permanent) email established poptools@csiro.au.

Bug fix: PCA on square matrices caused Excel crash  fixed.
Version 2.3 (build 1, 2, 3, 4) Never released except on bugs page.
Version 2.2 (build 8) Released July 2, 2001

Main change is an updated installation file that no longer requires VBA macros.

Bug fix. The routine to randomise worksheet ranges produces erroneous results if there are nonnumeric (or blank) cells in the upper rows of the selected matrix. This is because functions SHUFFLE and RESAMPLE expect non numeric data to be in the bottom rows of the matrix. This “feature” allows randomisation of different sized sets of numeric data  as shown in the demo of the two sample randomisation test (you can see further details on the resampling demo sheet). This release includes a check to ensure that all non numeric cells are in the bottom rows of the selected matrix.

Bug fix: Output of matrix projection routine had an extra row of error values. Fixed.

Bug fix: Removed links from some demonstration files.

Added routine to update links to the PopTools xla.

Added routine to delete unused range names from the active workbook (under Auditing tools)

Bug fix: Monte Carlo routine did not give correct results for tests of equality with floating point numbers. Fixed this bug by adding an option to set the precision of floating point comparisons.
Version 2.2 (build 5, 6, 7) Never released except on bug page.
Version 2.2 (build 4) Released May 28th 2001

Chi2 test now has Yates continuity correction for 2 x 2 tables.

Bug fix. Routine to colour a graph series was not working in previous release(s). Fixed in this version and added ability to colour points according to either font or interior colour of the data series.

Bug fix. Analysis of projection matrices now works for matrices larger than 18x18.

Bug fix. Mantel test calculated sum of products incorrectly. Fixed.

Bug fix. Minor adjustments to the auditing tools.

Bug fix. R_o was incorrect for Leslie matrices if entered as a single projection matrix and element 1,1 was nonzero.

New routines to analyse life tables and build projection matrices from lx and mx columns of a life table.

Added menu entry of matrix power function.

Demo added LIFETABLE.XLS. Some adjustments to other demos.

Bug fix. All forms that are initialised with numbers now use local decimal notation. Hard coded numbers prevented some routines running under French language settings.
Version 2.2 (build 3) Never released except on bug page.
Version 2.2 (build 2) Updated 18 April 2001

Last version introduced a bug in the SVD routine that crashed Excel when analysing nonsquare matrices. Fixed in this release.

Basic analysis of projection matrices improved

Minor changes to the Gtest and Chi square routines (more error checking).

Added some auditing tools. This was prompted by reading John Raffensperger’s page (at www.mang.canterbury.ac.nz/people/jfraffen/spreadsheets/). I don’t agree with everything at that page, but it’s an excellent guide. A new procedure “COLOUR USED CELLS” formats a worksheet to show used constants and formulas – which can be handy for pruning sheets that have gone a little feral.

Followed John’s advice and toned down the colours and rearranged the layout of some of the demonstration files (they’re still a little more colourful and illkempt than he would probably like :)
Version 2.2 (build 1) Updated 21 March 2001

Last version introduced a serious bug in the routine to return eigenvectors of real eigenvalues. This release fixes that bug, which caused errors in the analysis of matrix models. An updated version of the TPMath library has been released. PopTools now uses the new version.

Fitting functions from the TPMath library (SIMPLEX, Marquardt and BFGS) can now be called to minimise a standard spreadsheet. This provides a real alternative to Solver (accessed via menu system POPTOOLS/LIKELIHOOD TOOLS/OPTIMISATION).

SVD function now returns singular values in descending order  slower but much more useful.

Several array formulas added:

EYE(N) – returns an identity matrix of size N

COLOF(x, N) – returns a column of values x of size N

ROWOF(x, N) – returns a row of values x of size N

COLSUM(Matrix) – returns the column sums of Matrix as a row

ROWSUM(Matrix) – returns the row sums of Matrix as a column

dSTEP(Start,N,Increment) – returns a column of N values with initial value Start incremented by Increment at each step

DIAGONAL(Matrix) – returns a matrix with diagonal elements from Matrix and zeroes for offdiagonal elements. (See also function DIAG(Matrix) which returns a diagonal matrix using a row or column as input, and GETDIAG(Matrix) which extracts the diagonal of Matrix into a column vector).

MATPOW(Matrix, Exponent) – returns an integer power of Matrix_ by simple matrix multiplication.
All of these array formulas require the array entry technique (see this page for details). The utility of some of these functions for matrix models is illustrated in the first demonstration sheet: Eigenanalysis.xls.


Removed calculation of some generation times from the LIFE function (too hard to make these general without separate fecundity and survivorship matrices as input).

Added functions to support fitting with SIMPLEX, Marquardt and BFGS procedures.

TANFORM(Value, Minimum, Maximum) – Tan function to transform parameter values.

ARCTANFORM(Value, Minimum, Maximum) – Back transformation.

Version 2.1 (build 7) Updated 1 March 2001

Bug fix: The dialog boxes for entry of formulas in previous versions of PopTools did not not allow entry of output to a worksheet other than the input sheet. Apparently this behaviour caused Excel to freeze up on some computers. The current version allows entry on any worksheet (but not separate workbooks).

Bug fix: Dialog box for entry of PCA formula sometimes did not work. Fixed.

Solver functions did not work on some computers. This version should correct that problem, but let me know if it doesn’t :)
Version 2.1 (build 6) Updated 1 November 2000

Bug fix: Procedure to enter formula for dominant eigenvalue (finite rate of increase) did not work. Fixed. Note: I am aware of no bugs in the formula itself (DOMEIG).

New INSTALLATION.XLS prepared that no longer requires SOLVER.

Updated the setup files using new version of the setup compiler (Inno setup).
Version 2.1 (build 5) Updated 27 September 2000

Main change is that this version creates references to SOLVER on the fly. If SOLVER is not installed, the functions that do not depend on SOLVER will not work. This version should_ see the end of the “Compile error in hidden module …. “ messages :)

Bug fix: The PopTools menu bar disappeared when a chart was selected. This meant that the routines to colour or scale a graph series could not be used, since they require a graph series to be selected before they can be used. Fixed.

Bug fix: The new CA and PCA functions were limited to smaller data matrices than 5461 elements because the output could be too large to return to Excel. In this version, if the output is larger than 5461 elements, the results are copied to the clipboard and can then be pasted into a worksheet immediately after the function is calculated. The worksheet function return a message to indicate this.
Version 2.1 (build 4) Updated 19 September 2000

Bug fix: The functions to return eigenvectors (dEigVect and aliases) of general square matrices failed with some matrices of integers. The problem occurred because of pathological behaviour in the inverse iteration procedure used to obtain the eigenvectors. The current fix works on all matrices tested to date but a more robust algorithm is being developed for a future release.

New function SymPow calculates rational power of a symmetric matrix using spectral method. Useful for multivariate statistics.

Added menu entry of Jacobi function and SymPow

Updated some demonstration files.

The ACF function now returns approximate pacf in second column of the output.
Version 2.1 (build 3) Updated 14 September 2000
 Bug fix: TRACE function introduced in last version actually returned product of diagonal elements of a matrix rather than sum. Fixed.
Version 2.1 (build 2) Updated 12 September 2000
 Bug fix: Reference to VBE extensibility library somehow got back into the program. This has caused installation problems on some computers. Removed from this version.
Version 2.1 (build 1) Updated 12 September 2000
[Previous version should have been labelled 2.1 rather than 2.0
because it is an upgrade rather than a bug fix]

Bug fix: Monte Carlo routine did not restore calculation state. Fixed.

Added function =SetRan(PRNG). If PRNG = 1, the Marsaglia algorithm from the TPMath library is used for generation of pseudorandom numbers, otherwise default to Mersenne Twister algorithm. The argument PRNG sets a global variable, which is initialised to 0 (for Mersenne Twister) when the PopTools DLL is loaded. This function has been added to allow use of other PRNGs. Any suggestions for alternatives?
Version 2.0 (build 7) Updated 11 September 2000_

Added several new matrix formulas for centering and standardising data matrices.

Matrix utility functions added: TRACE, DETERM, UPTRIANG, IDENTITY.

Implemented the Mersenne Twister as the pseudorandom number generator.

Added a “Test” button to the dialog for entry of random variables. Clicking the button generates a file for testing the pseudorandom number generator using the DOS version of DIEHARD.

Improved ordination routines. Added correspondence analysis function (CA) and added extra arguments to the PCA function. See the new demonstration file ORDINATION.XLS installed by the setup file.

Added a progress metre that appears when some lengthy functions (eg MANTEL or SIMPLEX) are being calculated in the DLL. This should not slow processing significantly, and can be turned off or on using a new function =SHOWPROG(FALSE).

Calculation of Abar (in the function LIFE) is wrong. I have turned this feature off until I figure out what is going wrong.

Removed reference to VBE extensibility library. This has caused installation problems on some computers. An unfortunate side effect is that the worksheet “Map” function no longer adds hyperlinks to the mapped sheet.
Version 2.0 (build 6) Updated (late) 8 August 2000_

With any luck, this version should install cleanly on most computers. I think previous installation problems were due to the use of an Excel 2000 object library (Microsoft Visual Basic for Applications Extensibility 5.3), which caused compilation problems on Excel 97 (which uses an older version called “Microsoft Visual Basic for Applications Extensibility”). [Note: There could still be installation problems with this version because SOLVER does not seem to have a consistent location in all installations of MS Office. There also seem to be some anomalies in the location of an Excel library called REFEDIT.DLL. Some installations of MS Office seem to leave old copies lying around in the system directory. I still don’t know how to cope with these problems. One user reported successful installation of PopTools after first opening the SOLVER dialog box to force Excel to recognise the location of the SOLVER XLA file.]

Main change is addition of “Matrix rainbow plot”, which gives a quick overview of the numeric values in a range of cells.
Version 2.0 (build 5) Updated 2 Aug 2000_
 Released as a beta, but failed to install on Excel 97
Version 2.0 (build 4) Updated (late) 31 July 2000_
 This build has been compiled with Excel 97 SR2 rather than 2000. Doing so reduced the size of the XLA file by nearly a megabyte, and might help those users that report “Compile error in hidden module etc”
Version 2.0 (build 3) Updated (early) 31 July 2000_

Significant bug in the SHUFFLE function fixed. The routine gave biased randomisations if used on matrices (not vectors).

Routines for calculation of distance/similarity matrices added (under “Extra stats” menu). Not yet documented in help file but the demo file shows how to use them.

Improved biascorrected percentile demo.

Added distance matrix demo.

Minor cosmetic changes.
Version 2.0 (build 2) Updated 27 July 2000_

Major change is addition of following functions which use the TPMath library:
MatInv  matrix inversion (more accurate than Excel’s MINVERSE with near singular systems)
SVDSolve  solve linear system using SVD
QRSolve  solve linear system using QR decomposition
See the “Solve linear system” demo for details of these functions

Linear interpolation function is now an array formula and is in the DLL for speed.

Added interpolation function “Spline” to fit a cubic polynomial through a set of points.

Documented “dRandSeed” function which allows you to set the seed of the pseudorandom number generator

Added array functions to allow matrix projection within a single row of a spreadsheet (see “Matrix projection” demo).

Added a utility to convert text values that should be numeric into real numbers.

Fixed up documentation of some functions in the function wizard.

Added the “Solve linear system” demo, and a demo of how to calculate bias corrected percentile CL’s.

Monte Carlo routine is now more flexible (counts number of times test statistics are less than test statistics of real data, or <= or >=.
Version 2.0 (build 1) Updated 6 July 2000
[Note: A small bug was discovered soon after this version was first loaded.
It was fixed immediately without changing the build number]
Several routines from TPMath library added. See the new help file for
details.

Major change is implementation of nonlinear function minimisation algorithms  Marquardt’s, Simplex, Simulated annealing and BroydenFletcher GoldfarbShanno methods. These are dynamic formulas that return parameter estimates, minimised negative loglikelihood and AIC. They depend on formulation of the model as plain strings (like the ODEIntegrate and Euler functions). They have a rigid input format and are limited to models that can be specified as plain text strings, but are quite powerful.

Added MatInv function for matrix inversion. The function hasn’t been tested extensively on nearsingular matrices, so I don’t know whether it performs any better than Excel’s builtin MINVERSE. But you can easily test for singularity using the SVD function.

Functions Incrementer1 and Incrementer2 added with arguments StartValue, StepSize, Init. These increase every time the worksheet is recalculated, unless the final argument “Init” is True, in which case they stay at the starting value.

Function ColumnPlot added. This function returns an array representing a column plot of the XY data. If entered as an array formula (CtrlShiftEnter) it can be plotted as an XY graph.

Fixed the Interpolate function (for linear interpolation), which wasn’t working in previous releases.
Version 1.5 (build 4) Updated 5 June 2000: a significant upgrade_

Implemented routines for simple linear and multiple regression to replace Excel’s routines. As of this release, none of the PopTools functions depend on LINEST. Instead, Jean DeBord’s TPMath regression routines are called. These have been tested with the National Institutes of Standards in Technology (NIST) data sets (see http://www.nist.gov/itl/div898/strd/). You can download a zipped workbook showing the results of the tests  and methods for using the functions  here. Main features of the function “Regression” are better performance than LINEST with highly collinear problems, and correct R^{2} with forced intercept. The function can be entered using the menu system.

Added function for solution of discrete time systems. The function “Euler” has similar syntax to the “ODEIntegrate” function but performs a discrete time simulation. The function can be entered using the menu system.

Improved the demo sheet for the ODEIntegrate and Euler functions. It makes a nice teaching aid :)
Version 1.5 (build 3) Updated 23 May 2000
 Minor change to RungeKutta algorithm  allowed any size system to be integrated (previous limit was 20 state variables and 100 parameters). [This is a minor change  the algorithm will probably be too slow for very large systems  so don’t bother with the upgrade if you have version 1.5 (build 2)]
Version 1.5 (build 2) Updated 19 May 2000
 Bug fix  RungeKutta algorithm gave incorrect results. Fixed and validated.
Version 1.5 (build 1) Updated 18 May 2000

Added array function that numerically integrates a system of ordinary differential equations using a 4th order RungeKutta algorithm with a fixed step size. The integration is performed entirely in the PopTools DLL (RANDEVS.DLL) with the results being returned to Excel as an array formula. Because the step size is not adaptive, the algorithm best suited to “well behaved” systems; it can be unstable if the system is stiff (although you can usually get around this problem by choosing a smaller step size  albeit at the expense of some computational time).

Added demo ODE.XLS to show how to use the ODEIntegrate function.
Version 1.4 (build 6) Updated 8 May 2000

Functions for generating random variables as array formulas added. For a large number of random variables, the array versions are quicker (often much quicker) than the standard versions, because they make only one call to the DLL. They also make one call to VBA, but this is of little consequence if you are generating a large number of variables. The names of the array functions are the same as the standard versions, but have an “A” appended. Both sorts can be entered via the PopTools menu system.

Fixed minor bug in Monte Carlo routine  it did not return number of valid iterations if no test values provided.
Version 1.4 (build 5) Updated 5 May 2000
 Fixed another small bug (sigh!) in range input routines.
Version 1.4 (build 4) Updated 2 May 2000

Fixed bug in error checking routines with long sheet names.

Added demo of power analysis by simulation.

Function EQUAL now compares significant digits rather than decimal places.

About box now shows web address.

Small changes to help file.
Version 1.4 (build 3) Updated 28 April 2000

Fixed bug in last build  Monte Carlo routine did not calculate the number of times test values exceeded.

Fixed bug in error checking routines.

Fixed bug in demo sheet CORRELATED.XLS fixed  the sheet had a macro attached that caused infinite recalculation if Excel’s calculation mode was set to automatic (the default).

Small changes to help file.
Version 1.4 (build 2) Updated 27 April 2000

Most procedures now include integrated help. To achieve this, I converted the help system to the old WinHelp format (rather than the new HTMLHelp format) to make it compatible with Netscape users. This also makes the help system faster to load.

Minor changes to error checking routines.
Version 1.4 (build 1) Updated 14 April 2000

Most procedures that change worksheet cells now include an “Undo” facility. Only one level of Undo is supported, but because of this facility, I have given most routines the ability (optional) to overwrite a range even it contains array formulas. If an output range contains an array formula, the whole range containing the array is removed (with a VBA ClearContents directive). Although, this operation can be undone, you should still save a workbook before overwriting cells that contain array formulas.

Added data validation routines for all dialogs.

Setup file now launches the README file automatically to complete the installation process.
Version 1.3 (build 8) Updated 10 April 2000

Improved routine for exporting a range to a text file. Added option to export a fixed width file. The fixed width format can be imported directly to the R statistical program (and, presumably, S and Splus) using a simple directive [eg, read.table(“data.txt”)].

Added data validation routines for all routines based on a forms with range selection or numeric input boxes.

Updated help file.

Minor cosmetic changes.
Version 1.3 (build 7) Updated 4 April 2000

Added routine to draw a map of a spreadsheet, showing cells that contain numbers, formulas or text. the routine is particularly useful to find cells that cause errors because Excel is seeing text rather than a number. Relies partly on code from John Walkenbach’s site.

Added routine to export a range to a tabdelimited text file. Uses the DLL.
Version 1.3 (build 6) Updated 25 March 2000

Fixed the function dLogNormaLike, which returned incorrect results if the second argument was a single cell.

Ran the DIEHARD tests on the PRNG.
Version 1.3 (build 5) Updated 23 March 2000
 Bug in likelihood profile routine fixed (procedure failed when computing lower part of a fixed range). Also improved the routine by providing more feedback during a run.
Version 1.3 (build 4) Updated 21 March 2000
 Likelihood profile routine improved. Added facility to estimate profile over a particular range.
Version 1.3 (build 3) Updated 17 March 2000
 Bug fix: New function dNormDist always returned pdf rather than cdf  fixed.
Version 1.3 (build 2) Updated 16 March 2000
 Fixed bug in likelihood formulas that could cause Excel to crash if passed a number instead of a range as second argument.
Version 1.3 (build 1) Updated 14 March 2000:
Several improvements  hence the new version number_

Major change is ability to compute likelihoods with several different error structures (see new demo LIKELIHOOD.XLS)

Likelihood profile routine improved

Routine for generation of random deviates from the Beta distribution is now in the DLL  does not depend on the inverse method using Excel  and is much faster than it was previously.

Added functions dPoisson and dNormDist which have same syntax and result as Excel’s versions (POISSON and NORMDIST), but which don’t have the limitations discussed by Knusel (1998).
Version 1.2 (build 8) Updated 8 March 2000
 Fixed routine for drawing life cycle (didn’t work in Excel 97)
Version 1.2 (build 7) Updated 8 March 2000
 Serious flaw in RESAMPLING routines fixed  first and last variables in a range were selected with half the frequency of variables in middle of a range (apologies to anyone affected  this was a silly error)
Version 1.2 (build 6) Updated 7 March 2000

Routines for parameter estimation improved. These were previously difficult to use without access to the code. The new routines (“Bootstrap iteration” and “Likelihood profile” should be more robust and easier to use).

Bug in “Summary stats” that prevented proper display of plot in worksheets with a space in the name has been fixed.

New demo “Bootstrap.xls” added  parameter estimation by bootstrap method.
Version 1.2 (build 5) Updated 3 March 2000

Developed routine to draw a diagram of the life cycle from a population projection matrix.

Improved interface for entry of dominant eigenvalue, reproductive value and age structure.

Function EQUAL developed to compare values in two ranges to a precision specified.

Added function ELASTICITY (same form as SENSITIVITY).
Version 1.2 (build 4) Updated 28 Feb 2000

Build 3 introduced a bug in SHUFFLEDISTANCE formula. Fixed in Build 4.

Several optimisations of resampling and shuffling routines. Added routines for shuffling and resampling rows and columns of a data matrix

New routine for computing confidence limits by bootstrap method (under “Likelihood Tools” menu)

New routine to provide dynamic summary statistics of Monte Carlo routines.

Some cosmetic changes.
Version 1.2 (build 3) Updated 22 Feb 2000

Version 1.2 introduced a bug in the array function RESAMPLE. The function returned just a single sample from the data. This has now been fixed.

Added “Mantel test” under the “Extra stats” menu. This calls RANDEVS.DLL to do a computation of the Mantel test on large matrices. It is fast and handles large matrices (tested on 256 x 256 matrices of random variables). You can also enter a worksheet formula for the Mantel test, as shown on the new demo.

New demo sheet for the MANTEL worksheet function.

Modified the dBetadev function. The function now returns a 1 row x 3 column array, with the random variable in column one, and the parameters a and b in the next two columns. If entered as a plain formula, you just get the random variable.

Made functions MEANFREQ and VARFREQ available (these were present in earlier versions but not available from worksheets:
MEANFREQ(Vector, Freq) returns the mean of a “Vector” of observations with frequency “Freq”
VARFREQ(Vector, Freq) returns the sample variance (ie, divisor is n1) of a “Vector” of observations
Version 1.2 (build 2) Updated 17 Feb 2000

Major bug in calculation of generation times has been fixed (function LIFE invoked by the ‘Basic analysis” option under “Matrix Tools”). Previous versions of PopTools gave wrong results. The new version has been tested with Leslie matrices, but has not been tested with Usher matrices (standard size classified matrices). Current version should give correct results but you would be wise to check some results by hand.

Added facility to generate random variables from a beta distribution, parameterised by mean [0,1] and standard deviation. The function =BetaDev(μ, σ), or =dBetaDev(μ, σ), returns a random variable from the Beta distribution bounded on [0,1], with mean μ and standard deviation σ. This facility uses the inverse of the cdf, which is a built in function in Excel (ie, BETAINV). The function uses VBA to return the result of the following evaluation:
=BETAINV(RAND(),α,β), where α = (−μσ^{2} + μ^{2} − μ^{3}) / σ^{2}, and β = α(1  μ)/μ
The function does not use the DLL, and because of the call to VBA to re parameterise the function in terms of α and β, it is is slow compared to the other routines for generating random variables. A DLL version will be developed at a later date.

Compressed the RANDEVS.DLL

Fixed several demo sheets.
Version 1.2 (build 1) Updated 9 Feb 2000

Made all the DLL versions of the random deviates into volatile functions and allowed entry via the Excel function wizard.

Added numeric checking of each replicate and a timer to the Monte Carlo routine.

SHUFFLE and RESAMPLE are now DLL functions. They should be a little faster now (not checked). The DLL functions, dShuffle and dResample, can also be called directly from a worksheet.

Updated several demo files.

Some cosmetic changes.
Version 1.1 (build 16) Updated 4 Feb 2000

Found a bug in the function dLogNormalDev (and LogNormalDev). Function did not return required mean and standard deviation.

Cosmetic changes made to Monte Carlo iteration function.

Name of setup file now reflects version number.

DLL recompiled.