automative Regression, Macro, update regression with new values

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? However, please give us an indication of the approximate number of rows of data you want the solution to work with (100, 1000, 100,000 or whatever). Please don't attach a picture of an Excel sheet (no-one will want to re-type all your stuff before starting).

1. It does NOT have to be your real sheet - mock up a SAMPLE if you need to. But not 1000's of rows!!! It makes manual checking so tedious. Whatever you do... make sure that all confidential information is removed first!!

2. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

3. Make sure that your desired solution is also shown (mock up the results manually).

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

I was in exactly the same situation a few years ago. I set up a formula-based ANOVA table... from memory. That could be adapted to select specified ranges.. I will try to find it on my HDD.

It's not onvious from your sheet what data ranges you want to use... if it was columns N:Q, they are on your HDD, not in the sheet. I see non-working links. If it was those columns, how do they relate to columns A:K?

More information is needed...

Your sample file suggests to me that you are using the Data analysis -> Regression tool to perform these regressions, is that correct? When you recognize that the Data Analysis -> Regression tool uses the same exact algorithms as the LINEST() worksheet function to perform regressions, my first suggestion is to use the LINEST() worksheet function instead of the Data Analysis -> Regression tool. LINEST() help file: https://support.office.com/en-us/art...a-fa7abf772b6d

The Data Analysis -> Regression tool outputs more statistics than the LINEST() function. If all of those statistics are needed, recognize that all of those statistics can be calculated using other worksheet functions. You may need to research how those other statistics are calculated, but once you know how to calculate them, you should be able to come up with worksheet formulas that will calculate all of those statistics.

If I were going to make this more automatic, that's the direction I would go.

I don't expect Glenn or anyone to come up with a single formula that supplies all of those statistics. As I said, I would use the LINEST() function and what I know of the other statistics to get the same output.

1) As explained in its help file, the LINEST() will give you:
coefficients
standard errors of coefficients
r-squared and standard error in y
F-score and degrees of freedom
the sum squared and residual
2) When you need the t value and accompanying P score, these are easily calculated from the LINEST() output (see this statistics tutorial: https://stattrek.com/regression/slope-test.aspx )
t=coefficient/standard error of coefficient
P score is from the t-distribution (in Excel, you can use one of the TDIST() functions (https://support.office.com/en-us/art...8-4a1f9dcdd192 )
3) Margin of error of each coefficient uses the desired confidence interval and the TINV() function to get the lower and upper confidence limits (see stat tutorial here: https://stattrek.com/estimation/margin-of-error.aspx ).
4) To get the Fcritical value, use the FDIST() function with the Fscore output by LINEST()
5) and so on until you have all the stats you need.

The main "trick" I would expect is that, because the LINEST() output is a 2D block of cells rather than a single row/column, it will be tricky to arrange the LINEST() output into a single column for easy copying across columns B:K. Strategic use of an INDEX() function should allow you to arrange the LINEST() output (along with the other statistic calculations) so it all fits into a single column beneath the corresponding x.PF values. Then it will be a simple copy and paste to perform the regression analysis for each column of x.PF values.

Once you are familiar with Excel's stat functions and the statistics behind the analysis, this should not be overly difficult to program into the spreadsheet. Let us know where you get stuck.