About Us

Ali Baba

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

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Extracting Trendline Coefficients	[email protected]	Excel Discussion (Misc queries)	4	June 14th 07 05:57 PM
coefficients of polynomial	Doublee	Excel Discussion (Misc queries)	5	March 9th 07 05:55 PM
Trend Line Equation Coefficients	Jake	Excel Discussion (Misc queries)	4	April 17th 06 08:46 PM
Equation Editor- problem when editing an equation	Gaby L.	Excel Discussion (Misc queries)	0	September 27th 05 09:24 PM
Trendline coefficients	MrUniverseman	Charts and Charting in Excel	3	May 18th 05 12:56 PM

Menu

About Us