Moving all data points closer to the trend line on a graph

Short but useless answer -- yes such a thing is possible.

How much of the question is specifically Excel, and how much is broader statistics/math/geometry? We are usually pretty good with Excel programming questions, but we don't always have the statistics/match/geometry expertise that I find is so important before you get the programming question. If you can help us understand the algorithm you want to use, we should be able to help you program that into Excel.

If this is more of a statistics/math/geometry question, I would wonder exactly what you want to do. A statistical purist might wonder if it is even appropriate to "massage" the data just to make the R^2 value look better. To such a purist, the only correct way to improve R^2 is to examine the experimental protocol and figure out how to reduce the experimental error.

When you talk about moving a data point 10% closer, what exactly do you have in mind? I could visualize some variation of "compute the perpendicular distance between data point and trendline, then compute 90% of that distance, then compute a new point along the same perpendicular but 10% closer." Is that the kind of geometry problem you envision for this, or do you have a different way of visualizing distance and closer? I expect that most of the work for this kind of algorithm is finding formulas for "perpendicular distance" and "finding a point that is a certain perpendicular distance from a line". I expect a geometry text or tutorial would have such formulas.

Help us understand exactly what you are trying to do, and we should be able to help you program that into Excel.

Here's How I'd do it. It follows the logic of Mr Shorty essentially too in that it takes the perpendicular distance to the line and moves the point a percentage closer.

If you change D4 you'll see the orange dots move closer.

0% has them on the same point as the blue dots, -100% has them on the line of best fit and -10% for example moves them 10% closer form the blue dot point of origin. If you get large variances in the x and y scales on the chart it may look like they aren't moving perpendicular but I think that is a function of the skewed scales

I obviously cannot speak for Crooza, but his solution is exclusively using basic arithmetic operators (+,*,^,etc.), so i expect that most of the referencing was to geometry/algebra texts/tutorials (or previous learning in those fields). Once the basic geometry/algebra/arithmetic is recalled, then it is a simple matter to formulate those arithmetic expressions into spreadsheet formulas.

So in a way, we'd be dealing with a vector,

In many ways, I would agree that this sort of thing can be more easily thought of in terms of vector algebra. Unfortunately, spreadsheets generally don't have good support for vector algebra*. Often, when I want to use vector algebra for a problem, I end up doing the vector algebra outside of Excel, reduce the final solution to the resulting scalar arithmetic expressions, then enter those arithmetic expressions into Excel.

So basically, I would like each point moved 1.4142 degrees ( Sqrt[1^2 + 1^2] ) in the perpendicular direction towards the trend line.

Doesn't this assume that the slope of the perpendiculars is exactly -1 (which maybe assumes that the slope of the regression line is +1)? I note that the slope of the regression line (0.9735) is close to 1. Shouldn't the actual movement take into account the actual slope of the perpendicular and regression lines (movement will not be exactly 1° in each direction)? Or, if the "theoretical" slope of the regression line should be close to 1, should the analysis begin by forcing the slope of the regression line to be exactly 1?

* -- If you can express your vector algebra problem as complex numbers, spreadsheets do have a battery of functions to work with complex numbers (most of them begin with IM...(), such as the IMSUM() function). A vector problem where the vectors are represented in the complex plane can sometimes be nicely worked out in a spreadsheet using those functions. For example:
1) Each point can be represented as a complex number roll+yaw*i [In Excel, =COMPLEX(roll,yaw)]
2) The "movement" vector 1 degree in each direction can be expressed as the complex number 1-i [in Excel, COMPLEX(1,-1)] (You could also make this step more complex so that it takes into account the slope of the regression line to get the desired movement vector)
3) The "moved" point then becomes the simple sum of those complex numbers roll2+yaw2*i=roll+yaw*i+1-i [in Excel =IMSUM(COMPLEX(roll,yaw),COMPLEX(1,-1))]
4) In order to chart the points, you will need to separate the real and imaginary parts of the complex number using the REAL() and IMAGINARY() functions.

At this point, the problem feels more like a math/geometry/algebra problem. Once we understand exactly how you want to do the math/algebra, maybe reduced to arithmetic expressions, then we can work on programming those arithmetic expressions into Excel.

Forum doesn't want to let me edit my post today -- note that I erred. The function for the real portion of a complex number is IMREAL().

I simply used geometry. The Linest function gives you the slope (or gradient) and the y-intercept of the line of best fit. Although the chart already had this I set this up in cells D2 and E2 so it is now dynamic and will change if the data set changes.

The closest distance from a point to a line is the perpendicular distance. The gradient of a perpendicular line will be the negative inverse of the line it is perpendicular to! We know the gradient of the line of best fit is 0.97 so the gradient of the perpendicular line to that is -1/0.97.

If you know a point and the gradient of a line through that point you can create a formula for the line in the form of y = gradient *x + the y-intercept.

For each point I calculate the y intercept for the perpendicular line through that point (column F). Once I have two formulas for the two lines I can find where those to lines intersect and that will be the point on the line of best fit which is closest to the data point being investigated.

In essence then I have two formulas with two unknowns (the x and y coordinate).

With two formulas and two unknown I can solve for each of these. ( I can show you the arithmetic if you're really interested) but for now I've jumped to the answer

the x coordinate is equal to the gradient times (the y intercept of the perpendicular line - y intercept of the line of best fit) divided by the (gradient squared + 1)
or
x = m(b-c)/(m^2+1)
once I know the x I can substitute into the line of best fit formula to get y

From there all I did was proportionally move the point the appropriate percentage point closer to the line (ie between the data point and the intersect point) depending on the percentage entered into D4.

How much do you need help with?

The basic geometry is something that is typically taught in secondary school. Tutorials abound around the internet:
https://www.basic-mathematics.com/di...nd-a-line.html
https://www.basic-mathematics.com/di...nd-a-line.html
And Wikipedia, which probably has more detail than is needed: https://en.wikipedia.org/wiki/Distan...oint_to_a_line

Since I am focusing on approaching this with complex numbers, I figure there are still some details to figure out, but the overall procedure that I have outlined should be good. Is there a particular part of what I outlined that you are having trouble understanding or implementing?

I've attached a photo of my working. Hope it helps. I need to read through Mr Shorty's explanation above again. I understand real and complex numbers but not sure I understand the 1 degree thing yet. I glossed over it on my first reading.

As explained in the help file for the LINEST() function (https://support.office.com/en-us/art...a-fa7abf772b6d ), you can force the regression through the origin (force b to 0) by putting FALSE/0 in for the optional third argument =LINEST($A$3:$A$34,$B$3:$B$34,0,0)