Excluding cells in custom TREND function

[mike hutchins]

I'm trying to create a custom function which allows certain rows in my
data to be excluded from a trend calculation. The X-values are dates
in one column with the known Y-values alongside. In a third column I
have an "x" marker in several (non-adjacent) cells which identifies
those X-Y data for exclusion from the calculation.

I've been trying to build new X-Y arrays based on testing the content
of the third array (containing the "x" cells) but just can't make it
work.

Can anyone help with a solution?

Cheers.......MIKE

 

[Jerry W. Lewis]

What is the trend model? For linear, use something like
=SLOPE(IF(UseY,Ydata),Xdata)
You cannot do this with LINEST, but with a little effort, you could
probably adjust the Normal equations similarly and then solve with MINVERSE.

Jerry

 

[mike hutchins]

Hi Jerry
Thanks for your thoughts. I've managed to cobble-together the code
below which works fine where there's only one "daynum" X-array (i.e.
the trend is a straight-line). But how should it be modified to cope
with two (i.e. X and X^2) which produces a curvefit. Can you sort it?
Cheers....

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

 

[Jerry W. Lewis]

Here are minimal changes to your code to fit a quadratic rather than a
linear


....
Dim new_array_daynum(), new_array_devs(), new_array_alldays()
....
ReDim Preserve new_array_alldays(1 To 2, 1 To UBound(arrayx))
For L = 1 To UBound(arrayx)
....
x = arrayx(L, 1)
new_array_alldays(1, L) = x
new_array_alldays(2, L) = x ^ 2
If c = 0 Then
....
ReDim Preserve new_array_daynum(1 To 2, 1 To j)
new_array_daynum(1, j) = x
new_array_daynum(2, j) = x ^ 2
....
XTREND = .Trend(new_array_devs, new_array_daynum, new_array_alldays)
....


Note that while the Trend() function allows the observation index to be
either the first or second argument of the passed arrays, the Preserve
option on ReDim dictates that it must be the second argument.

It usually speeds replies in the newsgroups if you describe the working
environment assumed by your code. For instance, your code requires
named ranges Array_daynum, Array_devs, and ArrayIgnore, since it uses
these named ranges instead of the arguments that are passed to the
function. I presume that the arguments are there to force recalcs when
data changes. This is poor programming practice, since there is nothing
that insures that the arguments will match the named ranges. It would
be better to have no arguments and declare the function
Application.Volatile
for your recalcs. It would be better still to rewrite the code to use
the arguments instead of external information.

Jerry

 

[ma_hutchins]

Hi Jerry, thanks a lot for your further expertise.

 

[Jerry W. Lewis]

You're welcome; glad it helped.

Jerry