run several separate regressions from one data base

I am not quite sure what you mean by "regression." I'm guessing you may want to use a pivot table. You can set the pivot table up on one sheet, and make multiple copies of that sheet. Then you can go back and set the filters for the departments on each sheet.

One of the features of a pivot table is that if you refresh one pivot table, all other pivot tables built on that data source will also refresh.

On the other hand, if you are talking about linear regression, or something like that, then either array formulas, or maybe a pivot table with a named dynamic range overlay will meet your needs.

We'd have to see how the source data is organized, and what you would like the desired results to look like.

For others, this looks like an extension of http://www.excelforum.com/excel-gene...eviations.html

A sample data base would help us provide more specific suggestions. To add a sample file, click on "Go Advanced" below the quicky reply box, find "Manage Attachments", then work through the add attachment window.

As I explained in the previous thread, Excel's regression algorithms require that the input data be in a single block of cells. Every question like this that I have seen usually ends up being about how to separate out or compile the desired data for each regression into a single block. There are many different strategies for this. I illustrate one strategy in the attached spreadsheet. Note the following:

1) Start with unsorted list
2) Sort list by department. Note that you would likely not end up with two copies of the database, as you would normally sort the list in place. I copied the list before sorting so you could see the before and after. Sort command is on the Data Menu.
3) In the regression table (M2:Q13) I have:
3a) a list of all departments
3b) a MATCH() function to locate each department in the list
3c) a COUNTIFS() function to determine how many entries for each department
3d) an OFFSET() function inside of the LINEST() function to extract the desired data for each regression. (help files for all functions here: https://support.office.com/en-us/art...rs=en-US&ad=US ).

No VBA was required for this. The only step that needs user input is the "sort" step.

Since you may ask, the regressions in S3:T4 are there to check the main LINEST() functions. The input ranges were manually selected to make sure the OFFSET() functions were retrieving the correct data.

I'm not sure exactly what you are doing here. The first observation I make is that you are using A2 as the reference cell for the offset function, which is the first cell with data in it. In my example, I used G2 as the reference cell for the offset function, which is the cell above the first cell with data. If you use the Evaluate Formula tool (Formulas -> Evaluate formula https://support.office.com/en-us/art...6-a70aa409b8a7 ), you will see that the regression for department 3 is using data from rows 14 through 30. It appears that your data for dept 3 is in rows 13 to 29, so it is looking one row below the desired data. I think this part is fixed if you adjust the reference cell for your offset functions up one row.

The other observation I make is that you have only entered the LINEST() function in one cell. The LINEST() function is an array function that returns two values. To use this function in Excel, one usually selects two adjacent cells, P2:Q2 for example, then enters the formula and confirms with ctrl-shift-enter to designate the formula as an array function.

I think with those two corrections, you should get the correct results.

I would suggest that you review the help file for the LINEST() function to learn all of its capabilities. (from list of function linked to above https://support.office.com/en-us/art...a-fa7abf772b6d ). Also, if you need a quick refresher on the statistics of linear regression, I found this a quick explanation of the t-stat and P-score: http://stattrek.com/regression/slope-test.aspx

If you set the optional 4th argument for the LINEST() function to TRUE (up to now, we have only used the first two arguments), then LINEST() will output additional statistics. You will also need to enter the function over a larger, 2D array of cells -- 5 rows by 2 columns if you want all of the information. The helpfile describes the extra statistics and how they are output. t-stat and P-score are not included, but they are easily calculated from the other statistics, as explained in the stattrek article.
t for a given coefficient = coefficient/standard error for that coefficient
P-score is simply the value from the tdistribution for that t score, which is easily calculated using the TDIST() or T.DIST() functions.

You did not include any of your failed attempts, so I could not tell what you are specifically having trouble with. I have entered the formulas for dept. 2. Is this what you are looking for?