A linear equation is any equation that can be written in the form ax + b = 0 where (a ) and (b ) are real numbers and (x ) is a variable. This form is sometimes called the standard form of a linear equation. Note that most linear equations will not start off in this form.
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. 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 typesWhen adding a trendline in Excel, you have 6 different options to choose from. Additionally, Microsoft Excel allows 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 R 2 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 trendlineThe 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 R 2 value of 0.9855 indicates a pretty good fit of the estimated trendline values to the actual data.Exponential trendlineThe 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 trendlineThe 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 trendlineThe 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. Y = bx + aWhere:. 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 trendlineb: =SLOPE(y,x)Y-intercepta: =INTERCEPT(y,x)Assuming the x range is B2:B13 and the y range is C2:C13, the real-life formulas go as follows:=SLOPE(C2:C13, B2:B13)=INTERCEPT(C2:C13,B2:B13)The same results can be achieved by using the 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:=LINEST(C2:C13,B2:B13)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 formulasFor the exponential trendline, Excel uses the following equation. Y = ae bxWhere 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: =EXP(INDEX(LINEST(LN(y), x), 1, 2))b: =INDEX(LINEST(LN(y), x), 1)For our sample data set, the formulas take the following shape:a: =EXP(INDEX(LINEST(LN(C2:C13), B2:B13), 1, 2))b: =INDEX(LINEST(LN(C2:C13), B2:B13), 1)Logarithmic trendline equation and formulasHere's the logarithmic trendline equation in Excel. Y = ax bWhere a and b are constants, which can be calculated with these formulas:a: =EXP(INDEX(LINEST(LN(y), LN(x),), 1, 2))b: =INDEX(LINEST(LN(y), LN(x),), 1)In our case, the following formulas work a treat:a: =EXP(INDEX(LINEST(LN(C2:C13), LN(B2:B13),), 1, 2))b: =INDEX(LINEST(LN(C2:C13), LN(B2:B13),), 1)Excel trendline equation is wrong - reasons and fixesIf 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 chartsExcel trendline formulas should only be used with because only in this chart type both the y-axis and x-axis are plotted as numeric values.In, column and, 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 equationTo 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. 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! You may also be interested in:.
Comments are closed.
|
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |