Trendlines and Error BarsVBA Name Trendlines and Error Bar

If you have observed values, you might want to chart both the values and then add a trend line to help illustrate the trend. Excel offers several types of trend lines: linear, logarithmic, polynomial, power, exponential, and moving average. After you've added the trend line, you might want to add error bars to show how the actual values differ from the trend.

Figure 10.14 shows an XY chart plotting sales by year for a product line.

Figure 10.14 shows an XY chart plotting sales by year for a product line.

Equation Straight Line Worksheet

The following code adds a trend line to the chart based on a straight-line trend. It tells Excel to forecast forward 5 years and to display the series equation plus the R-squared value on the chart:

Sub AddTrendLine() Dim Ser As Series Dim Trnd As Trendline Dim Cht As Chart

Set Cht = Worksheets("Sheet1").ChartObjects("Chart 2").Chart Set Ser = Cht.SeriesCollection(l)

Set Trnd = Ser.Trendlines.Add(Type:=xlLinear, Forward:=5, _

Backward:=0, DisplayEquation:=True, DisplayRSquared:=True) Trnd.Border.LineStyle = xlDot With Cht

.HasTitle = True.ChartTitle.Characters.Text = "Forecasted Sales" End With End Sub

After the trend line is added, you can see projected sales for the next 5 years. An R-Squared value near 1 means that the observed data fits fairly closely to the trend line (see Figure 10.15).

Figure 10.15

Use Excel's curve-fitting power to find a trend line for the existing points and then forecast 5 years into the future.

Figure 10.15

Use Excel's curve-fitting power to find a trend line for the existing points and then forecast 5 years into the future.

Excel Curve Fitting
0 0

Post a comment