Getting back to our Linear equation, y = m*x+b, the equations for the coefficients m & b are: For the rest of the examples below, there are no built-in formulas.įor each type of trend line, I will present the coefficients, the equation to calculate each coefficient and then the calculated value based on the following data set: X To calculate these coefficients, Excel has built-in formulas, SLOPE & INTERCEPT. The coefficients for this equation are m & b where m is the slope of the line and b is where the line intercepts the y-axis. The equation for a linear line is y = m*x+b. The most simple of trend lines is to “fit” a linear line to your data. This post will assume that you know your x values and are trying to forecast your y values. Rarely will you have to forecast both x & y. Typically, you will have x or y values and need to calculate what the corresponding x or y values are. Please note that this post will not show you how to figure out which trend line is best for forecasting future data, however we will discuss figuring out which trend line best correlates to current data. However, this post will show you how to manually calculate all of them should you want to forecast a trend line past the known data set to calculate predicted values for x & y. Excel graphs can automatically add trend lines based on all of these trend methods.

I will show examples of the trended data graphed against the original data. Thank you for your time and help in advance.This post is on how to manually calculate trend lines in Excel. My question: Why does the equation, which is based on the values from the sensor, give a different result than expected if I plug in the numbers manually? Am I missing something?įor example, in the image below if you plug in 7ml instead of x you get a value of about 45 but the actual value on the trendline is somewhere near 57. Strangely, if I eye-ball it, it gives a value similar to the sensor readings.

Now, although it is a linear relationship as shown in the image below, if I try to find the expected sensor reading or volume by plugging in the number in the equation, I get a totally different number than the expected output from the graph and from the sensor output. After collecting 5 data points for each volume ( 0ml, 5ml, 10ml.), I found the average and plotted the sensor readings vs. I am recording data readings from a sensor based on the water volume in a tank. I apologize if this question should be somewhere else but I could not find a seperate section for excel related questions.

Excel equation of trendline