Excluding cells in custom TREND function
 

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

Excluding cells in custom TREND 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

Excluding cells in custom TREND function
 

Thanks for the pointers, Tushar, most helpful.

Excluding cells in custom TREND function
 

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