Displaying a regression trendline is an available style option for the line, vertical timed bar, spline, and with the line and timed bar options in the combo chart.
What is linear regression analysis?
Linear regression analysis is used to predict the value of a variable based on the value of another variable.
How do we calculate the regression line?
Calculating a regression trend line involves finding the line that minimizes the sum of the squared differences between the observed values and the predicted values. This line is determined using a statistical technique called linear regression.
Using the x and y coordinates that are generated to plot the chart, we find the average of all x values(x̄) and the average of all y values(ȳ).
For each data point, we find the difference between the x and y values and their corresponding averages. (x - x̄) and (y - ȳ)
Each of these differences are then multiplied together for each data point. (x - x̄) * (y - ȳ)
Next the difference between the x(x) value and the average value for x(x̄) is squared for each data point. (x - x̄)²
Sum all the values for each data point for the multiplied difference (Σ((x - x̄)(y - ȳ)))
Sum the squared difference between the x(x) value and the average value for x(x̄) for all data points (Σ((x - x̄)²)
We then divide the sum of the multiplied difference and the sum of the squared difference which calculates the slope of the line. m = Σ((x - x̄)(y - ȳ)) / Σ((x - x̄)²)
Next, we calculate the y intercept using the the following formula, b = ȳ - (m * x̄), where the average x̄ and ȳ values are used, and the slope is used for m.
This gives us the first point of the line, [0,b].
Using the slope-intercept formula for a straight line, y = mx + b, we can plug in the largest x value and calculate the end point for the line, [ x , y = mx + b ]
