How can I project X,Y coordinates to the two most distant points in Excel

Question

I am working with a set of data points collected with a GNSS receiver that are reported in X,Y (northing, Easting) format. The values in between the most distant points are not always in a straight line but for proper distancing I need to do a little math. I need to project all of the middle points to the straight line between the two most distant points. I have been working with a formula. To help visualize the data, I've attached an image of sample points collected. Imagine drawing a line segment between the two end points then projecting all other points to that location and creating a new x,y value for the projected point. That new x,y is ultimately what I'm looking to create. From there I can compute everything I need.

So does anyone have a suggestion on how to handle this in Excel? I know it could be done in Python with minimal effort but I want to integrate this into an existing Excel tool with out having to recreate everything.

Thanks ahead of time for any help, thought you have.

Point ID,northing,easting
0,1035968.533,2345983.331
1,1035982.422,2345983.331
2,1035994.228,2345982.637
3,1036006.728,2345980.554
4,1036017.839,2345976.387
5,1036037.978,2345977.081
6,1036046.311,2345984.72
7,1036057.422,2345993.054
8,1036069.922,2346001.387
9,1036089.367,2346002.081
10,1036103.256,2346002.081
11,1036116.45,2346012.498
12,1036129.644,2346011.109
13,1036147.006,2346004.165
14,1036158.811,2345998.609
15,1036176.867,2345993.748
16,1036188.672,2345993.054
17,1036205.339,2345990.276
18,1036215.061,2345981.942
19,1036230.339,2345984.72
20,1036240.756,2345995.831
21,1036256.728,2345999.998
22,1036272.006,2346000.692
23,1036285.2,2345993.054
24,1036301.172,2345988.887
25,1036319.922,2345985.415
26,1036331.033,2345979.165
27,1036344.922,2345976.387
28,1036359.506,2345970.831
29,1036372.7,2345956.248

Answer 1

Here is a first cut at this - just doing a shift by subtracting the first point from all the points then a rotation using a standard formula

where theta (a named range $E$2) is calculated by

=ATAN2(C31-C2,B31-B2)

where C2 and C31, B31 are the first and last Easting and Northing

and the other two formulas are

=G2*COS(Theta)+F2*SIN(Theta)

and

=-G2*SIN(Theta)+F2*COS(Theta)

If the results look on the right lines, this can be tidied up. There is more work to do for the cases where Theta is greater than Pi radians (which means the points are heading south i.e. the final northing is less than the initial northing), or the line is exactly at right angles to the x-axis.

In Excel 365, the whole thing can be done as a spill formula using Let() to calculate the intermediate steps.