Excluding cells in custom TREND function

M

mike hutchins

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
 
T

Tushar Mehta

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
 

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