-
Regression In Excel
I'm writing a vbScript to do cubic regression in Excel, but I'm not sure of how to write it. Cubic regression in Excel is done with the LINEST formula, and I know what the formula is:
= LINEST(Range of Y-Values, (Range of X-Values)^COLUMN($A:$C))
There are two problems:
1: The program is getting a type mismatch error with COLUMN
2: since LINEST is an array formula, I have to select a range of cells (1 row X 4 cols), enter the formula in the formula bar, BUT instead of pressing enter, you have to press Ctrl + Shift + Enter (So Excel knows that it an array formula, and not a normal one). Pressing Ctrl + Shift + Enter places {} brackets around the formula, but these brackets cannot be entered manually, or Excel will think what is contained within the brackets is text.
The code I currently have is:
Code:
objWorksheet1.Range("A8:D8").Value = objExcel1.LINEST(objWorksheet1.Range("B1:B6"),objWorksheet1.Range("A1:A6")^COLUMN(objWorksheet1.Range("$A:$C")))
Any help would be appreciated,
Thanks in advance.
-
Re: Regression In Excel
Hi J.J.
If you want to insert an array formula then you must use the "FormulaArray" methode.
e.g.
Activecell.FormulaArray = "=SUM(""A2:B10""*0,2)" or
objWorksheet1.Range("A8").FormulaArray = "=LINEST(mysheet!B1:B6,(mysheet!A1:A6^COLUMN(mysheet!$A:$C)))"
best regards
TheOnly
PS: I don't know anything about this formula, so check it -:)
-
Re: Regression In Excel
Ya, that worked great!
Thanks a million!