Shreefal Mehta

12-08-1998, 09:53 AM

An amazing response.

Some 20 people responded in a few hours...

Thanks to all who responded. There were a couple of queries to post response summaries.

So here they are:

Original question:

Is there any macro to do quadratic regression in Excel?

When i asked this question, i was trying to do it as a formula...

The student is specifically trying to do this in the worksheet so that he can process large numbers of

datasets without having to pull results (coefficients) off the graphs.

===========================================

Most of the respondees suggested doing it as a polynomial trendline in a scatter plot. ("RTFM")

--------------------------------------------------------------------

You can draw a graph and then insert a trendline which can be linear, logarithmic, polynomial, power, exponential, or moving average. The formula and correlation coefficient can then be shown on the plot. The order of the polynomial can be specified. The regression analysis tool appears to be only linear in my version too but the trendlines, which are regressions, have all of the above options.

--------------------------------------------------------------------

simply make a scatter plot, then do a line fit,

or

square your terms and do the linear regression on them. i.e. if you want

to fit y=x^2, first square your x terms, then do the linear fit.

--------------------------------------------------------------------

One note of caution, when the equation is displayed be sure to use scientific notation and about 4 significant digits. You can change the number type by selecting the equation and right click on it and

changing the format.

--------------------------------------------------------------------------

I'm not certain about excell, but I think this would work. In Quattro

pro, you can make a column of the x value, a column of the x^2 value,

(x^3, x^4 etc too if you want) and then the column of your experimental

data. Then when you do linear regression, you put all x columns into the

regression input, and the one column of data into the other input, and

run the regression. The answer would then give you the coefficients and

the errors for each x column, and then you could easily draw the curve

(using the x, x^2, x^3 ) through your data.

------------------------------------------------------------------------

Just use multiple linear regression where one of the variables is x^2.

-------------------------------------------------------------------------

Let the ranges A2:A5 and B2:B5contain your X and Y data respectively.

Select A15:D15 (you need four columns for the three coefficients plus

the intercept), enter the formula =LINEST(B2:B5, A2:A5^{1, 2, 3}) and

press SHIFT+CTRL+ENTER.

For the full statistics, select a range of 5 rows by 4 columns, use the

formula =LINEST(y, x^{1, 2, 3}, , TRUE) and complete it with

SHIFT+CTRL+ENTER.

Note also that a polynomial trendline can be added to an Excel chart.

For more details please visit my web site

http://www.stfx.ca/people/bliengme and follow the trial to Excel Tricks

& Tips.

--------------------------------------------------------------------

Thanks again everyone for your help.

Regards

Shreefal Mehta

shreefal.mehta@email.swmed.edu

---------------------------------------------------------------

To unsubscribe send SIGNOFF BIOMCH-L to LISTSERV@nic.surfnet.nl

For information and archives: http://isb.ri.ccf.org/biomch-l

---------------------------------------------------------------

Some 20 people responded in a few hours...

Thanks to all who responded. There were a couple of queries to post response summaries.

So here they are:

Original question:

Is there any macro to do quadratic regression in Excel?

When i asked this question, i was trying to do it as a formula...

The student is specifically trying to do this in the worksheet so that he can process large numbers of

datasets without having to pull results (coefficients) off the graphs.

===========================================

Most of the respondees suggested doing it as a polynomial trendline in a scatter plot. ("RTFM")

--------------------------------------------------------------------

You can draw a graph and then insert a trendline which can be linear, logarithmic, polynomial, power, exponential, or moving average. The formula and correlation coefficient can then be shown on the plot. The order of the polynomial can be specified. The regression analysis tool appears to be only linear in my version too but the trendlines, which are regressions, have all of the above options.

--------------------------------------------------------------------

simply make a scatter plot, then do a line fit,

or

square your terms and do the linear regression on them. i.e. if you want

to fit y=x^2, first square your x terms, then do the linear fit.

--------------------------------------------------------------------

One note of caution, when the equation is displayed be sure to use scientific notation and about 4 significant digits. You can change the number type by selecting the equation and right click on it and

changing the format.

--------------------------------------------------------------------------

I'm not certain about excell, but I think this would work. In Quattro

pro, you can make a column of the x value, a column of the x^2 value,

(x^3, x^4 etc too if you want) and then the column of your experimental

data. Then when you do linear regression, you put all x columns into the

regression input, and the one column of data into the other input, and

run the regression. The answer would then give you the coefficients and

the errors for each x column, and then you could easily draw the curve

(using the x, x^2, x^3 ) through your data.

------------------------------------------------------------------------

Just use multiple linear regression where one of the variables is x^2.

-------------------------------------------------------------------------

Let the ranges A2:A5 and B2:B5contain your X and Y data respectively.

Select A15:D15 (you need four columns for the three coefficients plus

the intercept), enter the formula =LINEST(B2:B5, A2:A5^{1, 2, 3}) and

press SHIFT+CTRL+ENTER.

For the full statistics, select a range of 5 rows by 4 columns, use the

formula =LINEST(y, x^{1, 2, 3}, , TRUE) and complete it with

SHIFT+CTRL+ENTER.

Note also that a polynomial trendline can be added to an Excel chart.

For more details please visit my web site

http://www.stfx.ca/people/bliengme and follow the trial to Excel Tricks

& Tips.

--------------------------------------------------------------------

Thanks again everyone for your help.

Regards

Shreefal Mehta

shreefal.mehta@email.swmed.edu

---------------------------------------------------------------

To unsubscribe send SIGNOFF BIOMCH-L to LISTSERV@nic.surfnet.nl

For information and archives: http://isb.ri.ccf.org/biomch-l

---------------------------------------------------------------