Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA coefficients of an equation
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€¦. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA coefficients of an equation
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA coefficients of an equation
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA coefficients of an equation
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA coefficients of an equation
Thanks deanop and bill
What I actually want is something which looks like this http://phpaw.com/myscrpit/milt-up/jp...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?? "deanop" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA coefficients of an equation
What I actually want is something which looks like this
http://phpaw.com/myscrpit/milt-up/jp...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? -- Dana DeLouis Win XP & Office 2003 "Ali Baba" wrote in message ... Thanks deanop and bill What I actually want is something which looks like this http://phpaw.com/myscrpit/milt-up/jp...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?? "deanop" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA coefficients of an equation
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 B1:D6: 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 In article , says... Thanks deanop and bill What I actually want is something which looks like this http://phpaw.com/myscrpit/milt-up/jp...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?? "deanop" wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA coefficients of an equation
Thanks all
I think I will adopt Tushar way "Tushar Mehta" wrote: 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 B1:D6: 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 In article , says... Thanks deanop and bill What I actually want is something which looks like this http://phpaw.com/myscrpit/milt-up/jp...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?? "deanop" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extracting Trendline Coefficients | Excel Discussion (Misc queries) | |||
coefficients of polynomial | Excel Discussion (Misc queries) | |||
Trend Line Equation Coefficients | Excel Discussion (Misc queries) | |||
Equation Editor- problem when editing an equation | Excel Discussion (Misc queries) | |||
Trendline coefficients | Charts and Charting in Excel |