Ali Baba wrote:
I am trying to write a VBA code to figure out the coefficients of an equation.

Y = A + B*X1 + C*X2 + D*X3

So if I have an equation of this form
Y = 2 + 3*X1 + 4*X2 + 5*X3 (values of X1, X2, X3 are known and so Y can be
calculated)

I want to know that A = 2, B = 3, C = 4 and D = 5

I can obtain the coefficient A by putting X1, X2, X3 = 0 and this would give
Y = 2 which is the value of A. I don't know how to find the other
coefficients.

If it is not possible to find the coefficients, how can I take the square of
each term in the equation.

Y = (2)^2 + (3*X1)^2 + (4*X2)^2 + (5*X3)^2


Just to sum up,
1) I want the user to enter a function in the form of Y = A + B*X1 + C*X2 +
D*X3
2) then the user will enter the values of X1, X2, X3 and this would enable
us to calculate Y
3) then we want to calculate Y1 = (B*X1)^2 + (C*X2) ^2 + (D*X3) ^2

PLEASE HELP IF YOU CAN€¦.


You don't need VBA - just use Solver. I'm presuming that you have a bunch of
data that you're trying to fit to the equation. And I presume further that you
have a columns of (Y,X1,X2,X3) values and want to know what A,B,C,D coefficients
best fit the data.

The "no VBA" approach is to use Solver. Set aside cells to hold values for
A,B,C,D and just plug anything into them to start with. Then create a column of
Y2 values which just take the Xn values, and use the ABCD coefficients to
calculate Y2. Finally create and Error^2 column = (Y-Y2)^2. At the bottom sum
the squared errors.

Then ask Solver to minimize this sum by adjusting the A,B,C,D values and PRESTO!
it will give you the best fit coefficients.

You can use VBA to somehow create a Solver of your own, buy why bother when
Excel provides the function?

Good luck...

Bill