VBA coefficients of an equation

[Ali Baba]

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….

 

[Guest]

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

 

[deanop]

have users fill out coeff in C3:C6 and x variable in d4:d6, set d3 to
equal 1

Define coeff as your coeff array

set coeff= range ("C3:C6") ' Define coeff as your coeff array
set xi= range ("d3:d6") ' Define Xi as your variable x array

y =SUMPRODUCT(coeff,Xi)

this takes care of calculating y.....will play around with it to get
sum of squares

cheers,deano

 

[deanop]

as promised here is the code revised to include square function

Sub coeffxi()
Dim i As Integer
Dim Prod_array(1 To 4)
Set Coeff = Range("c3:C6")
Set xi = Range("d3:d6")
Range("C8").Value = "Y ="
Range("D8").Value = Application.SumProduct(Coeff, xi)
For i = 1 To 4
Prod_array(i) = Coeff(i) * xi(i)
Next
Range("C9").Value = "SumSQ ="
Range("D9").Value = Application.SumSq(Prod_array)
End Sub

cheers,deano

 

[Ali Baba]

Thanks deanop and bill

What I actually want is something which looks like this
http://phpaw.com/myscrpit/milt-up/jpg/jpg-1124615026.jpg

So the task of the program is to select the number of variables then enter
their details and the SM equation. from this we would be able to calculate
the answer

I don't know if there is another way to do this. deanop, you have suggested
that i ask the user to input the coefficients, how would the form look like??

 

[Dana DeLouis]

What I actually want is something which looks like this

http://phpaw.com/myscrpit/milt-up/jpg/jpg-1124615026.jpg

Hi. Just want to point out that the link shows an answer of 0.02

7/392 equals about 0.02, but the equation is

7/Sqrt(392), which I think should be equal to about 0.35
Is the equation correct?

 

[Tushar Mehta]

You can do what you show in the image with XL (no VBA required).

Ask the user to enter the means in a column, say B, starting with, say
B2 (B1 is reserved for a label such as 'mean'). Enter the std.dev. in
C. Enter the coefficients for each variable in the new equation in D.
[The constant term will have a mean of 1 and a standard deviation of
zero.]

So, to do what you show in the example, you would have in B16:

Mean StdDev Coeff
1 2 3
2 4 2
3 6 1
4 8 -2
1 0 5

To make your life easy, add a few named formulas (Insert | Name >
Define...)

MeanVals =OFFSET(Sheet2!$B$2,0,0,COUNTA(Sheet2!$B:$B)-1,1)
CoeffVals =OFFSET(MeanVals,0,2)
StdDevVals =OFFSET(MeanVals,0,1)

Now, the formula for the inverse of the coefficient of variation of the
new equation (which is what you are calculating) is the array formula
=SUMPRODUCT(CoeffVals,MeanVals)/SQRT(SUM((CoeffVals*StdDevVals)^2))

--
An array formula is entered with CTRL-SHIFT-ENTER rather than just
ENTER. If done correctly, XL will display curly brackets { and }
around the formula
--

Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Ali Baba]

Thanks all

I think I will adopt Tushar way

You can do what you show in the image with XL (no VBA required).

Ask the user to enter the means in a column, say B, starting with, say
B2 (B1 is reserved for a label such as 'mean'). Enter the std.dev. in
C. Enter the coefficients for each variable in the new equation in D.
[The constant term will have a mean of 1 and a standard deviation of
zero.]

So, to do what you show in the example, you would have in B16:

Mean StdDev Coeff
1 2 3
2 4 2
3 6 1
4 8 -2
1 0 5

To make your life easy, add a few named formulas (Insert | Name >
Define...)

MeanVals =OFFSET(Sheet2!$B$2,0,0,COUNTA(Sheet2!$B:$B)-1,1)
CoeffVals =OFFSET(MeanVals,0,2)
StdDevVals =OFFSET(MeanVals,0,1)

Now, the formula for the inverse of the coefficient of variation of the
new equation (which is what you are calculating) is the array formula
=SUMPRODUCT(CoeffVals,MeanVals)/SQRT(SUM((CoeffVals*StdDevVals)^2))

--
An array formula is entered with CTRL-SHIFT-ENTER rather than just
ENTER. If done correctly, XL will display curly brackets { and }
around the formula
--

Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

Thanks deanop and bill

What I actually want is something which looks like this
http://phpaw.com/myscrpit/milt-up/jpg/jpg-1124615026.jpg

So the task of the program is to select the number of variables then enter
their details and the SM equation. from this we would be able to calculate
the answer

I don't know if there is another way to do this. deanop, you have suggested
that i ask the user to input the coefficients, how would the form look like??