excelregression

Quadratic and cubic regression in Excel

I have the following information:

  Height    Weight

    170     65
    167     55
    189     85
    175     70
    166     55
    174     55
    169     69
    170     58
    184     84
    161     56
    170     75
    182     68
    167     51
    187     85
    178     62
    173     60
    172     68
    178     55
    175     65
    176     70

I want to construct quadratic and cubic regression analysis in Excel. I know how to do it by linear regression in Excel, but what about quadratic and cubic? I have searched a lot of resources, but could not find anything helpful.

Solution

  • You need to use an undocumented trick with Excel's LINEST function:

    =LINEST(known_y's, [known_x's], [const], [stats])

    Background

    A regular linear regression is calculated (with your data) as:

    =LINEST(B2:B21,A2:A21)

    which returns a single value, the linear slope (m) according to the formula:

    enter image description here

    which for your data:

    enter image description here

    is:

    enter image description here

    Undocumented trick Number 1

    You can also use Excel to calculate a regression with a formula that uses an exponent for x different from 1, e.g. x1.2:

    enter image description here

    using the formula:

    =LINEST(B2:B21, A2:A21^1.2)

    which for you data:

    enter image description here

    is:

    enter image description here

    You're not limited to one exponent

    Excel's LINEST function can also calculate multiple regressions, with different exponents on x at the same time, e.g.:

    =LINEST(B2:B21,A2:A21^{1,2})

    Note: if locale is set to European (decimal symbol ","), then comma should be replaced by semicolon and backslash, i.e. =LINEST(B2:B21;A2:A21^{1\2})

    Now Excel will calculate regressions using both x1 and x2 at the same time:

    enter image description here

    How to actually do it

    The impossibly tricky part there's no obvious way to see the other regression values. In order to do that you need to:

    enter image description here

    enter image description here

    enter image description here

    You will now see your 3 regression constants:

      y = -0.01777539x^2 + 6.864151123x + -591.3531443

    Note: At some point since this post was last updated, Excel (at least, Excel 365 Version 2410) does not require this workaround to see all 3 regression constants. The formula will naturally output all 3 constants as desired.

    Bonus Chatter

    I had a function that I wanted to perform a regression using some exponent:

    y = m×xk + b

    But I didn't know the exponent. So I changed the LINEST function to use a cell reference instead:

    =LINEST(B2:B21,A2:A21^F3, true, true)

    With Excel then outputting full stats (the 4th paramter to LINEST):

    enter image description here

    I tell the Solver to maximize R2:

    enter image description here

    And it can figure out the best exponent. Which for you data:

    enter image description here

    is:

    enter image description here