How To Find Trendline Equation In Excel

Excel for Microsoft 365 Word for Microsoft 365 Outlook for Microsoft 365 PowerPoint for Microsoft 365 Excel for Microsoft 365 for Mac Discussion for Microsoft 365 for Mac PowerPoint for Microsoft 365 for Mac Excel 2021 Word 2021 Outlook 2021 PowerPoint 2021 Excel 2021 for Mac Word 2021 for Mac PowerPoint 2021 for Mac Excel 2019 Word 2019 Outlook 2019 PowerPoint 2019 Excel 2019 for Mac Word 2019 for Mac PowerPoint 2019 for Mac Excel 2016 Word 2016 Outlook 2016 PowerPoint 2016 Excel 2016 for Mac Word 2016 for Mac PowerPoint 2016 for Mac Excel 2013 Word 2013 Outlook 2013 PowerPoint 2013 Excel 2010 Word 2010 Outlook 2010 PowerPoint 2010 Excel 2007 Excel for Mac 2011 Give-and-take for Mac 2011 PowerPoint for Mac 2011 Excel Starter 2010 More...Less

This topic covers the different trendline options that are available in Office.

Utilize this type of trendline to create a best-fit direct line for simple linear information sets. Your data is linear if the blueprint in its information points looks like a line. A linear trendline unremarkably shows that something is increasing or decreasing at a steady rate.

A linear trendline uses this equation to summate the to the lowest degree squares fit for a line:

Linear

where thou is the slope and b is the intercept.

The post-obit linear trendline shows that fridge sales have consistently increased over an 8-twelvemonth period. Notice that the R-squared value (a number from 0 to one that reveals how closely the estimated values for the trendline correspond to your actual data) is 0.9792, which is a good fit of the line to the data.

Scatter chart with a linear trendline

Showing a best-fit curved line, this trendline is useful when the charge per unit of change in the data increases or decreases quickly and so levels out. A logarithmic trendline tin can utilise negative and positive values.

A logarithmic trendline uses this equation to calculate the least squares fit through points:

Logarithmic

where c and b are constants and ln is the natural logarithm part.

The following logarithmic trendline shows predicted population growth of animals in a stock-still-space area, where population leveled out as space for the animals decreased. Notation that the R-squared value is 0.933, which is a relatively good fit of the line to the data.

Scatter chart with a logarithmic trendline

This trendline is useful when your data fluctuates. For case, when you analyze gains and losses over a large data set. The order of the polynomial tin can exist determined by the number of fluctuations in the data or by how many bends (hills and valleys) appear in the curve. Typically, an Order ii polynomial trendline has only 1 hill or valley, an Order 3 has i or ii hills or valleys, and an Guild 4 has up to 3 hills or valleys.

A polynomial or curvilinear trendline uses this equation to calculate the least squares fit through points:

Polynomial

where b and c1..c6 are constants.

The following Club 2 polynomial trendline (one hill) shows the relationship between driving speed and fuel consumption. Notice that the R-squared value is 0.979, which is shut to 1 and then the line'southward a expert fit to the data.

Scatter chart with a polynomial trendline

Showing a curved line, this trendline is useful for data sets that compare measurements that increase at a specific rate. For case, the acceleration of a race car at 1-second intervals. You cannot create a power trendline if your information contains naught or negative values.

A power trendline uses this equation to calculate the least squares fit through points:

Power

where c and b are constants.

Note:This option is not available when your data includes negative or nix values.

The following altitude measurement chart shows distance in meters by seconds. The power trendline clearly demonstrates the increasing acceleration. Annotation that the R-squared value is 0.986, which is an almost perfect fit of the line to the data.

Scatter chart with a power trendline

Showing a curved line, this trendline is useful when information values rising or autumn at constantly increasing rates. You lot cannot create an exponential trendline if your information contains aught or negative values.

An exponential trendline uses this equation to summate the to the lowest degree squares fit through points:

Exponential

where c and b are constants and e is the base of the natural logarithm.

The following exponential trendline is shows the decreasing corporeality of carbon 14 in an object every bit information technology ages. Note that the R-squared value is 0.990, which means the line fits the information almost perfectly.

Chart with an exponential trendline

This trendline evens out fluctuations in data to show a blueprint or trend more conspicuously. A moving average uses a specific number of data points (set past the Period option), averages them, and uses the average value every bit a point in the line. For example, if Period is set to 2, the average of the start ii data points is used as the outset point in the moving average trendline. The average of the second and third data points is used every bit the second indicate in the trendline, etc.

A moving boilerplate trendline uses this equation:

MovingAverage

The number of points in a moving average trendline equals the full number of points in the serial, minus the number y'all specify for the menstruum.

In a scatter chart, the trendline is based on the order of the x values in the chart. For a better result, sort the x values earlier you add together a moving boilerplate.

The following moving average trendline shows a pattern in the number of homes sold over a 26-week flow.

Scatter chart with a moving average trendline

Of import:Start with Excel version 2005, Excel adjusted the way it calculates theR² value for linear trendlines on charts where the trendline intercept is prepare to zero (0). This adjustment corrects calculations that yielded wrong R² values and aligns the Rⁱⁱ adding with the LINEST function. As a result, you may see dissimilarR² values displayed on charts previously created in prior Excel versions. For more information, meet Changes to internal calculations of linear trendlines in a nautical chart.