The RPM values (1000 through 5000) were plugged into the formula, but it was calculated by Excel using the counting numbers 1 through 5. The calculated values are way too high: 5.1E+10 is 51 billion. When the coefficients and actual X values are plugged into the trendline formula, we get the following actual HP values and fitted values (“Line”). The fit doesn’t look too bad, but as I pointed out, the X values are not appropriate for the fit. In this chart I have applied a fourth order trendline to the data, removed the lines between the points, and formatted the curved trendline to match the series. Notice the X values: The axis doesn’t start at zero, and although the differences between adjacent numbers are not all the same (some differ by 1000, others by 500), the spacing between labels is constant. The first mistake people make while fitting trendlines to charts is when they start with a line chart. For our purposes here, suffice to say that XY charts treat both X and Y data as continuously variable numerical data, while line charts treat the X values as non-numerical text labels, and if necessary, treats them using the counting numbers 1, 2, 3, etc.
XY Charts I described differences between XY and Line charts. The person who asked about fitting this data didn’t make this mistake, but this is a fine place to illustrate it. The first problem that many people encounter when fitting a trendline is caused by using the wrong chart type. This is Yet Another Reason not to use 3D charts, when will you ever learn? You cannot add trendlines to a 3D chart: the command is disabled.
In the sheet, calculate the sums of the first series, then the first and second, then the first, second, and third series, etc., and add them to the chart as line chart series, formatted below as markers without lines.Īpply trendlines to the unstacked line series, format the trendlines, format the line series to display no markers, and remove all the unneeded entries from the legend. Note that each trendline captures the variability of not only its respective series, but all other series stacked beneath it. You must promise never to use them for evil purposes.
You cannot add trendlines to a stacked series: the command is disabled.įor qualitative purposes of illustration, I’ll show how to add trendlines to this type of chart. You can even add trendlines to a horizontal bar chart, but their usefulness is even less than that of trendlines in a line or column chart. This adds to the confusion of non-numeric categories. Note that the trendlines don’t necessarily match up with their corresponding columns, but with the center of the cluster (centered over the category labels). You can also add trendlines to a clustered column chart. I discuss use of trendlines on the wrong chart types in the next section. Note that the categories are not numerical, and a trend between discrete categories may be meaningless (e.g., Cat, Dog, Ferret, Goldfish). You can add trendlines to a line chart Excel makes no value judgments here. Trendlines are really valid only for charts with a numerical category axis, such as an XY chart, or a line chart with a date-scale axis. Below I’ve formatted the trendline and trendline formula to match the line series, and changed the line series to display markers only. You can right click the trendline, choose Format, and make it much more presentable. The added trendline is dumped onto the chart, obliterating details in its vicinity. The specifics of this dialog are a topic for a different discussion. This dialog also appears when you right-click on an existing trendline and choose Format from the context menu, with an additional tab for patterns (to format the line). This pops up a dialog from which you can select a type of trendline to fit to the series, as well as choose options for the trendline. The easiest way is to right click on a series, and choose Add Trendline from the context menu. For the most part, Excel is adequate for this purpose, particularly if the data aren’t “extreme” (a statistical term) and if the data is not overfitted.Īdding a trendline is straightforward. Note: Some versions of Excel have problems performing statistics on some data sets. The person had a motor, and had measured horsepower (HP) at particular rotational speeds, in RPM (revolutions per minute).
The errors are listed in the order they are likely to be realized, not in the order of severity. I will use this example to describe a number of errors people encounter when fitting data. I saved the example, because I knew I’d have a blog someday, and I’d need a topic. Several years ago I helped someone who was having trouble with a fitted trendline in an Excel chart.