Quadratic Polynomial fit with LINEST()
 

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

Quadratic Polynomial fit with LINEST()
 

Copying the values w/o the NA's to elsewhere and doing the regression
on the copied values seems much easier. You can delete the copied
values afterwards if you want.

Hth,
Merjet


Option Base 1
Option Explicit

Sub LinReg2Setup()
'Take two column vectors, ignore NA values
'and regress Y on X returning coefficients
Dim rngX As Range
Dim rngY As Range
Dim c As Range
Dim rtn As Variant
Dim iEnd As Integer
Dim iCt As Integer
Dim ws As Worksheet

Set ws = Sheets("Sheet1")
iEnd = ws.Range("A2").End(xlDown).Row
Set rngY = ws.Range("A2:A" & iEnd)
For Each c In rngY
If c < "NA" Then
iCt = iCt + 1
ws.Cells(iEnd + 1 + iCt, 1) = ws.Cells(c.Row, 1)
ws.Cells(iEnd + 1 + iCt, 2) = ws.Cells(c.Row, 2)
ws.Cells(iEnd + 1 + iCt, 3) = ws.Cells(c.Row, 3)
End If
Next c
Set rngY = ws.Range("A" & iEnd + 2 & ":A" & iEnd + 1 + iCt)
Set rngX = ws.Range("B" & iEnd + 2 & ":C" & iEnd + 1 + iCt)
rtn = LinReg2(rngY, rngX)
rngX.Clear
rngY.Clear
ws.Range("F2:H2") = rtn

End Sub

Function LinReg2(Y As Range, X As Range)
LinReg2 = Application.LinEst(Y, X, True, False)
End Function

Quadratic Polynomial fit with LINEST()
 

=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

Quadratic Polynomial fit with LINEST()
 

Hi. This is slightly better...

Sub Example()
Dim Ans
Ans = LinReg2([A1:A10], [B1:B10])
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
d.Add d.Count + 1, Array(Yy(J), Xx(J))
End If
Next J

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

With ActiveWorkbook
.Names.Add "Y", Y
.Names.Add "X", X

v = [LINEST(Y,X^{1,2},TRUE,FALSE)]

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

' Three Coefficients: a*x^2+b*x+c
Debug.Print v(1) 'a
Debug.Print v(2) 'b
Debug.Print v(3) 'c

LinReg2 = v
End Function

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

<snip

"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):

<snip

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