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