SolverTable, StatPro, StatBasics Add-Ins

SolverTable is add-in that I developed. It is analogous to Excels 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 StatPros, and Im 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 Id 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, its 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 cant 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, Im not continuing to support StatPro Im 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 StatPros 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 Xs, so it is a measure of how related this X is to the other Xs. The VIF (variance inflation factor) for each X variable is the reciprocal of 1 minus this Xs R-square. So if this Xs 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 Excels add-ins list (under ToolsAdd-Ins). To fix the problem, select the ToolsAdd-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 ToolsAdd-ins, unchecked the StatPro item, and closed Excel. Then I launched Excel again, used ToolsAdd-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