Regression Macro

I have been reading around the forum trying to figure out the solution to this, but was unable to, so I thought I would join and post!

I am doing cash projections for work, and I use a regression to do them. I want to make this work via macro in order to avoid doing it every month. I would like to have my Macro use a Y and X range that automatically goes to the bottom of the data (i.e. Month 1 to row 54, month 2 to row 55, etc. or wherever the end of the data happens to be)

The regression output needs to have "labels" checked.

My X and Y values come from the worksheet called "Regression Data"
The Y data is currently C1:C54
The X data is currently D1:S54
(Rows will be added to these columns each month)

The output is on the worksheet "Regression Output"
The Output range is just this entire worksheet. (1:1048576)

I attempted to simply record the macro, but kept getting errors about having no "Y" data, and when I looked at the code, I was unable to fix this and it did not appear that the macro was recording my input of the data range.

I am not sure if this will happen automatically, but the new regression (ran via macro) needs to simply replace the old macro (i.e. go right on top of it, with the same data in the same cells)

I will post my code below, if anyone is able to help me out with this. I would greatly appreciate it! Thank you!

Please Login or Register  to view this content.

it would be useful to see the dummy workbook otherwise it will look like treating patient by foto

Originally Posted by watersev

it would be useful to see the dummy workbook otherwise it will look like treating patient by foto

Would you like just screen shots of essentially what it would look like? Or is there a good way to post an actual excel file?

Originally Posted by Mordred

Its better to attach a working copy of the workbook and what it is you want your outcome to be. Screenshots are not very helpful.

I use excel 2007 at work, but have 2010 at home. I will set up a dummy worksheet in 2010 here and post it shortly.

Here is what I just put together quick with the data and the output, basically what I want the Macro to do is the regression output in the same spot every time.

I want it to automatically go to the bottom of the data in the y and x columns wherever that may be each month, if that is possible.

The regression on this probably makes no sense as I made up numbers, but it is the same concept.

Thank you again for all of the help!

Dummy Regression.xlsx

it's nice start to see the data structure. The only thing left is to understand what and how to do with the help of your explanation. I'm for example do not do regression analysis every day. The file has no either your macro nor formulas.

Originally Posted by watersev

it's nice start to see the data structure. The only thing left is to understand what and how to do with the help of your explanation. I'm for example do not do regression analysis every day. The file has no either your macro nor formulas.

I eventually want to create a button for it, so that after you have added new data to the spreadsheet (Regression Data), clicking the button will run a new regression using that new data (if possible) and put the output values where the previous regression output (Regression Output) was on the worksheet.

When I tried to simply record the macro, it wasn't reading what the data parameters I was setting were. I am unsure why? Or how I could just write the code for it to do this?

recording macro of all actions you are doing will be not bad

i'm afraid it would be extremely difficult to help you unless you provide your actions and formulas you use. Namely what do these headers stay for: Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0%
How do you get those results? Is the calculation is the same for all rows? What are the formulas?

Originally Posted by watersev

i'm afraid it would be extremely difficult to help you unless you provide your actions and formulas you use. Namely what do these headers stay for: Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0%
How do you get those results? Is the calculation is the same for all rows? What are the formulas?

That is what the regression produces ... They are not formulas, that is the output that excel creates based on the data on the "Regression Data" tab. It is a Data-Analysis function that Excel does.

Originally Posted by shg

Are you interested in all of those stats or a subset?

If you select K17:O33 on sheet regression input, paste

=TRANSPOSE(LINEST('Regression Data'!C2:C49, 'Regression Data'!D2:S49,,TRUE))

... in the formula bar, and confirm with Ctrl+Shift+Enter, you'll see largely the same results, except that the order of the rows is reversed (a peculiarity of LINEST that I've never understood.)

Specifically, coefficients are in the first col, and standard errors in the next (slightly different, but they are very small anyway.

Nope, all I need are the coefficients. I will try out that formula and see how it works! Thanks.

Originally Posted by shg

Are you interested in all of those stats or a subset?

If you select K17:O33 on sheet regression input, paste

=TRANSPOSE(LINEST('Regression Data'!C2:C49, 'Regression Data'!D2:S49,,TRUE))

... in the formula bar, and confirm with Ctrl+Shift+Enter, you'll see largely the same results, except that the order of the rows is reversed (a peculiarity of LINEST that I've never understood.)

Specifically, coefficients are in the first col, and standard errors in the next (slightly different, but they are very small anyway).

That formula did exactly what I wanted! (Although the ordering of the coefficients is very curious as you mentioned)

Is there a way, possibly with a macro, to have it so that when adding a new row (so that the data now goes down to C50 for example) to have the formula automatically now include that row? Or would I have to update the formula each time I add a row?

Thanks!

used Internet search, first link, as for me it's easier to make it manually or if not, see attachment

Hey, Thanks from me as well. I was having the same problem, and this fixed it !