SolverTable, StatPro, StatBasics Add-Ins
SolverTable is add-in that I developed. It is analogous to Excel’s data tables, except that when you vary one or two inputs, Solver runs. SolverTable ships with all of our books except VBA for Modelers. It is also available here as a free add-in.
StatPro is a statistical add-in for Excel that I developed. Several years ago, Palisade developed a commercial version of this add-in, renamed StatTools, and the educational version of StatTools now accompanies our books. Although StatPro has not been developed any further, it is still available at this site as a free add-in. In addition, I have developed a mini version of StatPro that I call StatBasics. This mini version contains only the basics – summary statistics and basic charts – but its interface is a great improvement over StatPro’s, and I’m hoping that some of its chart features will eventually find their way into StatTools.
SolverTable Items
·
SolverTable
for Excel 2007. Here is a version of SolverTable that's compatible with Excel 2007 (but not
previous versions). The main difference, besides a slightly different user
interface, is that it references the new Solver add-in file, Solver.xlam. Read
the help file included in the zip file for more info. As this help file
indicates, to run this version of SolverTable
successfully, you must first click on the Macro Security button on the
Developer tab and check the “Trust access to the VBA project object model”
option. Download: SolverTable 2007.2.zip. (Last updated: 10/22/2007)
·
Fixing SolverTable
for Excel 2007. A few
people have had problems getting SolverTable for 2007
to work correctly, so besides the directions above on macro settings, the
following might help you out: Fixing
SolverTable 2007.doc
·
SolverTable
for Excel 2003 and earlier. Download: StatPro_SolverTable_Setup.zip.
This setup program allows you to install SolverTable.
(It also gives you the option to install StatPro.)
This is the version included in our
books, so if you've installed SolverTable from
any of our books, you can ignore this setup. If there are current versions of SolverTable in the setup's default folder (C:\Program
Files\Microsoft Office\Officexx\Libary\SolverTable),
the setup program will overwrite them. Note: If you are trying to
replace an older version of either of SolverTable and
it is in some folder other than the setup's default folder, I strongly suggest
that you physically delete the old version. Having two versions in different
locations will almost certainly cause you problems. See the next bullet for
instructions on fixing problems with SolverTable.
·
Fixing SolverTable
for Excel 2003 and earlier. I've answered enough
e-mails about fixing SolverTable setups, always with
basically the same answers, that I finally decided to write it up and put a
link here. So if you are having problems with SolverTable
not working correctly, or not working at all, check out this link: Fixing SolverTable.doc.
·
An interesting use of SolverTable: One way to use SolverTable that I’d never thought of is to let the Input
cell(s) (for a one-way or two-way table) be the initial value(s) of changing
cell(s). For a linear model, the only point in doing this would be to check
that Solver indeed gets to the optimal solution regardless of the initial
values. For a nonlinear model, this could be used to check whether there are local
optima that Solver might get to, depending on the initial values it starts
from. For example, for problem 7.48 of PMS 3e, which has exactly two changing
cells, it’s easy to show that Solver gets to the global optimum only for some
initial values of the changing cells. (Thanks to Tom Schriber for this
suggestion.)
StatPro (and StatBasics) Items
·
StatBasics
for Excel 2003 and earlier, version 3. The version you can
download here shows the kinds of changes I'm working on. It has only the
statistical basics: summary statistics, correlations, autocorrelations, and
charts. Also, the Help buttons aren't operational. But feel free to try it out.
Download: StatBasics for Excel 2003 V3.zip. (Note: If you
downloaded the version I posted about a month ago and it didn't work, try this
one instead. I left some extraneous references in the previous version that
caused problems. Now they're gone. I also changed the internal name to StatBasics, not StatPro, in
version 3 so that it can coexist with the full version of StatPro.)
Installation instructions are in the zip file. If you're already running StatPro on your PC, it will run along with StatBasics with no problem. However, I suggest that you
unzip the StatBasics files to a separate folder. By
the way, this version doesn't work well with Excel 2007. It doesn't crash, but
the charts come out in a strange way. I'll work on that! (Last updated:
5/25/07)
·
StatPro
for Excel 2007. I can’t guarantee that the StatPro
you download in the following bullet will work perfectly in Excel 2007, but
most of it should work fine. You might have trouble with charts (boxplots?) and modules (exponential smoothing, logistic
regression) that rely on Solver. However, as stated earlier, I’m not continuing
to support StatPro – I’m leaving it up to Palisade to
make the appropriate changes in StatTools.
·
StatPro
for Excel 2003 and earlier. Download: StatPro_SolverTable_Setup.zip.
This setup program allows you to install StatPro. (It
also gives you the option to install SolverTable.)
This is the version included in Data Analysis and Decision Making, 2nd
ed., and Data Analysis for Managers, 2nd ed., so if you've installed StatPro from either of these books, you can ignore this
setup. If there is a current versions of StatPro in
the setup's default folder (C:\Program Files\Microsoft Office\Officexx\Libary\StatPro),
the setup program will overwrite it. Note: If you are trying to replace
an older version of StatPro and it is in some folder
other than the setup's default folder, I strongly suggest that you physically
delete the old version. Having two versions in different locations will almost
certainly cause you problems. See the next bullet for instructions on fixing
problems with StatPro.
·
Fixing StatPro.
I've answered enough e-mails about fixing StatPro
setups, always with basically the same answers, that I finally decided to write
it up and put a link here. So if you are having problems with StatPro not working correctly, or not working at all, check
out this link: Fixing StatPro.doc.
·
Improved
regression module. One user asked for extra information
about multicollinearity in StatPro’s
regression procedures. It is now available in the following download: Regr.zip. First, it lets you provide a critical
value to check for extreme multicollinearity. More
importantly, it allows you to ask for multicollinearity
diagnostics: VIF and R-square values. The R-square value for each X variable is
the R-square for a regression if this X were regressed on the other X’s, so it
is a measure of how related this X is to the other X’s. The VIF (variance
inflation factor) for each X variable is the reciprocal of 1 minus this X’s
R-square. So if this X’s R-square is large, VIF is large. To use this improved
module, unzip the file and copy the Regr.xla file to your StatPro
folder (probably C:\Program Files\Microsoft Office\Officexx\Libary\StatPro), letting it write
over the old Regr.xla file. You might want to rename the old version as
Regr_old.xla first, just so that you have it as a backup.
·
Initialization
of exponential smoothing in StatPro.
Several of you have asked how I implemented the initialization of exponential
smoothing in StatPro. Read the following to see my
procedure: Exponential
Smoothing Initialization.doc.
·
Forecast module in StatPro:
A few of you have reported problems with the forecasting module in StatPro. This is typically a Solver problem. The
forecasting module gives you the option to optimize the smoothing constants for
exponential smoothing. To do this, StatPro checks
whether the Solver add-in is loaded, and if it isn't, it first attempts to load
Solver. Unfortunately, this evidently fails to work on some PCs, for reasons
which are not clear to me. (Yes, I've checked with the technical support at
Frontline Systems, the developer of Solver, and they've confirmed that my StatPro code should work. But it still doesn't for a few of
you.) Therefore, a simple fix is to delete all references to Solver and disable
the optimization option. I've done this in the following file (which you can
unzip): Forecast.zip. To use this file, I
suggest you do the following two steps. After doing this, the forecast module
should work as usual; you just won't be able to optimize the smoothing
constants. However, you can still try running Solver manually to
minimize RMSE (or one of the other error measures). By the way, if Solver is
really the culprit, you will almost surely continue to have problems with StatPro's logistic regression module. There is no easy way
to perform logistic regression without the help of a built-in optimizer
like Solver. (December 2003)
o Go
into the StatPro folder on your PC (probably
C:\Program Files\Microsoft Office\Officexx\Libary\StatPro) and rename the
files Forecast.xla and Forecast1.xla to something like Forecast_withSolver.xla
and Forecast1_withSolver.xla. This way, you still have these original files if
you ever need them.
o Download
the new Forecast.xla file from the above zip file to the StatPro
folder.
·
Problem
with seasonality indexes in StatPro. Open
the following file to see the reasons for problems with seasonality indexes
under certain conditions: Ratio to
Centered Moving Averages.xls.
·
Naming problems with StatPro: One user ran into a
naming problem with StatPro. (I'm surprised I haven't
heard of this from others.) He had a variable called Q7, and the program
bombed. Here's why. StatPro checks whether a variable
name is a legal cell address (which Q7 is). If so, that variable's associated
range name should be Q7_ (note the underscore) since Excel won't allow range
names that look like cell addresses. Well, for a very obscure reason, StatPro's code fails to recognize that Q7 (and some others)
are legal cell addresses. Then it tries to range-name them as Q7, which Excel
doesn't like, so the program bombs. Once I discovered the problem, the fix was
fairly easy. This fix isn't built into the above setup program. If you want to
incorporate it, install StatPro with the above Setup
program and then replace the StatPro.xla file with the following fixed
version (which you can unzip): StatPro.zip.
For most users, the StatPro.xla file will be found under the C:\Program
Files\Microsoft Office\Officexx\Libary\StatPro folder.
·
Regression limit in StatPro:
A couple of users have complained that StatPro's
regression procedure bombs with really large data sets. We have verified that
this is indeed true, but we wondered why. Luckily, a connection at Microsoft
provided the obscure reason. As you might know, regression requires matrix
manipulation, specifically, doing matrix multiplication and taking a matrix
inverse. We implemented these in StatPro with Excel's
MMULT and MINVERSE functions. As it turns out, however, these functions are
limited by Excel to ranges no longer than 5463. In other words, if the sample
size is larger than 5463, StatPro's regression
procedures will fail. We agree that this upper limit appears to be totally
arbitrary, but it's a limit of Excel that StatPro is
stuck with. We should also add that StatTools (the
educational version that is now being packaged with our books) has an even more
stringent limit: 1000. However, this is a deliberate limit imposed by Palisade
for the educational version, not a built-in limit of Excel.
·
StatPro
troubleshooting. Most problems should go away if you
follow the instructions in the Fixing StatPro.doc file mentioned above. Please
read it if you're having problems. Other possible problems follow.
o
Conflict with Norton Anti-Virus 2002:
Although I haven't experienced it personally, other users have experienced a
conflict between StatPro and Norton Anti-Virus 2002
(only this verion of NAV). The solution is to disable
the Office Plug-in in NA 2002. (Thanks to Ron Craig at Wilfrid
Laurier Univ.)
o
Too many files added in.
There are a number of .xla files that come with StatPro and are all stored in the same StatPro
folder. For example, the Regr.xla file runs the regression module. However,
when you use the Tools/Add-ins menu item to load StatPro,
the only .xla file you should select is StatPro.xla.
Some of my students have also selected the Regr.xla file and others, which is
the wrong thing to do. You don't want these in Excel’s add-ins list (under
ToolsàAdd-Ins).
To fix the problem, select the ToolsàAdd-ins
menu item and uncheck any items other than StatPro
that shouldn't be checked. This is a bit of a pain because you'll have to find
out which items in the list are part of the StatPro add-in and which are totally unrelated, such as
Analysis Toolpak, Conditional Sum Wizard, and others.
These latter items can be checked or unchecked, as you like, and won't affect StatPro.
o
One other tip that might solve the
previous problem. I hate to get you into Visual Basic,
but the following is quite easy and might get you out of a lingering problem.
From Excel, open the Visual Basic editor by pressing Alt-F11. Open the Project
Explorer window (ctrl-r) to see a list of open "projects", and open
the Immediate window (ctrl-g), where you'll type a
line of code. Look through the projects. You should see StatPro,
but you shouldn't see any of the other .xla files
that come with StatPro, such as Regr.xla. If you see
any, you can close them by typing the following line in the Immediate window
and pressing Enter:
Workbooks("Regr.xla").Close
Of course, you'd replace Regr.xla by whatever file you want to close. Now get
back to Excel (its button should be on your status bar at the bottom of the
screen), close Excel, and then open it again. Everything should now be OK. That
is, if you get back into the VB editor, the offending .xla
file should no longer be in the project list.
o
Message about some workbook that can't
be deleted when you launch Excel. I had two students
report this problem recently. I'm still not sure why it occurred, but I solved
it by the following trickery. First, I used ToolsàAdd-ins, unchecked
the StatPro item, and closed Excel. Then I launched
Excel again, used ToolsàAdd-ins,
and checked the StatPro item. To make sure this worked, I closed Excel, opened it again, and saw no
offending message.
o
Wrong data range
The first step in almost all of the StatPro
procedures is to request a data range. If you have your cursor inside the data
set, StatPro will usually guess the correct range
that "surrounds" the cursor location. However, its guess is
occasionally wrong, and you should override it if you get a strange error
message. For example, the first row of the data range should always have
variable names. If one of the cells in this first row is blank, you'll get an
error message. Again, the "fix" is to manually override StatPro's guess if you see that it's wrong.
·
International issue (August 2002). A
user from Norway had problems with parts of StatPro
(and SolverTable) that rely on the Solver add-in, but
his colleague from Holland didn't have the problems. He finally straightened
them out as he describes: "We [he and his colleague] have exactly same same settings on WIN NT / OFFICE 2000 on our machine apart
from one thing, the regional settings under the Control Board in Windows. She
had an English USA setting while I had a Norwegian setting. After converting to
USA setting, things worked fine. To conclude, Excel settings and regional
settings can vary in different countries in Europe. I know for example that
settings are different in UK, Germany, and Norway when it comes to how , and . are treated in excel.
Probably the Solver (Problemloser in Norwegian) has
different European settings with respect to variable types and declaration."
Visit the Cengage site for our books. (The former Thomson Learning is now Cengage Learning.)
Send e-mail to albright@indiana.edu, winston@indiana.edu
Albright and Winston are located
at the Kelley School of
Business, Indiana University, Bloomington
Back to Albright,
Winston, Zappe home page
Updated: 11/27/2007