How do I calculate the distance from a linear trend in a line chart that goes up and down but steadily increasing?

The simplest way (if the numbers are positive and you have a good regression line) is to calculate the y value on the line for the x value of the point.

The vertical distance between the line is y(line) - y(point).

That is one way and that is not the only way.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

=TREND((known_y's, [known_x's], [new_x's], [const]), whereknown_y'sis the range containing the y-valuesknown_x'sis the range containing the x-values (optional if x is understood to be 1,2,3,4...)new_x'sis the range containing the "new" x-values to use for calculating the given linear trend pointconstis TRUE is forcing the formula to intercept at y=0In your case, you'll use your existing data for all the x and y values, then use each individual value of x to produce the linear trend value. From there you just substract that value from the original y-value to get the delta (distance).

See this example of how you'd do it:

Note the formula entered in cell C2 (and copied down)

=TREND($B$2:$B$11,$A$2:$A$11,A2,TRUE) You can see from the chart and linear trend on it the the deltas agree with what's seen visually. Example file attached.,

Regards,

-Glenn

Microsoft Reference: TREND function

EE-TrendExample.xlsx