I'm trying to create a user defined function for a polynominal
regression that allows specified data to be excluded from the
calculation.
The following code works fine where there's only one "daynum" X-array
(i.e.
the trend is a straight-line). How should it be modified to cope
with two X-arrays (i.e. X and X^2) to produce a curvefit?
TIA....

Function XTREND(Array_daynum, Array_devs, ArrayIgnore)
Dim new_array_daynum(), new_array_devs()

arrayx = Range("Array_daynum").Value
arrayy = Range("Array_devs").Value
arrayc = Range("ArrayIgnore").Value

For L = 1 To UBound(arrayx)
c = arrayc(L, 1)
If c = 0 Then
x = arrayx(L, 1)
y = arrayy(L, 1)
j = j + 1
ReDim Preserve new_array_daynum(1 To j)
new_array_daynum(j) = x
ReDim Preserve new_array_devs(1 To j)
new_array_devs(j) = y
End If
Next L

With Application.WorksheetFunction
XTREND = .Trend(new_array_devs, new_array_daynum, Array_daynum)
End With
End Function

As far as using polynomials with LINEST in VBA see my posts in
http://groups-
beta.google.com/group/microsoft.public.excel.charting/browse_thread/thr
ead/bd158645bcad9995

and

http://groups-
beta.google.com/group/microsoft.public.excel/browse_thread/thread/9130f
e9e85f1c476

As noted above, the posts deal with LINEST. Hopefully, the same works
for TREND. If not, use LINEST to get the coefficients, and create the
TREND results with a loop.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
I'm trying to create a user defined function for a polynominal
regression that allows specified data to be excluded from the
calculation.
The following code works fine where there's only one "daynum" X-array
(i.e.
the trend is a straight-line). How should it be modified to cope
with two X-arrays (i.e. X and X^2) to produce a curvefit?
TIA....

Function XTREND(Array_daynum, Array_devs, ArrayIgnore)
Dim new_array_daynum(), new_array_devs()

arrayx = Range("Array_daynum").Value
arrayy = Range("Array_devs").Value
arrayc = Range("ArrayIgnore").Value

For L = 1 To UBound(arrayx)
c = arrayc(L, 1)
If c = 0 Then
x = arrayx(L, 1)
y = arrayy(L, 1)
j = j + 1
ReDim Preserve new_array_daynum(1 To j)
new_array_daynum(j) = x
ReDim Preserve new_array_devs(1 To j)
new_array_devs(j) = y
End If
Next L

With Application.WorksheetFunction
XTREND = .Trend(new_array_devs, new_array_daynum, Array_daynum)
End With
End Function

Thanks for the pointers, Tushar, most helpful.

In article .com,
says...
Thanks for the pointers, Tushar, most helpful.


You are welcome.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions