More Predictive Analytics: Microsoft Excel (Brianne Kwasny's Library) by Conrad Carlberg

Author:Conrad Carlberg
Language: eng
Format: mobi
Publisher: Que Publishing
Published: 2016-09-18T16:00:00+00:00

Confirming the Regression Equation

If you want it, Excel will display on the chart the equation that’s behind the polynomial trendline. At the bottom of the Trendline Options pane is a Display Equation on Chart check box. Fill it to see the equation on the chart. To see it properly, you might have to click and drag the equation, which is really just a label, to an empty section of the chart’s plot area.

The predictor variable in this sort of situation is frequently, but not always, a set of sequential integers that identify the time period, such as those in the range A3:A22 in Figure 4.20. On occasion you will find yourself using some other identifier for the time period such as Year (that is, 1980, 1984, and so on). In that case, be sure to have Excel create an XY (Scatter) chart rather than some other type, such as a Line chart.

A Line chart’s horizontal axis is a category axis, not a value axis. So when you request a trendline in a Line chart, the values on the horizontal axis, regardless of their appearance as labels on the chart, are treated as 1, 2, 3,..., 9 rather than, for example, 1980, 1984, 1988,..., 2012. That means that the equation as it appears on the chart will differ from the equation as it’s returned by LINEST(). That inconsistent outcome has resulted in more ill-informed complaints to web groups about inaccurate polynomial regression in Excel than I care to count; I got to the mid-30s before I sickened of the task.

To avoid the apparent inconsistency, be sure to call for an XY (Scatter) chart rather than a chart with categories on the horizontal axis, such as a Line or a Column chart.

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.