VBA Linest function for polynomial regression on horizontal range


H

Herman

Hello,
I made a function to calculate values and derivatives of polynomial
trends. It works perfect on vertical X en Y ranges but not on
horizontal ranges. I use the Dutch version of Excel 2007. Any ideas
what could clear the problem? I'll be very gratefull.

It goes like this :

Function Polytrend(Xas, Yas, Punt, Graad, ResultType, Optional Per) As
Double
Dim a6 As Double, a5 As Double, a4 As Double, a3 As Double, a2 As
Double, a1 As Double, a0 As Double
Dim Res1 As Double, Res2 As Double
Dim varr()
If IsMissing(Per) Or Per = 0 Then Per = 1
Select Case Graad
Case Is = 1
varr = Evaluate("LINEST(" & Yas.Address & "," & Xas.Address &
"^{1})")
a6 = 0: a5 = 0: a4 = 0: a3 = 0: a2 = 0
a1 = varr(1): a0 = varr(2)
Case Is = 2
varr = Evaluate("LINEST(" & Yas.Address & "," & Xas.Address &
"^{1,2})")
a6 = 0: a5 = 0: a4 = 0: a3 = 0
a2 = varr(1): a1 = varr(2): a0 = varr(3)
Case Is = 3
varr = Evaluate("LINEST(" & Yas.Address & "," & Xas.Address &
"^{1,2,3})")
a6 = 0: a5 = 0: a4 = 0
a3 = varr(1): a2 = varr(2): a1 = varr(3): a0 = varr(4)
Case Is = 4
varr = Evaluate("LINEST(" & Yas.Address & "," & Xas.Address &
"^{1,2,3,4})")
a6 = 0: a5 = 0
a4 = varr(1): a3 = varr(2): a2 = varr(3): a1 = varr(4): a0 =
varr(5)
Case Is = 5
varr = Evaluate("LINEST(" & Yas.Address & "," & Xas.Address &
"^{1,2,3,4,5})")
a6 = 0
a5 = varr(1): a4 = varr(2): a3 = varr(3): a2 = varr(4): a1 =
varr(5): a0 = varr(6)
Case Is = 6
varr = Evaluate("LINEST(" & Yas.Address & "," & Xas.Address &
"^{1,2,3,4,5,6})")
a6 = varr(1): a5 = varr(2): a4 = varr(3): a3 = varr(4): a2 =
varr(5): a1 = varr(6): a0 = varr(7)
End Select
Polytrend = a6 * Punt ^ 6 + a5 * Punt ^ 5 + a4 * Punt ^ 4 + a3 * Punt
^ 3 + a2 * Punt ^ 2 + a1 * Punt + a0


End Function
 
Ad

Advertisements


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

Top