Macro for Intercept of Regression Line

**elementalsurf7** · 06-25-2010, 01:47 PM

Hello, I'm new to macros, and have only been working with recordings so far. I am hoping I can get some help (after a search for the topic, of course). Here is the issue, which is slightly different from other topics:

I have collected a lot of data. I have a macro already established to take my data and plot it. Now, I want to add a 2nd order tread line, and find its intercept. I then want to use that intercept and subtract it from all of my data so that it intersects at (0,0).

The way I am currently doing it is all manually. I have to analyze data too often, and it takes forever to get the tread line, show the equation of the line on the graph, manually copy the intercept from the equation and subtract it from my original data. I'm looking for any help that can streamline this using a macro. Thanks

**shg** · 06-25-2010, 05:21 PM

Welcome to the forum.

If your data is in ranges x and y, then select three cells wide (e.g., A1:C1), and add the formula

=LINEST(y, x^{1,2})

... which MUST be confirmed with Ctrl+Shift+Enter, not just Enter. You'll recognize those numbers from the trendline formula on the graph. The number in C1 is the y intercept.

**elementalsurf7** · 06-29-2010, 08:16 AM

I have found a similar solution online. My command would be:

INDEX(LINEST(A1:A25, B1:B25^{1,2},1,3)

to find the intercept. I have also tried a variation without the index, but I heard it was easier to do it with. However, my intercept numbers would be different. For example, on the trendline, it would say the intercept was -1.*****, whereas this command would give me -9.****. Any idea why the discrepancy? I know the -1 number is correct because when doing it manually, those numbers give us the correct plots later down the line.

**shg** · 06-29-2010, 09:51 AM

Maybe post a workbook, so I can see what you're seeing?

**elementalsurf7** · 06-29-2010, 10:23 AM

Nevermind. Excel 2007 was having trouble doing it properly with empty rows between the data points. I removed the empty rows and the discrepancy went away. Same results as the trendline. Thank you for your help