Linear Trendline Proximity Problem

It might be an iterative problem, it will probably depend on exactly how you decide to approach it. I think it will be even more complicated the more "decision making" that Excel does and the less that the user does.

My first thought on something like this is to make it first a statistics problem before we make it a programming problem. Since you want to minimize user input, I think you are going to want a fairly robust algorithm for detecting outliers. I would probably suggest that you start with Wikipedia (https://en.wikipedia.org/wiki/Outlier ) or any other suitable statistics text to become familiar with some of the different outlier tests. I see Grub's test frequently mentioned for testing outliers in linear regression problems, so that might be one to explore. Note that, to my knowledge, Excel does not have any of the outlier tests preprogrammed in, so you will likely need to program your own implementation of the outlier algorithm. I expect that this will be the hardest part of the overall spreadsheet. I'm sure we can help you with this, but you will probably need to be familiar enough with your desired outlier test to help us understand it enough to program it. You might also look at this website (http://www.real-statistics.com/multi...d-influencers/ ) since most of his examples are specifically for statistics in Excel.

I am not an expert in fluid mechanics, but I seem to recall that the Reynold's number was the usual quantity used to draw the line between turbulent and laminar flow. Reynold's number might be easier to calculate than an outlier test -- if it can be reliably used in your situation.

Most of Excel's regression functions are not going to like a "variable" size data set. I would expect that you will want to be able to use the LINEST() function when all is said and done here, because the LINEST() function will output several different statistics that will likely be useful in the regression task. (help file for the LINEST() function https://support.office.com/en-us/art...a-fa7abf772b6d ). Once an algorithm is chosen and implemented for testing for outliers, we will need an algorithm that will extract those points from the input ranges to the regression function. That will also likely be a challenge.

Overall "flow chart" for this:

1) User inputs data.
2) Perform regression.
3) Test for outliers.
4) Remove outliers from data set.
5) If needed/desired, return to (2) and repeat loop until satisfied with the result.

I would probably recommend that you "divide" and conquer -- identify the steps that you are not show how to program into Excel, and let's focus on smaller chunks of the problem.