Tweet
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
15 (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
15 (you need four columns for the three coefficients plusthe 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
---------------------------------------------------------------