=LINEST(Y,X^{1,2},True,False)] because VBA doesn't allow the abbreviation
X^{1,2}.

Hi. If you don't get a better answer, here is one way.
This calls the function with just the y & x column of data.
It's not too fancy, but maybe there are some ideas you can use.

Option Explicit

Sub Example()
Dim Ans
Ans = LinReg2([A1:A14], [B1:B14])
End Sub

Function LinReg2(Ys, Xs)

Dim J As Long
Dim Yy, Xx
Dim Y, X
Dim v 'Solution Vector

Dim d
Set d = CreateObject("Scripting.Dictionary")

'Load data...
With WorksheetFunction
Yy = .Transpose(Ys.Value)
Xx = .Transpose(Xs.Value)

' Save only valid numeric pairs
For J = LBound(Yy) To UBound(Yy)
If (IsNumeric(Yy(J)) And IsNumeric(Xx(J))) Then
'Key is not important
d.Add d.Count + 1, Array(Yy(J), Xx(J))
End If
Next J

Y = .Transpose(.Index(d.items, 0, 1))
X = .Transpose(.Index(d.items, 0, 2))
End With

With ActiveWorkbook
.Names.Add "Y", Y
.Names.Add "X", X
.Names.Add "Z", [Transpose(Transpose(X)^{1,2})]

v = [LinEst(Y, Z, True, False)]

.Names("Y").Delete
.Names("X").Delete
.Names("Z").Delete
End With

' Three Coefficients:
Debug.Print v(1)
Debug.Print v(2)
Debug.Print v(3)

LinReg2 = v
End Function

--
HTH :)
Dana DeLouis
Windows XP & Office 2007


"SamCar" wrote in message
...
I have the following in a spreadsheet for example:

120.82 165.97 27546.65
NA NA NA
99.61 157.86 24920.88
216.03 137.9 19015.91
NA NA NA
NA NA NA
NA NA NA
NA NA NA
NA NA NA
99.61 161.05 25937.26
124.51 193.57 37468.83
99.61 167.15 27938.5
NA NA NA
NA NA NA

These columns are Y, X and X^2 respectively. I ultimately want to use
LINEST() for a quadratic polynomial fit on the rows not displaying NA (the
rows not displaying NA satisfy a previous condition elsewhere on the
spreadsheet). The number of rows with NA will be variable with each
different
application of the function. I have written a crude function in VBA using
help from this website and elsewhere, and some basic matlab programming
knowledge, for a straight line fit (i.e. just on Y, X):

Option Base 1

Option Explicit



Function LinReg2(Y, X)

'Take two column vectors, create vba arrays, remove empty values and
regress
Y on X

'returning coefficients

Dim ym(), xm()

Dim i, n, j, m As Integer

i = 1

n = 1

j = 1

m = 1

Do Until Y(i, 1) = ""

ReDim Preserve ym(n)

ym(n) = Y(i, 1)

i = i + 1

n = n + 1

Loop

Do Until X(j, 1) = ""

ReDim Preserve xm(m)

xm(m) = X(j, 1)

j = j + 1

m = m + 1

Loop

Dim ym2(), xm2()

Dim b, bb

bb = 1

For b = LBound(ym) To UBound(ym)

If ym(b) < "NA" Then

ReDim Preserve ym2(bb)

ym2(bb) = ym(b)

bb = bb + 1

End If

Next

Dim c, cc

cc = 1

For c = LBound(xm) To UBound(xm)

If xm(c) < "NA" Then

ReDim Preserve xm2(cc)

xm2(cc) = xm(c)

cc = cc + 1

End If

Next

LinReg2 = Application.LinEst(ym2, xm2, True, False)

End Function

However I can't do the same for the quadratic fit [in Excel,
=LINEST(Y,X^{1,2},True,False)] because VBA doesn't allow the abbreviation
X^{1,2}. How can I change my program to prepare Y and X(as an n by 2
matrix
with X in the first column and X^2 in the second) removing the 'NA's as
before? Or alternatively, preparing the three columns as arrays and then
joining the X and X^2 columns before applying the function? I'm very new
to
VBA so I'm not very good at array manipulation.

Many thanks,

Sam