Troubleshooting
This page describes common problems and solutions in working with our files. If you are looking for updated versions of a workbook or add-in, please go to the Updates and Typos page.
Please keep in mind that Excel is not a sophisticated statistical analysis program. For this reason, we explicitly warn you about our Excel workbooks and add-ins: "This software was written and designed for teaching purposes. It has been tested on several examples, but not on a wide variety of datasets. For mission critical projects, always check the results with full-fledged statistical programs."
One common problem involves moving workbooks from one computer to another. Remember that any macros (buttons or functions) that depend on Excel add-ins will not function if you move the workbook to another computer because the workbook relies on code in the add-in that is not available on the new computer.
You should be sure to have the latest version of Excel (and other Office and Windows products) because fixes and patches contain solutions to documented problems: office.microsoft.com/en-au/officeupdate/default.aspx.
For Microsoft documentation and help, see support.microsoft.com
Issues
| Description | Explanation | Solution |
| Buttons (or scroll bars or other objects) do not work at all. Clicking a button does nothing. | All of the buttons (and other objects) in our workbooks are tied to macros. You must allow Excel to run these macros for the buttons to work. | Set Excel's Security (by executing Tools: Macros: Security) to Medium, reopen the workbook and click Enable Macros when prompted. See SettingSecurity.doc |
The add-in won't work. I get an error message like “Can’t find Project or Library” or “Programmatic Access Denied.” |
Add-ins are special files that contain macros. In order to access them, Excel's security must be properly configured. | Set Excel's Security (by executing Tools: Macros: Security) to Trust Access to Visual Basic projects. See SettingSecurity.doc |
I'm using Excel 2007 and I can't figure out how to configure Excel. Will these files work? |
Excel 2007 has been completely reorganized, but it is (mostly) backward compatible and the files will work. | Set Excel's Security in the Trust Center. See SettingSecurityExcel2007.doc |
I am using a non-English version of Excel and I can't install an add-in. |
The add-in is not using the correct word for "Tools" in the Tools menu. | Manually alter the code. See NonEnglishVersionError.doc for detailed instructions. |
A button on one of our workbooks doesn't function. Clicking the button gives an error message. |
This is often due to the button being originally linked to an Excel add-in file that is no longer available. | Re-establish the link to the add-in file. See BrokenLinkCausesButtonFailure.doc |
| A button or function on a workbook created or modified by a user no longer works. Clicking buttons give error messages and functions display #NAME? errors. | The button or function relies on access to an Excel add-in that is no longer available. This will happen when you create a workbook on a computer with access to an add-in, then move the workbook to another computer that does not have the add-in. | Re-establish the link to the add-in file. If the workbook has been moved to a different computer, you will have to install the add-in on that computer and re-establish the link. |
LINEST gives a coefficient estimate of exactly zero and this is obviously incorrect. |
This is a documented bug in Excel 2003 and it has a fix OR you may be dealing with perfect multicollinearity (see below). | If the problem is not perfect multicollinearity, see Excel's Knowledge Base Article 887964 |
The DDV or MCSimSolver add-in doesn't work. |
Assuming you have Solver properly installed and functioning, this is usually caused by the add-in not being able to find the Solver add-in file, Solver.xla. | Search your hard drive for "Solver." If you find other files that contain the name Solver (e.g., Solver.doc), rename them. You must have a Solver.xla file for the add-in to work. Solver is proprietary and owned by Frontline Systems, Inc. Another possibility is that Security is improperly set. See SettingSecurity.doc |
| Excel's regression functions have trouble dealing with cases of perfect multicollinearity. | There is no unique solution to the ordinary least squares optimization problem in cases of perfect multicollinearity. Different versions of Excel have various problems when confronting this fact. | The simplest solution is to drop one of the collinear variables. The way Excel handles perfect multicollinearity depends on which version you are using. See pp. 187-188 in our book. |
If you cannot find the answer to your question here, please contact us:
Humberto Barreto and Frank M. Howland
Last Update: 6 July 2017