Trying to run regresssion on about 500 series, too slow!

Eratanz

New Member
Joined
Feb 17, 2005
Messages
7
I have a sheet with about 500 series of two dimensional data arranged in two columns. Each series is separated by a single empty row. I need to run a polynomial regression on each series and have the coefficients spit out, preferably in two (two coefficients) separate columns. I have little experience with Excel and doing this manually would take many hours. The regression formula is =LINEST(range, domain^{1,2},FALSE,FALSE).

Thanks in advance.
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

cigs

New Member
Joined
Dec 8, 2002
Messages
23
Let there be two columns B and C containing your x and y data e.g
Col B Col C
1 0
9 4
5 2
7 3

You could place the line regression equation in column D somewhere for that data set. The formula is
{=LINEST((B1:B4),(C1:C4),TRUE,FALSE)}
Note that this has to be an array formula over two cells because it is going to return two coefficients m (slope) and b (offset)
To enter an array formula you have to use Shift-Ctrl-Enter together instead of the normal Enter.
Note that the third part of the formula is TRUE rather than FALSE as in your question, because you mention that you want both coefficients returned rather than have the b coefficient forced to zero.

For the other 500 series it is just a careful cut and paste job.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,491
Messages
5,596,474
Members
414,070
Latest member
DuncanLucas

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top