# Excel polynomial trendline

When you want to add a trendline to a chart in Microsoft Graph, you can choose any of the six different trend/regression types. The type of data you have determines the type of trendline you should use.

Trendline reliability A trendline is most reliable when its R-squared value is at or near 1. When you fit a trendline to your data, Graph automatically calculates its R-squared value. If you want, you can display this value on your chart.

Linear

A linear trendline is a best-fit straight line that is used with simple linear data sets. Your data is linear if the pattern in its data points resembles a line. A linear trendline usually shows that something is increasing or decreasing at a steady rate.

In the following example, a linear trendline clearly shows that refrigerator sales have consistently risen over a 13-year period. Notice that the R-squared value is 0.9036, which is a good fit of the line to the data.

Logarithmic

A logarithmic trendline is a best-fit curved line that is most useful when the rate of change in the data increases or decreases quickly and then levels out. A logarithmic trendline can use negative and/or positive values.

The following example uses a logarithmic trendline to illustrate predicted population growth of animals in a fixed-space area, where population leveled out as space for the animals decreased. Note that the R-squared value is 0.9407, which is a relatively good fit of the line to the data.

Polynomial

A polynomial trendline is a curved line that is used when data fluctuates. It is useful, for example, for analyzing gains and losses over a large data set. The order of the polynomial can be determined by the number of fluctuations in the data or by how many bends (hills and valleys) appear in the curve. An Order 2 polynomial trendline generally has only one hill or valley. Order 3 generally has one or two hills or valleys. Order 4 generally has up to three.

The following example shows an Order 2 polynomial trendline (one hill) to illustrate the relationship between speed and gasoline consumption. Notice that the R-squared value is 0.9474, which is a good fit of the line to the data.

Power

A power trendline is a curved line that is best used with data sets that compare measurements that increase at a specific rate — for example, the acceleration of a race car at one-second intervals. You cannot create a power trendline if your data contains zero or negative values.

In the following example, acceleration data is shown by plotting distance in meters by seconds. The power trendline clearly demonstrates the increasing acceleration. Note that the R-squared value is 0.9923, which is a nearly perfect fit of the line to the data.

Exponential

An exponential trendline is a curved line that is most useful when data values rise or fall at increasingly higher rates. You cannot create an exponential trendline if your data contains zero or negative values.

In the following example, an exponential trendline is used to illustrate the decreasing amount of carbon 14 in an object as it ages. Note that the R-squared value is 1, which means the line fits the data perfectly.

Moving average

A moving average trendline smoothes out fluctuations in data to show a pattern or trend more clearly. A moving average trendline uses a specific number of data points (set by the Period option), averages them, and uses the average value as a point in the trendline. If Period is set to 2, for example, then the average of the first two data points is used as the first point in the moving average trendline. The average of the second and third data points is used as the second point in the trendline, and so on.

In the following example, a moving average trendline shows a pattern in number of homes sold over a 26-week period.

Sours: https://support.microsoft.com/en-us/office/choosing-the-best-trendline-for-your-data-1bb3c9e7-0280-45b5-9ab0-d0c93161daa8

### Polynomial

Creates a curved line illustrating fluctuations in the data values.
This trendline has an additional parameter

You can specify an order value to indicate the maximum number of fluctuations or bends that occur on the line.
A polynomial trendline is a curved line that is used when data fluctuates.
It is useful, for example, for analyzing gains and losses over a large data set.
The order of the polynomial can be determined by the number of fluctuations in the data or by how many bends (hills and valleys) appear in the curve.
An Order 2 polynomial trendline generally has only one hill or valley.
Order 3 generally has one or two hills or valleys. Order 4 generally has up to three.

A polynomial or curvilinear trendline by using the following equation to calculate the least squares fit through points:
y=b + c1x + c2x2 + c3x3 +
where c1, c2, c3, are constants

2nd Order Polynomial Trendline
Equation: y = (c2 * x^2) + (c1 * x ^1) + b
c2: =INDEX(LINEST(y,x^{1,2}),1)
C1: =INDEX(LINEST(y,x^{1,2}),1,2)
b = =INDEX(LINEST(y,x^{1,2}),1,3)

3rd Order Polynomial Trendline
Equation: y = (c3 * x^3) + (c2 * x^2) + (c1 * x^1) + b
c3: =INDEX(LINEST(y,x^{1,2,3}),1)
c2: =INDEX(LINEST(y,x^{1,2,3}),1,2)
C1: =INDEX(LINEST(y,x^{1,2,3}),1,3)
b: =INDEX(LINEST(y,x^{1,2,3}),1,4)

Notice the pattern in the two preceding sets of formulas

Type the highest power for the independent variable in the Order box

#### Trendline Options

Sours: https://bettersolutions.com/excel/charts/trendlines-polynomial.htm

## Excel trendline types, equations and formulas

In this tutorial, you will find the detailed description of all the trendline options available in Excel and when to use them. You will also learn how to display a trendline equation in a chart and find the slope of trendline.

It is very easy to add a trendline in Excel. The only real challenge is to choose the trendline type that best corresponds to the type of data you are analyzing. In this tutorial, you will find the detailed description of all the trendline options available in Excel and when to use them. If you are looking for how to insert a trendline in an Excel chart, please check out the above linked tutorial.

### Excel trendline types

When adding a trendline in Excel, you have 6 different options to choose from. Additionally, Microsoft Excel allows displaying a trendline equation and R-squared value in a chart:

• Trendline equation is a formula that finds a line that best fits the data points.
• R-squared value measures the trendline reliability - the nearer R2 is to 1, the better the trendline fits the data.

Below, you will find a brief description of each trendline type with chart examples.

### Linear trendline

The linear trend line is best to be used with linear data sets when the data points in a chart resemble a straight line. Typically, a linear trendline describes a continuous rise or fall over time.

For example, the following linear trendline shows a steady increase in sales over 6 months. And the R2 value of 0.9855 indicates a pretty good fit of the estimated trendline values to the actual data.

### Exponential trendline

The exponential trendline is a curved line that illustrates a rise or fall in data values at an increasing rate, therefore the line is usually more curved at one side. This trendline type is often used in sciences, for example to visualize a human population growth or decline in wildlife populations.

Please note that an exponential trendline cannot be created for data that contains zeros or negative values.

A good example of an exponential curve is the decay in the entire wild tiger population on the earth.

### Logarithmic trendline

The logarithmic best-fit line is generally used to plot data that quickly increases or decreases and then levels off. It can include both positive and negative values.

An example of a logarithmic trendline may be an inflation rate, which first is getting higher but after a while stabilizes.

### Polynomial trendline

The polynomial curvilinear trendline works well for large data sets with oscillating values that have more than one rise and fall.

Generally, a polynomial is classified by the degree of the largest exponent. The degree of the polynomial trendline can also be determined by the number of bends on a graph. Typically, a quadratic polynomial trendline has one bend (hill or valley), a cubic polynomial has 1 or 2 bends, and a quartic polynomial has up to 3 bends.

When adding a polynomial trendline in an Excel chart, you specify the degree by typing the corresponding number in the Order box on the Format Trendline pane, which is 2 by default:

For example, the quadratic polynomial trend is evident on the following graph that shows the relationship between the profit and the number of years the product has been on the market: rise in the beginning, peak in the middle and fall near the end.

### Power trendline

The power trend line is very similar to the exponential curve, only it has a more symmetrical arc. It is commonly used to plot measurements that increase at a certain rate.

A power trendline cannot be added to an Excel chart that contains zero or negative values.

As an example, let's draw a power trendline to visualize the chemical reaction rate. Note the R-squared value of 0.9918, which means that our trendline fits the data almost perfectly.

### Moving average trendline

When the data points in your chart have a lot of ups and downs, a moving average trendline can smooth the extreme fluctuations in data values to show a pattern more clearly. For this, Excel calculates the moving average of the number of periods that you specify (2 by default) and puts those average values as points in the line. The higher the Period value, the smoother the line.

A good practical example is using the moving average trendline to reveal fluctuations in a stock price that otherwise would be difficult to observe.

### Excel trendline equations and formulas

This section describes the equations that Excel uses for different trendline types. You do not have to build these formulas manually, simply tell Excel to display the trendline equation in a chart.

Also, we will discuss the formula to find the slope of a trendline and other coefficients. The formulas assume that you have 2 sets of variables: independent variable x and dependent variable y. In your worksheets, you can use these formulas to get the predicted y values for any given values of x.

For consistency, we will be using the same data set with slightly varying values for all the examples. However, please keep in mind that it's only for demonstration purposes. In your real worksheets, you should choose the trendline type corresponding to your data type.

Important note! The trendline formulas should only be used with XY scatter charts because only this chart plots both x and y axes as numeric values. For more information, please see Why Excel trendline equation may be wrong.

### Linear trendline equation and formulas

The linear trendline equation uses the least squares methods to seek the slope and intercept coefficients such that:

Where:

• b is the slope of a trendline.
• a is the y-intercept, which is the expected mean value of y when all x variables are equal to 0. On a chart, it's the point where the trendline crosses the y axis.

For linear regression, Microsoft Excel provides special functions to get the slope and intercept coefficients.

Slope of trendline
b:

Y-intercept
a:

Assuming the x range is B2:B13 and the y range is C2:C13, the real-life formulas go as follows:

The same results can be achieved by using the LINEST function as an array formula. For this, select 2 adjacent cells in the same row, enter the formula and press Ctrl + Shift + Enter to complete it:

As shown in the screenshot below, the slope and intercept coefficients returned by the formulas are perfectly in line with the coefficients in the linear trendline equation displayed in the chart, only the latter are rounded to 4 decimal places:

### Exponential trendline equation and formulas

For the exponential trendline, Excel uses the following equation:

Where a and b are calculated coefficients and e is the mathematical constant e (the base of the natural logarithm).

The coefficients can be calculated by using these generic formulas:

a:

b:

For our sample data set, the formulas take the following shape:

a:

b:

### Logarithmic trendline equation and formulas

Here's the logarithmic trendline equation in Excel:

Where a and b are constants and ln is the natural logarithm function.

To get the constants, use these generic formulas, which only differ in the last argument:

a:

b:

For our sample data set, we use these ones:

a:

b:

### Polynomial trendline equation and formulas

To work out the polynomial trendline, Excel uses this equation:

y = b6x6 + … + b2x2 + b1x + a

Where b1b6 and a are constants.

Depending on the degree of your polynomial trendline, use one of the following sets of formulas to get the constants.

#### Quadratic (2nd order) polynomial trendline

Equation: y = b2x2+ b1x + a

b2:

b1:

a:

#### Cubic (3rd order) polynomial trendline

Equation: y = b3x3 + b2x2+ b1x + a

b3:

b2:

b1:

a:

The formulas for higher degree polynomial trendlines can be built by using the same pattern.

For our data set, the 2nd order polynomial trendline suites better, so we are using these formulas:

b2:

b1:

a:

### Power trendline equation and formulas

A power trendline in Excel is drawn based on this simple equation:

Where a and b are constants, which can be calculated with these formulas:

a:

b:

In our case, the following formulas work a treat:

a:

b:

### Excel trendline equation is wrong - reasons and fixes

If you think that Excel has drawn a trendline incorrectly or the trendline formula displayed in your chart is wrong, the following two points may shed some light on the situation.

### Excel trendline equation is correct only in scatter charts

Excel trendline formulas should only be used with XY (scatter) graphs because only in this chart type both the y-axis and x-axis are plotted as numeric values.

In line charts, column and bar graphs, numeric values are plotted only on the y-axis. The x-axis is represented by a linear series (1, 2, 3,…) regardless of whether the axis labels are numbers or text. When you make a trendline in these charts, Excel uses those assumed x-values in the trendline formula.

### Numbers are rounded in Excel trendline equation

To occupy less space in the chart, Excel displays very few significant digits in a trendline equation. Nice in terms of design, it significantly reduces the formula's accuracy when you manually supply x values in the equation.

An easy fix is to show more decimal places in the equation. Alternatively, you can calculate the coefficients by using a formula corresponding to your trendline type, and format the formula cells so that they show a sufficient number of decimal places. For this, simply click the Increase Decimal button on the Home tab in the Number group.

That's how you can make different trendline types in Excel and get their equations. I thank you for reading and hope to see you on our blog next week!

## Nonlinear Curve Fitting in Excel Using Charts

In engineering, you’ll encounter data than doesn’t follow a linear trend. In those cases, you’ll need to use nonlinear methods. Excel has a few different options for fitting these curves.

The worksheet contains pressure and flow data for a valve in a piping system. Select the data and create an XY scatter chart. You’ll see that the data is non-linear:

Excel is able to add trendlines to non-linear curves. Right-click the data series and select Add Trendline. The Format Trendline task pane will open:

The default trendline uses a linear equation, but there are several other options. If you aren’t sure what equation fits the data best, you can click through the equation types to find the right one.

Before doing that, scroll down in the task pane and select Display Equation on Chart. This way you can see the equation of the lines that each fit uses. Also check the box beside Display R-squared value on the chart.

Click through each of the trendline options. You’ll find that exponential, linear, and logarithmic are visibly poor fits. The polynomial and power trendlines are better fits. The polynomial trendline fits the curve to the general equation y = ax2 + bx + c. Excel will give you values for the coefficients a, b and c:

The power trendline fits the data to the equation y = axb, outputting values for a and b. It’s also a fairly good fit:

However, you may be able to guess from looking at the trendlines that the polynomial equation fits the data slightly better. This is confirmed by examining the R2 values (0.998 for the polynomial fit versus 0.986 for the power fit).

The trendline tool in Excel will calculate polynomials up to sixth order. You can increase the order of the polynomial using the box beside the polynomial option. However, in this case, a higher order doesn’t have much effect, if any, on the R2 value, so stick with the second order polynomial.

The governing equation for pressure drop through an orifice involves a squared term, so fitting to a second-order polynomial equation makes logical sense.

Be careful about using high order polynomials if you have limited data points. For example, if you have only five data points, don’t select a fourth order polynomial. It will fit the data exactly, but won’t tell you much about the general trend of the data. It’ll also be inaccurate outside of the bounds of the data that you used as inputs.

Sours: https://engineerexcel.com/nonlinear-curve-fitting-in-excel-using-charts/

## Formula to match Polynomial trendline

This is brilliant - thank you very much - With the MS page open on the syntax of the formula It explains better than all google findings, which ironically all mentioned scatters like you allude to in your comments
Thanks too, for the comments - ironically I had first tried using a "days count" in a row below the "mon-yy", but selected the wrong column when tapping in the formula. Seeing results and thinking these are just serial dates anyways, I went with it
I have for many years used a date on a graph axis and trendlines without problem, but will bear in mind your experiences.

I was a bit stumped about how and why the "three" coefficients (the maths approach is a bit over my head tbh), but looking at the trend label, the penny dropped!
I'm guessing we could use the same thinking for a exponential trend line (I had put in a manually entered cell) but better we can link to a calculated one

You have no idea how much time I have spent in researching this and the only thing I found was an increasing sense of overwhelming and highlights of my lack of maths knowledge.
Embarrassingly, I did have a very large stretch graph where I visually took a guestimate about where the monthly intersections lay, surprisingly ridiculously close to these calculated one so I've every confidence this is bang on !! Cheers joeu2004

Sours: https://www.excelforum.com/
Linear and Polynomial Regression in Microsoft Excel
Introduction

The term trends implies a change over time. One type of forecasting is quantitative, and involves analyzing time-series data, and then predicting what the future might be.

For example, sales at an ice cream stand at the town park in June of each of the last five years has been good, but in July it was about 20% more than in June. If this year, the stand took in \$10,000 in June (a new record), how much would you  predict it will take in in July?  Well, if we were correct in our assumption based on the historical data, we'd estimate the July figure would be 20% higher, or \$12,000.

Microsoft Excel offers some built-in tools for forecasting. One of these allows you to add a trendline to existing data points on a chart. This allows the user to interpolate (i.e., to find a data point between existing points) or to extrapolate (i.e., to find a data point past either end of the current data, either by forecasting foreword, or "backcasting" to an earlier period.)

However, as with templates, the developers of these tools have made some decisions for the user, and not all users would agree with those decisions. If the limitations imposed by Microsoft Excel's features for forecasting are inappropriate for a particular forecasting task, the reader is instead encouraged to use direct numerical manipulation using proven analytical techniques as described in any of several texts on forecasting (such as Makridakis, Wheelwright & Hyndman, 1998).

Before You Begin

This page assumes the user has Microsoft Excel™ 2010 or 2007 with the Analysis ToolPak Add-In from Microsoft installed.

Getting the Data

Let's look at some data concerning compact fluorescent lamps (CFLs) using the following as a source document:

US Department of Energy. (2009). CFL Market Profile - March 2009. Washington, DC: Author. Retrieved April 7, 2009 from http://www.energystar.gov/ia/products/downloads/CFL_Market_Profile.pdf

The analysis in this report was performed by D & R International, LTD (http://www.drintl.com/.)

On Page 2, there is a bar chart (or bar graph) that lists the number of shipments of CFLs by year up to 2007, and then predicts the number of shipments in 2008, 2009, and 2010, based on that data.

Figure 1. Bar chart from US DOE, 2009, p.2.

Let's use the data in this chart, and the power of Microsoft Excel, to make a similar prediction. Ideally, you would have the actual data values, but in this case, an estimate was made based on the above graphic and the following was entered into an Excel spreadsheet.

 Year Incand. CFLs 2000 1686 23 2001 1659 71 2002 1681 54 2003 1668 69 2004 1649 91 2005 1641 104 2006 1560 188 2007 1325 400 Est: 2008 1319 328 Est: 2009 1238 360 Est: 2010 1148 395
Table 1. Raw data.

Let's only look at the historical data for 2000 to 2007, not at the estimates or predictions for 2008 to 2010. We can recreate the bar chart shown in the source document by selecting the historical data in Excel and creating a bar chart:

Figure 2. Raw data in a bar chart to match original

But instead, let's create a scatter plot of the values (since Excel's trendline equation feature can produce errors with bar charts or line graphs.)

Figure 3. Raw data in a scatter plot.

Adding a Linear Trendline and Regression Equation

Now, remember, we are just concerned with the CFL data, and we want to be able to predict future years. In order to add a trendline, click on one of the icons representing a data point for CFLs, and then right-click and select "Add Trendline." You'll see the following dialog box.

Figure 4. Trendline options dialog box.

In this example, we will assume that the number of CFLs shipped per year increases at a steady or linear rate. For now, in the Trendline Options area, select the following

• Trend / Regression type: Linear

• Forecast - Forward 3 periods

• Display Equation on chart

After moving the equation we have:

Figure 5. Raw data with a linear trendline and regression equation.

The equation is a linear regression equation. That means that is it the equation of a straight line that best fits the points on the chart. The method Excel uses to determine this equations involves finding the line that produces the least value for the sum of the squares of the vertical differences between data points and the line. Like all lines, it has an equation in the form:

• y is the number to be calculated, the dependent variable, or in this case, the number of millions of CFLs shipped per year;

• m is the slope of the line, which equals the change in the y value divided by the change in the x value;

• x is the given data point or the dependent variable, in this case, it is the year; and

• b is the y-axis intercept of the line.

The equation, in this case, is:

That means that for the year 2010, the predicted value is

y = (40.429 * 2010) + 80874

y388 million CFLs shipped

We can substituted other values for x, such as the year 2020, and since we now have an equation, we can predict that there will be 793 million CFLs shipped in the year 2020. Of course, this is making a lot of assumptions that we shouldn't make. In particular, we are assuming that the trend is linear, and that it will continue far into the future.

Alternate Method. You can find out the equation directly from the tabled data, if you like. Select two cells like G5 and G6 and then start typing in the formula: =LINEST(range) for the range, select all of the known y values, then type the closing parenthesis, but don't hit the Enter key. Instead, hit Control-Shift-Enter. You'll see the slope and the intercept appear in these two cells.

Many trends are not linear. For example, human population on the planet was fairly linear, but then it shot up, as illustrated by the red line in the following figure:

Figure 7.  Non-linear trend of "Long-term World Population Growth." This graphic is from United Nations, 1999, p. 7.

There are several non-linear predictive equations. We'll look at two, exponential equations and polynomial equations, but you are advised to explore others.

Exponential

Let's take that same historical CFL shipment data we used above and apply some non-linear trendlines. Here is an exponential trendline. It uses an equation that has the x value (the year) as an exponent. I clicked on the new equation an selected "format trendline label" to display the equation in scientific notation with six decimal points, since the default does not give me enough precision for predicting.

Figure 8. Raw data with exponential trendline.

As we can see, there trendline is curved, not quite as much as is indicated by the relatively high 2007 datum point, but it is still curved up.

The predictive equation is:

y = 1.598767 E -279 e ^ 3.226616 E -01 x

Recall that the capital E means "Times ten to the power of" and that the lower case e is a constant approximately equal to 2.71828. In Excel, I can then type in the following formula in any cell:

=1.598767E-279*EXP(0.3226616*2010)

and by replacing "2010" with the year, get a prediction for that year. The value for 2010 is 733 million CFLs, and the value for 2012 is 1.398 billion CFLs.

Polynomial

The predictive equation can be a polynomial. We saw that the linear regression equation was

y = mx + b

A second-order, or quadratic, polynomial equation adds an x2 term, resulting in:

y = ax2 + bx + c

The graph of a quadratic equation of this form is typically a parabola. Here is the same data with a second order polynomial trendline:

Figure 9. Second order polynomial trend line with equation.

It is possible to increase the order, adding an x3, x4, or x5 term, if there is reason to believe such a curve will be more accurate.

Modifying the Data

Sometimes, we suspect the data should be modified. In our example, notice how high the value of 400 was for 2007. An analyst might have reason to believe that this point was an outlier, and due to some special circumstances, like a one-time marketing blitz, the high value of this datum is throwing off the future prediction. Let's alter the data, reducing that point to 300.

 Year Incand. CFLs 2000 1686 23 2001 1659 71 2002 1681 54 2003 1668 69 2004 1649 91 2005 1641 104 2006 1560 188 2007 1325 300 Est: 2008 1319 328 Est: 2009 1238 360 Est: 2010 1148 395
Table 2. Revised data.

Using the revised data and second-order polynomial forecasting, we get:

Figure 10. The value of 2007 was changed from 400 to 300 in the belief that this was an abnormal value.

Notice how Figure 10 is relatively close to the initial prediction shown in the original US DOE source document.

There are many ways to transform and adjust data, and in each instance the analyst should have a defensible line of reasoning that justifies the transformation.

Exercising Care

As with many forms of statistical analysis, trendline extrapolation can be subject to deliberate attempts to make the data suggest the analyst's bias. This is inappropriate. Where there are alternative projections, it is best to present them with explanations of each. For example, the following illustration shows several different paths world population might take given different conditions explained by the authors.

Figure 11. "World population according to five projection scenarios, 1950 - 2150" from United Nations, 1998, p. 4.

Other Quantitative Methods

As seen on Excel's Trendline Options dialog box, there are other types of trendlines that can be added, including a logarithmic, power, and moving average trendline. The Analysis ToolPak Add-in for Excel also has several forecasting tools. To access them, click Data Analysis in the Data tab. You will see moving average, regression, and exponential smoothing there, all of which can be used to forecast.

But don't stop there, Excel, like some other programs for numerical manipulation, allows the user to directly control the formulas used to derive values. We do not have to settle for the default settings used in the Add Trendline feature of charts, but we can instead perform the necessary calculations on the data directly.

For information on the methods in this lesson, and others, such as the Box-Jenkins method, dynamic regression, multiple regression, please consult a text on forecasting, such as the one by Makridakis, Wheelwright, & Hyndman (1998).

References

Makridakis, S., Wheelwright, S., & Hyndman, R. (1998). Forecasting: Methods and Applications. 3rd ed. New York: Wiley & Sons.

United Nations. (1998). Long-range World Population Projections: Based on the 1998 Revision. Executive Summary: Author. Retrieved April 7, 2009 from http://www.un.org/esa/population/publications/longrange/longrangeExecSum.pdf

United Nations. (1999). The World at Six Billion. New York: Author. Retrieved April 7, 2009 from http://www.un.org/esa/population/publications/sixbillion/sixbillion.htm

United States Department of Energy. (2009). CFL Market Profile - March 2009. Washington, DC: Author. Retrieved April 7, 2009 from http://www.energystar.gov/ia/products/downloads/CFL_Market_Profile.pdf

Sours: http://techweb.bsu.edu/jcflowers1/rlo/trendlines.htm

### You will also like:

I myself was on the verge of orgasm, and straightening up, saddled my husband. A chest moan escaped me as I felt how deep his cock was. He began to move, throwing me up with a movement of his hips and sharply lowering me onto his penis: Go on.

316 317 318 319 320