Search: | |||||

| ||||

Home » Forum » MS Office | RSS Feeds |

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 !!

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

and m2, and then trying to get the best least-squares fit.

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.

Related Topics:

- Statistics Data Modeling ?
- i have JDK, Java 3D Game kit and Java 3D kit, i want to know.
- Java Development Kit 1.2 and Java 3D 1.3.1 -Problems
- would U PLZ help me on JAVA 3D?
- java 3d api for linux
- 3D Button Help
- java 3D
- Can anyone help with Java 3d Api ?
- 3D Java
- poser python modeling
- Document Object Model?
- Application layer in Model
- About Architecture Model
- Model to Dto conversion
- Testing Models with CakePHP 1.2 test suite
- A great article about "Transaction Models and Strategies"
- Modelling software
- client / server model
- Help with Sun Model 2
- MVC Vs Model 2
- Default table model
- VBComponets & VBIDE Extensibilty Object Model ?
- books on Exvel VBA + financial modeling
- User-data types: loading data, Equivalence or Alias possible?
- Data Sum rows not hidded