Excel trendline types

# How to choose a trend line

## The 5 Different Trend Lines Explained

The problem was to determine the relationships between how long it takes water to drain from a hole in the bottom of a bucket, the diameter of this hole, and the initial height of water in the bucket.

The data is shown in this table.

The first step is to plot the data using XY charts. The charts below show time to drain vs height for various hole sizes and time to drain vs hole size for various diameters.

### Excel trendline types, equations and formulas

We will investigate various trendline formulas using these plots. In Trendline Fitting Errors I warned about picking an appropriate type of trendline relationship.

Updated Nov 16, What Is a Trendline? Trendlines are easily recognizable lines that traders draw on charts to connect a series of prices together or show some data's best fit. The resulting line is then used to give the trader a good idea of the direction in which an investment's value might move.

How might I go about fitting trendlines to the data. The original data are shown with points connected by dotted lines, and the trendlines by solid lines.

Since there is curvature in the data, we need a higher power of the X variable. These two charts show polynomial fits of order 2.

## Choosing a Trendline Type

The fits come close to fitting all of the points, but the curve for height seems to be approaching a maximum at a height of 30, implying that if we double the height, the time will decrease.

The curve for diameter is even stranger, with a minimum just above 4; this implies that if we widen the hole further, the water will leak out more slowly. Both of these scenarios are rather unlikely. Fortunately with only four points, we will not be able to go beyond this.

### Trendline & R²

In the height relationship, the poly fit predicts a slowing of the increase of time with height, then after about 20 it accelerates. The diameter relationship actually changes direction twice. A simpler relationship is how to choose a trend line with fewer fitting parameters, and which follows the form of any theoretical description of the phenomenon.

A great many physical phenomena follow power law relationships. The following show power law fits to the data.

Time to drain the bucket follows the reciprocal of diameter to the second power. This fits nicely since the volume of water passing through the hole is proportional to the area of the hole, which is proportional to the diameter squared.

The time is obviously additive as more water is added to the initial height.

Also, the added water should push harder on the water beneath, forcing it out faster. An equal increment of water height will produce a decreasing increment of time. Below is another view of the power law fits utr der binary option a double logarithmic scale on which a power how to choose a trend line fit traces a straight line.

The visual goodness of fit lets me assert that the power law fits are appropriate. Trendline Articles on this Web Site.

Useful posts