Page 1 of 3 123 LastLast
Results 1 to 10 of 24

Thread: Regression Analysis.
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jan 2008
    Posts
    168
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Regression Analysis.

    I want to make kind of a 3d regression and from a set of 3d points (XYZ - Coords) i want to calculate the interpolated plane,that fits best between the set of points.

    So far I've only thought of making 3 linear regressions,one on each plane (XY,XZ,YZ) and combining them to create the plane.I don't know if there's an easier,more accurate and better way to do this.

    Also what's the precision of the regression equation.it seems like it provides 4 decimals for the a coefficient and 2 for the b constant.Is that good,can i make it provide more accurate equation for the regression it draws?I mean does it use this exact equation or does it display an approximation of the regression it drew?

  2. #2
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    19,704
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Regression Analysis.

    Hi

    Check the help for Linest(). You can get the interpolated plane directly.

    it seems like it provides 4 decimals for the a coefficient and 2 for the b constant
    ??

    Please tell how are you calculating the regressions. As far as I know, there is not 4 decimals limitation.
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

  3. #3
    Board Regular
    Join Date
    Jan 2008
    Posts
    168
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Regression Analysis.

    Well i'm just selecting format trendline and i then check "show equation on chart" and "display r-squared".And i get the precisions i stated.

  4. #4
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    19,704
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Regression Analysis.

    If you are looking at the equation displayed in the chart, you are seeing it in a default format.
    If you want to see more decimals Right-click on the equation label box and change the decimals in the number format.
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

  5. #5
    Board Regular
    Join Date
    Jan 2008
    Posts
    168
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Regression Analysis.

    You're absolutely right.Although my method isnt exactly correct.I just tried combining the regressions and they didnt actually form a plane.

    I will now try your function and will report when more progress is made.

  6. #6
    Board Regular
    Join Date
    Jan 2008
    Posts
    168
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Regression Analysis.

    Ok i tried it,and it semms like it only outputs the a coffiecient of x.How can i use it to get the corresponding plane i'm interested in?

  7. #7
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    19,704
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Regression Analysis.

    This is a simple example.

    The points (x,y,z) are in A2:C6

    To get the coefficients:

    - select E2:G2
    - enter in the formula bar: =LINEST(C2:C6,A2:B6)
    - confirm with CTRL-SHIFT-ENTER

    You should get the values in E2:G2

    In mu example the equation of the interpolated plan is:

    Z = 3.004392971 * X -1.997574492 * Y -0.003069987

    You can get more decimals if you want, by changing the number format of the cells.

     ABCDEFGH
    1XYZ     
    20.0590.288-0.401 -1.997573.004393-0.00307 
    30.560.977-0.273     
    40.950.6041.645     
    50.1320.51-0.624     
    60.1510.1830.084     
    7        
    [Book1]Sheet3
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

  8. #8
    Board Regular
    Join Date
    Jan 2008
    Posts
    168
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Regression Analysis.

    And then what?Jut calculate Z values for minimum and maximum x,y so i can define the sides of a plane?Ok let me try that.

  9. #9
    Board Regular
    Join Date
    Jan 2008
    Posts
    168
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Regression Analysis.

    I tried it and that was definitely not it.

    Here's my set of data

    # X Y Z
    1 508,4116073 506,3527588 499,9928089
    2 504,0678459 504,7305128 499,9888056
    3 504,2501379 511,3033669 499,9092441
    4 506,6228564 513,2947125 499,9206961
    5 510,3754041 513,9208489 499,9503464
    6 511,4170777 511,601339 499,9482386
    7 505,0220678 517,0924855 499,8621102
    8 508,4532643 518,3274606 499,8884361
    9 507,9267521 521,9080671 499,8490075
    10 511,8660 523,2208511 499,8813995
    11 509,1370515 526,3137856 499,8228214
    12 513,5008469 529,9595136 499,8348534
    13 514,8537169 524,1636833 499,8634049
    14 508,5771791 509,7915416 499,9689059
    I'm looking for the Z values of the edges of the plane and i got values somewhere around 508 when all my elevations are 499,.... where the values should have been.

  10. #10
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    19,704
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Regression Analysis.

    Hi again

    I don't understand.

    I used your data, calculated the coefficents in F2:H2 with =LINEST(C2:C15,A2:B15) and got the equation:

    Z = 0.006835753 * X -0.008673374 * Y + 500.90754

    This is the equation of your interpolated plan. I don't understand what's the edges of the plan. As far as I know, a plane doesn't have edges. Can you explain?

    To confirm the equation I used in D2:

    =$G$2*A2+$F$2*B2+$H$2
    Copy down

    The values in column D are the corresponding values to column C, using the interpolation plan.

     ABCDEFGHI
    1XYZZ Interp     
    2508.4116073506.3527588499.9928089499.9911290 -0.0086733740.006835753500.90754 
    3504.0678459504.7305128499.9888056499.9755065     
    4504.2501379511.3033669499.9092441499.9197438     
    5506.6228564513.2947125499.9206961499.9186914     
    6510.3754041513.9208489499.9503464499.9389122     
    7511.4170777511.6013390499.9482386499.9661508     
    8505.0220678517.0924855499.8621102499.8748093     
    9508.4532643518.3274606499.8884361499.8875527     
    10507.9267521521.9080671499.8490075499.8528977     
    11511.8660000523.2208511499.8813995499.8684391     
    12509.1370515526.3137856499.8228214499.8229585     
    13513.5008469529.9595136499.8348534499.8211676     
    14514.8537169524.1636833499.8634049499.8806849     
    15508.5771791509.7915416499.9689059499.9624350     
    16         
    [Book1]Sheet3
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •