Search:

3D data modelling

Asked By: Ludwig    Date: Nov 04    Category: MS Office    Views: 1484

This is basically a problem in 3D modelling.
I have collected some data in the following format.
X Y Z
---- ---- ----
1 5 1
2 4 1
3 3 1
4 2 1
5 1 1
1 4.5 2
2 3.5 2
3 2.5 2
4 1.5 2
5 0.5 2
1 4 3
2 3 3
3 2 3
4 1 3
5 0 3
etc...

I want to fit the following data into a curve of the equation,
z = m1*X + m2*Y
can it b done using excel ??
Any pointers would be appreciated !!

Share:

This is possible to do in Excel. Even better, your problem  is linear,
which means that you can use the standard Excel Solver to do the work -
no need to upgrade. BUT REMEMBER, if you try a different equation, your
problem might become non-linear, in which case you are not guaranteed
the most optimal solution. Depending on what settings you choose in the
Solver, it will converge on some local optimum, which may not be the
global optimum.

With all the legal stuff out of the way, proceed as follows:

A B C D E F Delta
X Y Z Zcalculated m1 m2
1 5 1 =\$e\$2*A2+\$f\$2*b2 1 1
=abs(d2-c2)
2 4 1 =\$e\$2*A3+\$f\$2*b3
=abs(d3-c3)

Then do a =sum(G2:G3), and using the solver, minimise the cell, with
cells E2:F2 as variables.

So, what are we doing? We are specifying random starting points for m1

To check the fit, it is always a good idea to plot on an x-y graph Z
versus Z-calculated. Fit a straight-line curve to the data, and display
the r2 fit on the graph. FOR YOUR PARTICULAR data  SET, you need to
decide what is a suitably acceptable r2 value. Secondly, you need to
ensure that your prediction actually represents reality. So draw a line
from the bottom left corner to the top right hand corner of the graph.
Your fitted line should overlay this line, or at least be close to it,
or at least have the same slop. When the slope is substantially
different, you may want to reconsider your equation, because your
prediction will then only be accurate in a small range (where the two
lines cross, or are closest together.

You can do so using Linear Regression in Excel. You have to make sure that
the "Analysis Tool Pack" Add-in is selected.

Didn't find what you were looking for? Find more on 3D data modelling Or get search suggestion and latest updates.