Current version of PopTools is 2.7 (build 5)
Version 2.7 (build 5) Released Sep 25, 2006
1. 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, user-defined,
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.
2. Feature: Monte-Carlo routine now allows setting of random seed at beginning
of a run.
3. Bug fix: Bug in R code for fitting distributions fixed.
Version 2.7 (build 4) Released Sep 18, 2006
1. 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").
2. Bug fix: Generator of geometric deviates (dGeomDev) gave incorrect
distribution. Fixed.
3. 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.
4. 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
1. Feature: Monte-Carlo routine remembers input settings.
2. Feature: New Monte-Carlo demonstration file.
3. Feature: Web based demonstration files now available under 'Demos'.
Version 2.7 (build 2) Released Sep 11, 2006
1. 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.
2. 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.
3. Feature: Colour scale function now ignores cells containing error values.
4. 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 'Add-Ins' 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.
5. 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
1. 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.
2. Bug fix: Routines to standardise matrices (ColCentStand and RowCentStand)
now compute sample standard deviation (ie, divide by n-1) rather than population
standard deviation (divide by n).
Version 2.6 (build 9) Released Oct 1, 2005
1. 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.
2. 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.
3. 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
1. Bug fix: Fixed routine that returns life table statistics.
Version 2.6 (build 6) Released Feb 24, 2005
1. 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.
2. 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
1. Feature: Improved routines for creating code to connect to R,
including addition of a "Connect to worksheet" function.
2. 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.
2. "Range to comments". Takes cell values and places them as cell comments
in a new range.
3. "Comments to HTML". Produces an HTML file displaying cell comments
of a selected range.
4. "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.
5. "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)..
1. 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
1. Bug fix: The "R connection" routine failed to insert a named range in the
workbook. Fixed.
2. Bug fix: The "R dataframe" routine did not quote strings. Fixed.
3. 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
1. 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
1. 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.
2. 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.
3. The sensitivity analysis function for population projection
matrices now has an option to calculate sensitivities for
zero-valued elements. An option to set the new boolean argument
to the function is provided in the interface.
4. An appropriate citation of the product for scientific papers
has been added to the About box.
5. Monte-Carlo routine now requires an output cell before
starting the iteration.
6. Two routines added to make it easy to interact with the
statistical program R (see
http://cran.r-project.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
1. Bug fix: Oops! Last version(s) failed to compile on Excel 97.
Fixed.
Version 2.5 (build 4) Released Mar 19, 2003
1. Bug fix: dPCA function caused Excel to crash if columns >
rows. Fixed
2. Bug fix: dChiTest returned incorrect results for some 2x2
tables. Fixed
Version 2.5 (build 3) Released Oct 10, 2002
1. Bug fix: Last version(s) failed to compile on Excel 97.
Fixed.
Version 2.5 (build 2) Released Oct 7, 2002
1. 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.
2. Bug fix: The Jacknife demonstration was returning #NAME errors
because the DeleteElement function was failing.
3. New mirror site established.
Version 2.5 (build 1) Not released
1. 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.
2. 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
1. Bug fix: Minor fixes to the routines for entry of random
variables into a worksheet (the code that actually generates the
variables is unchanged).
2. Several other fixes which I have been too busy to record.
Version 2.4 (build 8) Released May 16, 2002
1. 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
1. 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
1. 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 re-compiled but no algorithms have changed.
2. Bug fix: Numerical projection routine failed to save the first
step of the iteration. Fixed.
3. 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
1. 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) only.
Unfortunately, I don't have access to Excel 2002, so I can't
check this. The maximum array size allowed should now be
268369924 in Excel 2002, and 5461 in earlier versions of
Excel.
2. 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]
3. 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
1. Epidemiological tools added to "Extra stats" menu - formulas
for relative risk (dRR) and odds ratio (dOR) with confidence
limits.
2. New routine for sampling with probability proportional to size
(PPS) added to "Abundance" menu.
3. Added ability to generate multivariate Normal samples from a
population with a specified covariance matrix to the "Random
variable" menu.
4. Fixed bug in the "Schnabel" routine.
5. 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.
6. 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
1. Bug fix: New version of function to generate negative binomial
deviates (previous version was unreliable with large
values).
2. 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
1. Updated the eigenvector calculations to use a new version of
the TPMath library.
2. Bug in drawing of life cycle diagram fixed (routine crashed
when reproductive value calculated).
3. 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.
4. Numerical projection routine now has option for multiple time
iterations.
5. Sensitivity analysis recoded -- now allows entry of
arbititrarily sized matrix of parameters.
6. Added facility to define dynamic range names.
7. Added more documentation of worksheet functions to help file
and this site.
8. New (permanent) e-mail established poptools@csiro.au
9. 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
1. Main change is an updated installation file that no longer
requires VBA macros.
2. Bug fix. The routine to randomise worksheet ranges produces
erroneous results if there are non-numeric (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.
3. Bug fix: Output of matrix projection routine had an extra row
of error values. Fixed.
4. Bug fix: Removed links from some demonstration files.
5. Added routine to update links to the PopTools xla.
6. Added routine to delete unused range names from the active
workbook (under Auditing tools)
7. 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
1. Chi2 test now has Yates continuity correction for 2 x 2
tables.
2. 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.
3. Bug fix. Analysis of projection matrices now works for
matrices larger than 18x18.
4. Bug fix. Mantel test calculated sum of products incorrectly.
Fixed.
5. Bug fix. Minor adjustments to the auditing tools.
6. Bug fix. Ro was incorrect for Leslie matrices if
entered as a single projection matrix and element 1,1 was
non-zero.
7. New routines to analyse life tables and build projection
matrices from lx and mx columns of a life table.
8. Added menu entry of matrix power function.
9. Demo added LIFETABLE.XLS. Some adjustments to other
demos.
10. 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
1. Last version introduced a bug in the SVD routine that crashed
Excel when analysing non-square matrices. Fixed in this
release.
2. Basic analysis of projection matrices improved
3. Minor changes to the G-test and Chi square routines (more
error checking).
4. 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.
5. 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 ill-kempt than he would
probably like :-)
Version 2.2 (build 1) Updated 21 March 2001
1. 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.
2. 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).
3. SVD function now returns singular values in descending order -
slower but much more useful.
4. Several array formulas added:
All of these array formulas require the array entry technique
(see this
page for details). Utility of some of these functions for
matrix models is illustrated in the first demonstration sheet:
Eigenanalysis.xls.
5. Removed calculation of some generation times from the LIFE
function (too hard to make these general without separate
fecundity and survivorship matrices as input).
6. Added functions to support fitting with SIMPLEX, Marquardt and
BFGS procedures.
Version 2.1 (build 7) Updated 1 March 2001
1. 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).
2. Bug fix: Dialog box for entry of PCA formula sometimes did not
work. Fixed.
3. 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
1. 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).
2. New INSTALLATION.XLS prepared that no longer requires
SOLVER.
3. Updated the setup files using new version of the setup
compiler (Inno setup).
Version 2.1 (build 5) Updated 27 September 2000
1. 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
:-)
2. 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.
3. 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
1. 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.
2. New function SymPow calculates rational power of a symmetric
matrix using spectral method. Useful for multivariate
statistics.
3. Added menu entry of Jacobi function and SymPow
4. Updated some demonstration files.
5. The ACF function now returns approximate pacf in second column
of the output.
Version 2.1 (build 3) Updated 14 September 2000
1. 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
1. 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]
1. Bug fix: Monte Carlo routine did not restore calculation
state. Fixed.
2. Added function =SetRan(PRNG). If PRNG = 1, the Marsaglia
algorithm from the TPMath library is used for generation of
pseudo-random 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
1. Added several new matrix formulas for centering and
standardising data matrices.
2. Matrix utility functions added: TRACE, DETERM, UPTRIANG,
IDENTITY.
3. Implemented the Mersenne Twister as the pseudo-random number
generator.
4. Added a "Test" button to the dialog for entry of random
variables. Clicking the button generates a file for testing the
pseudo-random number generator using the DOS version of
DIEHARD.
5. 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.
6. 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).
7. Calculation of Abar (in the function LIFE) is wrong. I have
turned this feature off until I figure out what is going
wrong.
8. 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
1. 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.]
2. 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
1. Released as a beta, but failed to install on Excel 97
Version 2.0 (build 4) Updated (late) 31 July 2000
1. 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
1. Significant bug in the SHUFFLE function fixed. The routine
gave biased randomisations if used on matrices (not
vectors).
2. 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.
3. Improved bias-corrected percentile demo.
4. Added distance matrix demo.
5. Minor cosmetic changes.
Version 2.0 (build 2) Updated 27 July 2000
1. 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
2. Linear interpolation function is now an array formula and is
in the DLL for speed.
3. Added interpolation function "Spline" to fit a cubic
polynomial through a set of points.
4. Documented "dRandSeed" function which allows you to set the
seed of the pseudorandom number generator
5. Added array functions to allow matrix projection within a
single row of a spreadsheet (see "Matrix projection" demo).
6. Added a utility to convert text values that should be numeric
into real numbers.
7. Fixed up documentation of some functions in the function
wizard.
8. Added the "Solve linear system" demo, and a demo of how to
calculate bias corrected percentile CL's.
9. 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.
1. Major change is implementation of non-linear function
minimisation algorithms - Marquardt's, Simplex, Simulated
annealing and Broyden-Fletcher-Goldfarb-Shanno methods. These are
dynamic formulas that return parameter estimates, minimised
negative log-likelihood 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.
2. Added MatInv function for matrix inversion. The function
hasn't been tested extensively on near-singular matrices, so I
don't know whether it performs any better than Excel's built-in
MINVERSE. But you can easily test for singularity using the SVD
function.
3. 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.
4. Function ColumnPlot added. This function returns an array
representing a column plot of the X-Y data. If entered as an
array formula (Ctrl-Shift-Enter) it can be plotted as an XY
graph.
5. 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
1. 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.
2. 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.
3. 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:
1. Minor change to Runge-Kutta 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:
1. Bug fix - Runge-Kutta algorithm gave incorrect results. Fixed
and validated.
Version 1.5 (build 1) Updated 18 May 2000:
1. Added array function that numerically integrates a system of
ordinary differential equations using a 4th order Runge-Kutta
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).
2. Added demo ODE.XLS to show how to use the ODEIntegrate
function.
Version 1.4 (build 6) Updated 8 May 2000:
1. 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.
2. 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:
1. Fixed another small bug (sigh!) in range input routines.
Version 1.4 (build 4) Updated 2 May 2000:
1. Fixed bug in error checking routines with long sheet
names.
2. Added demo of power analysis by simulation.
3. Function EQUAL now compares significant digits rather than
decimal places.
4. About box now shows web address.
4. Small changes to help file.
Version 1.4 (build 3) Updated 28 April 2000:
1. Fixed bug in last build - Monte Carlo routine did not
calculate the number of times test values exceeded.
2. Fixed bug in error checking routines.
3. 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).
4. Small changes to help file.
Version 1.4 (build 2) Updated 27 April 2000:
1. 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.
2. Minor changes to error checking routines.
Version 1.4 (build 1) Updated 14 April 2000:
1. 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.
2. Added data validation routines for all dialogs.
3. Setup file now launches the README file automatically to
complete the installation process.
Version 1.3 (build 8) Updated 10 April 2000:
1. 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 S-plus) using a simple directive [eg,
read.table("data.txt")].
2. Added data validation routines for all routines based on a
forms with range selection or numeric input boxes.
3. Updated help file.
4. Minor cosmetic changes.
Version 1.3 (build 7) Updated 4 April 2000:
1. 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.
2. Added routine to export a range to a tab-delimited text file.
Uses the DLL.
Version 1.3 (build 6) Updated 25 March 2000:
1. Fixed the function dLogNormaLike, which returned incorrect
results if the second argument was a single cell.
2. Ran the DIEHARD
tests on the PRNG.
Version 1.3 (build 5) Updated 23 March 2000:
1. 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:
1. Likelihood profile routine improved. Added facility to
estimate profile over a particular range.
Version 1.3 (build 3) Updated 17 March 2000:
1. Bug fix: New function dNormDist always returned pdf rather
than cdf - fixed.
Version 1.3 (build 2) Updated 16 March 2000:
1. 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
1. Major change is ability to compute likelihoods with several
different error structures (see new demo LIKELIHOOD.XLS)
2. Likelihood profile routine improved
3. 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.
4. 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:
1. Fixed routine for drawing life cycle (didn't work in Excel
97)
Version 1.2 (build 7) Updated 8 March 2000:
1. 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:
1. 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).
2. Bug in "Summary stats" that prevented proper display of plot
in worksheets with a space in the name has been fixed.
3. New demo "Bootstrap.xls" added - parameter estimation by
bootstrap method.
Version 1.2 (build 5) Updated 3 March 2000:
1. Developed routine to draw a diagram
of the life cycle from a population projection matrix.
2. Improved interface for entry of dominant eigenvalue,
reproductive value and age structure.
3. Function EQUAL developed to compare values in two ranges to a
precision specified.
4. Added function ELASTICITY (same form as SENSITIVITY).
Version 1.2 (build 4) Updated 28 Feb 2000:
1. Build 3 introduced a bug in SHUFFLEDISTANCE formula. Fixed in
Build 4.
2. Several optimisations of resampling and shuffling routines.
Added routines for shuffling and resampling rows and columns of a
data matrix
3. New routine for computing confidence limits by bootstrap
method (under "Likelihood Tools" menu)
4. New routine to provide dynamic summary
statistics of Monte Carlo routines.
5. Some cosmetic changes.
Version 1.2 (build 3) Updated 22 Feb 2000:
1. 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.
2. 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.
3. New demo sheet for the MANTEL worksheet function.
4. 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.
5. 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 n-1) of a "Vector" of observations
Version 1.2 (build 2) Updated 17 Feb 2000:
1. 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.
2. Added facility to generate random variables from a beta
distribution, parameterised by mean [0,1] and standard deviation.
The function =BetaDev(m, s) [or
=dBetaDev(m, s)] returns a random
variable from the Beta distribution bounded on [0,1], with mean
m and standard deviation s. 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(),a,b), where a = (-ms^{2} + m^{2} -
m^{3}) / s2, and b = a(1 -
m)/m
The function does not use the DLL, and because of the call
to VBA to re-parameterise the function in terms of a and b, it is is
slow compared to the other routines for generating random
variables. A DLL version will be developed at a later date.
3. Compressed the RANDEVS.DLL
4. Fixed several demo sheets.
Version 1.2 (build 1) Updated 9 Feb 2000:
1. Made all the DLL versions of the random deviates into volatile
functions and allowed entry via the Excel function wizard.
2. Added numeric checking of each replicate and a timer to the
Monte Carlo routine.
3. 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.
4. Updated several demo files.
5. Some cosmetic changes.
Version 1.1 (build 16) Updated 4 Feb 2000:
1. Found a bug in the function dLogNormalDev (and LogNormalDev).
Function did not return required mean and standard
deviation.
2. Cosmetic changes made to Monte Carlo iteration function.
3. Name of setup file now reflects version number.
4. DLL recompiled.