No announcement yet.

XL regression? summary...

This topic is closed.
  • Filter
  • Time
  • Show
Clear All
new posts

  • XL regression? summary...

    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,
    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 A1515 (you need four columns for the three coefficients plus
    the intercept), enter the formula =LINEST(B2:B5, A2:A5^{1, 2, 3}) and
    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

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

    For more details please visit my web site and follow the trial to Excel Tricks
    & Tips.

    Thanks again everyone for your help.

    Shreefal Mehta

    To unsubscribe send SIGNOFF BIOMCH-L to
    For information and archives: