Regression In Excel

[J.J.]

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:

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.

[TheOnly]

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 -

[J.J.]

Ya, that worked great!

Thanks a million!