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

1. ## 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. ## 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.

3. ## 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. ## 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.

5. ## 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. ## 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. ## 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

8. ## 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. ## 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. ## 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

## User Tag List

#### Posting Permissions

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