Quadratic Polynomial fit with LINEST()

S

SamCar

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
 
M

merjet

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
 
D

Dana DeLouis

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

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 said:
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
Click to expand...
 
D

Dana DeLouis

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

SamCar said:
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):
Click to expand...
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
Click to expand...
Click to expand...
 
S

SamCar

Thank you both for replying. Dana- I haven't tried your second example yet
but the first one didn't work for me. Perhaps it is my version of Excel? I
think it's 2002. Merjet- this one worked for me. I was wondering also if
there was a way of writing a function that I could put into one of the cells
ie =linest2() with the arguments being Y and the matrix with X and X^2.

Thanks again, I'm learning VBA bit by bit...

Sam

Dana DeLouis said:
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

SamCar said:
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):
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 
M

merjet

Merjet- this one worked for me. I was wondering also if
there was a way of writing a function that I could put into
one of the cells ie =linest2() with the arguments being Y
and the matrix with X and X^2.
Click to expand...

LinEst2 can be used in that way. So can LinEst. With either you need
to highlight 3 adjacent cells in 1 row and use Ctrl+Shift+Enter
(rather than 1 cell and Enter).

Hth,
Merjet
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Why won't negative sign come over with negative numbers? 1
Script only pulls numbers not minus sign for negatives? 0
VBA Linest function for polynomial regression on horizontal range 0
Trouble with Clear Contents Macro 9
Need help with using LINEST() 3
VBa code for index(linest) 2
Select Case Failing on Array Variable 5
Macro problem - not working properly 6

Top