Jerry,

Great solution! This eliminated the need for VBA in my case. With one
extra column in the worksheet, I was able to track when a new starting
row was signaled for the dynamic arrays (used to generate a line), then
use OFFSET in combination with TREND to return max (and min) residuals
using your formula.

Thanks again for the collective wisdom of the group in your solutions
to this question!

Bob

Jerry W. Lewis wrote:
The maximum absolute residual can be computed by the array formula
=MAX(ABS(ydata-TREND(ydata,xdata,xdata)))
This has the same capability for multi-linear regression as LINEST (which is
the calculation engine for the ATP regression tool). In VBA, you can use
Application.Evaluate to get array evaluation of worksheet functions.

Jerry

"Bob Nelson" wrote:

Greetings, I'm trying to write a custom worksheet function in Excel
2003 that will return the maximum residual value from a linear
regression. Unfortunately, this is not one of the potential outputs
from LINEST. I realize the Analysis ToolPak regression option will
generate a list of residuals from which a maximum value could be
extracted, but this requires generating a new worksheet or large array,
and I'm working with a dynamic array on a worksheet from which the
maximum residual will need to be "updated" in each row of a column.


The SLOPE and INTERCEPT built-in functions perform analogous tasks
(i.e. linear regressions are calculated before specific parameters of
the line generated are returned) but my understanding is that VBA
source code for these is not available? The Analysis ToolPak-VBA seems
promising for a template, but it is password protected-any potential
for accessing this?

So...any thoughts or directions on where to take this? Thanks in
advance for your suggestions.

Bob Nelson