Get equation for 3d shape
I have 2 arrays say X and Y. Each have 5 elements. Now for each possible combination of (X,Y) I have a Z value, so Z is a 5x5 matrix.
I am looking to find a formula e.g. z=f(x,y). Any idea about how that can be done.
I tried MS Excel surface chart, but it doesn't give any equation or curve fitting on surface charts.
Answer
in general I would suggest to use some other software like SciLab or Matlab to work on this task. These products are more computatinal mathematics than Excel. But Excel has some built-in features that maybe will help you.
First note: You will need to use the Add-In called "Solver". This add-in comes along with Excel, but maybe is not installed as default on your installation. One description (there are thousands available in www) how to install that add-in you will find here: Solver Add-in
If you are done with this, the next step is to create a sheet with the data. I tried to generate an example shown in the picture below.
The range C5:G9holds the Matrix you want to approximate by a function.
So it's the z=f(x,y) Matrix.
The Chart beside is just the 3D-Plot of your (in this case my) original data.
Now it will become a little bit mathematical....
You need a general type of function which will be used to do the approximation.
The quality of the result is depending on how good this function is able to come close to your data.
In the example I used an approach with a 2nd order approximation (maximum quadratic terms).
My example function is z=a*x^2 + b*y^2 + c *x*y + d*x + e*y +f.
If you need more, try it with a third order term (including also x^3, y^3 , ...).
I didn't want to do this in the example, because I'm hating to type long formulas in Excel.
Typing long formula is the next step:
Now we have to fill the range C15:G19 with the values of the calculated formula. But before this, we have to define the polynomial coefficiants in range J14:J19. As a starting value, you can use just 1 for all coefficients (the picture shows the solution after running the solver)
The formula in Cell C15 is =$J$14*C$14^2+$J$15*$B15^2+$J$16*C$14*$B15+$J$17*C$14+$J$18*$B15+$J$19
It should be easy to copy it to the other cells of the Matrix.
The plot beside this is showing the result of our approximation function.
Now we have to prepare the solver. The solver needs to optimize somehow.
Therefore we need to define a function which indicates the quality of our approximation.
I used the least square value... Have a look on the www for explanations.
In the range C24:G28 I calculated the squares of the differences from our approximation function to the original data. Cell C24 has the formula =(C15-C5)^2
Now we are close to be finished. Just copy this formula to the rest of the range and than add one very important cell:
Put the sum of the range C24:G28 in Cell H29
This value is the sum of the error or better to say the difference of our approximation function to the original data points.
Nowe the most important !!! Select Cell H29 and start the solver add-in:
This window will pop-up (sorry I have a German Excel installation on my PC)
Just fill in the value fro target cell $H$29, target value =0 and the variable cells (important) $J$14;$J$19
Press "solve" and .... tada the polynomial coefficiants have changed to fit your data with the function.
Is this, what you have been searching for ???
Kindly Regards Axel