Avoid the Excel Data Analysis Add-in’s Traps

By Conrad Carlberg

For years, some of the Excel Data Analysis add-in’s tools (for example, the Regression tool) have confused the input range with the output range. If you’re going to forecast by means of regression you need at least two variables: a predictor variable (such as date or advertising dollars) and a predicted variable (in this context, something such as sales revenues or unit sales).

The Regression tool refers to the predictor variable’s values as the Input X Range, and the predicted variable’s values as the Input Y Range.

Be aware of which reference edit box has the focus.

Now, suppose you do this:

  1. Go to the Ribbon’s Data tab and click Data Analysis in the Analyze group.
  2. Locate and click on the Regression tool in the list box and then click OK.
  3. Click in the Input Y Range field, and then drag through something such as your sales revenue values on the worksheet.
  4. Click in the Input X Range box, and then drag through something such as the date values on the worksheet.
    Notice that the default option for the Output Options is New Worksheet Ply.

If you now override the default option and select the Output Range option button (which lets you put the Regression output on the same sheet with your table), the focus snaps back to Input Y Range. If you then click in some worksheet cell to select it as the output location, that cell becomes the Input Y Range. Because you normally want to use an empty range for the output, you certainly won’t select a cell with input values in it. So you choose an empty cell, and because of the change in focus, that cell becomes the Input Y Range.

In other words, the Regression tool is trying to get you to choose a range, or cell, without any data in it to supply your Input Y Range — that is, the values of the variable that’s to be predicted.

If you’re not aware of what’s going on, this can cost you time and unnecessary skull sweat. Unfortunately, there’s no good solution — remember, you can’t open the code that drives the Data Analysis add-in’s tools — other than to be aware that it happens, and to know you have to select the Output Range option button and then its associated edit box again to reset the focus where you want it.

Several tools in the Data Analysis add-in have this problem. Take care when you’re identifying an output range for one of these tools. If the problem occurs, usually no great harm is done. But it’s really annoying after the third or fourth instance.

The other main problem with the Data Analysis add-in is that its output is often static. The Regression tool, for example, puts calculated values in cells rather than formulas that can recalculate when the inputs change. If you get new or changed input values, you’ll have to rerun the tool to get the revised results.

Other tools, such as Moving Average and Exponential Smoothing, report their results as formulas, so they’ll recalculate if you change the inputs. If you have new values for these tools to use (for example, your input range changes from A1:A20 to A1:A25), you’ll need to reset the input range address; but if you’re just revising an earlier value, the formulas will recalculate and the charts will redraw without any extra effort on your part.