Surface Regression

[abalkees]

Hi All,

I am trying to generate a regression equation for the data I have in the table. You can see the surface which represents the the response values for two independent variables (x, y).

Does anybody have an idea about how to get a third-power equation or an exponential equation?

Is there any function in EXCEL or add-ins or a simple way to use? please provide your solution step by step.

Many Thanks,
Alaa

[MrShorty]

Is there any function in EXCEL or add-ins or a simple way to use? please provide your solution step by step.

I'm not sure how "simple" any kind of regression in 3 dimensions will be.

The first step in any regression problem is to determine what form the regression equation [z=f(x,y)]should take. This really isn't an Excel problem - more of a math problem. Deciding on a form for f involves considering the data, and the physical, real world process that the data are supposed to represent to come up with a reasonable equation (usually trying to use as few parameters as possible) that will describe the data. Just looking at the data, I would expect that a 3rd order equation would overfit the data - it probably only needs a 2nd order equation. If I remember correctly, the standard form for a 2nd order polynomial will look something like z=f(x,y)=ax^2+bx+cy^2+dy+exy+f. It may be possible to eliminate some of those parameters (for example, the data suggest that the surface goes through the origin (0,0,0), so we could force the equation to through the origin). While considering what form the equation should take, consider using a linear function where all the parameters are linear (like a polynomial), because then we can use the linear regression algorithms built into most spreadsheets and other software.

Once a suitable equation has been chosen (assuming it is linear) then we will use the LINEST() function to determine the parameters. http://office.microsoft.com/en-us/ma...823.aspx?CTT=1 To do this, we need to build the input matrix. This involves building a new table from the current table. This table will contain a column for z and columns for each f(x,y) that corresponds to that z. This will look something like (comma delimited assuming 2nd order in both x and y):

Please Login or Register  to view this content.

With the input matrix in place, we can then use the LINEST() function to return the parameters =LINEST(known_z's, known_x matrix)

[abalkees]

Hi MrShorty,

Many thanks for your reply. I took me some time to work on my data, and your method was really useful.

I had two sets of data, and I followed the steps to get my regression formulas which are from the second order.
Y1=f1(x1,x2)
Y2=f2(x3,x4)

By the way (Y1 and Y2) represents the same response which is Ship Resistance.

***************
May you please help me with one more problem?

I am trying to generate a regression formula for both data contains one response Z and 4 variants (x1, x2, x3, x4). So, I want to find how the resistance change when we change all 4 variants together.
Z=f(x1, x2, x3, x4)

Do you have any idea about finding a regression formula for such a data?

I am sure that it will be from the second order since (Y1,Y2) r from the second order.

Your help will be really appreciated because I have to find a solution.

Many Thanks
Alaa.

[shg]

SurfaceSolver does a fit with an RMS error of 0.01 using 10 terms on just x and y ...

[shg]

If you detabulate your data (put it in a list instead of a table), you can use LINEST.

[MrShorty]

The overall solution to the question as you've asked it should be the same as the one I gave before - a) determine the form of f, b) compute each input "term" for each data point, c) regress using LINEST() or other regression algorithm.

As noted before, the real challenge to these kinds of problems is know what form f should take. Extending a generic 2nd order polynomial out to 5 dimensions will generate a long equation with a lot of parameters (I expect more parameters than are truly necessary). I would suggest at this point that it might be best to step away from Excel and look at the literature for calculating/regressing ship resistance to see what kind of equation one should expect for ship resistance. I'm sure you would have better resources than what I can find in a quick internet search, but I found this at scribd http://www.scribd.com/doc/7174460/Ba...hip-Resistance (assuming this is the same "ship resistance" that you are working on). Perhaps a study of the theory behind ship resistance and how ship resistance depends on all the different parameters that go into it will yield a more workable equation than a generic polynomial.

[shg]

it might be best to step away from Excel and look at the literature for calculating/regressing ship resistance to see what kind of equation one should expect for ship resistance.

+1 .

[abalkees]

Thx Sgh,
so in this case I cannot use the SurfaceSolver. Is that right?

[abalkees]

Shg,
Also if I re-tabulate my data, the data in the table will be a bit complicated to consider all variants and all factors, is not it?

[shg]

so in this case I cannot use the SurfaceSolver.

True, though as I said, Surface Solver gives a very good fit using just x and y.

Also if I re-tabulate my data, the regression formula will not take the four variants separately.

Sure it will.

[abalkees]

the data in the table will be a bit complicated to consider all variants and all factors, is not it?

[abalkees]

Dear MrShorty,

The link you provided does not cover my area of work because I am looking to the resistance in a different way. I am trying to find the influence of (Length, Breadth/Draught, Block Coefficient, Longitudinal centre of Buoyancy). In all ship design books there is no such a formula considers these factors together.

Apologise for my poor knowledge about what I am asking about.

[shg]

I dunno, I haven't seen your data. As MrShorty said, you need to decide what the form of the regression is, which you should do with reference some naval architecture data.

[shg]

Mark's has a brief section of its 1000-odd pages devoted to marine engineering, and describes resistance as the sum of wave-making, friction, pressure or form, and air, and provides models for each. That might be a place to start, but there are surely naval architecture books devoted entirely to the topic.