Custom function for max residual from linear regression

[Bob Nelson]

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

[[email protected]]

Hi Bob.

I would suggest the following:
1) Obtain the slope & intercept from LINEST (or SLOPE and INTERCEPT)
using your code
2) Calculate the maximum residual for each datapoint using code
eg
for i = 1 to N
residual = yvalue(i) - (intercept + slope * xvalue(i))
' or residual = abs(yvalue(i) - (intercept + slope *
xvalue(i)))
if residual > maxresidual then maxresidual = residual
next i

This should be pretty fast so long as you don't have too many
datapoints.

Cheers
Richard

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

[Bob Nelson]

Hi Richard,

Elegant solution! Many thanks! I had been trying to go work out a
tenable loop, but was having trouble with the syntax and had not
considered using the built-in functions in VBA to get me to the
residuals.

By the way, this is my first foray into a usenet group and I am in awe
of the helpfulness and willingness to field questions from complete
strangers. I look forward to gathering enough expertise so that I can
"pay it forward".

Thanks again for saving me many hours of headscratching and best of
luck with your own adventures with Excel.

Cheers,
Bob

[email protected] wrote:
> Hi Bob.
>
> I would suggest the following:
> 1) Obtain the slope & intercept from LINEST (or SLOPE and INTERCEPT)
> using your code
> 2) Calculate the maximum residual for each datapoint using code
> eg
> for i = 1 to N
> residual = yvalue(i) - (intercept + slope * xvalue(i))
> ' or residual = abs(yvalue(i) - (intercept + slope *
> xvalue(i)))
> if residual > maxresidual then maxresidual = residual
> next i
>
> This should be pretty fast so long as you don't have too many
> datapoints.
>
> Cheers
> Richard
>
> 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

[Bob Nelson]

Hi Richard,

Elegant solution! Many thanks! I had been trying to go work out a
tenable loop, but was having trouble with the syntax and had not
considered using the built-in functions in VBA to get me to the
residuals.

By the way, this is my first foray into a usenet group and I am in awe
of the helpfulness and willingness to field questions from complete
strangers. I look forward to gathering enough expertise so that I can
"pay it forward".

Thanks again for saving me many hours of headscratching and best of
luck with your own adventures with Excel.

Cheers,
Bob

[email protected] wrote:
> Hi Bob.
>
> I would suggest the following:
> 1) Obtain the slope & intercept from LINEST (or SLOPE and INTERCEPT)
> using your code
> 2) Calculate the maximum residual for each datapoint using code
> eg
> for i = 1 to N
> residual = yvalue(i) - (intercept + slope * xvalue(i))
> ' or residual = abs(yvalue(i) - (intercept + slope *
> xvalue(i)))
> if residual > maxresidual then maxresidual = residual
> next i
>
> This should be pretty fast so long as you don't have too many
> datapoints.
>
> Cheers
> Richard
>
> 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

[Dana DeLouis]

>>> ... The Analysis ToolPak-VBA seems
>>> promising for a template, but it is password protected-any potential
>>> for accessing this?

Hi. Not sure what you mean for "accessing" the ATP, so I'll just throw
this out as an idea.
As a small example, the ATP function "LCM" is not available to vba.
However, in the vba editor, go to Tools | References...and select
"atpvbaen.xls"
Now you can run ATP codes like the following simple example:

Sub Demo()
MsgBox LCM(3, 5)
End Sub

To see what's available in the vba editor, pull up the vba editor, and
open the object browser (F2). Then, pull up just the atpvbaen.xls
library from the upper left corner.

HTH
Dana DeLouis



Bob Nelson wrote:
> Hi Richard,
>
> Elegant solution! Many thanks! I had been trying to go work out a
> tenable loop, but was having trouble with the syntax and had not
> considered using the built-in functions in VBA to get me to the
> residuals.
>
> By the way, this is my first foray into a usenet group and I am in awe
> of the helpfulness and willingness to field questions from complete
> strangers. I look forward to gathering enough expertise so that I can
> "pay it forward".
>
> Thanks again for saving me many hours of headscratching and best of
> luck with your own adventures with Excel.
>
> Cheers,
> Bob
>
> [email protected] wrote:
>> Hi Bob.
>>
>> I would suggest the following:
>> 1) Obtain the slope & intercept from LINEST (or SLOPE and INTERCEPT)
>> using your code
>> 2) Calculate the maximum residual for each datapoint using code
>> eg
>> for i = 1 to N
>> residual = yvalue(i) - (intercept + slope * xvalue(i))
>> ' or residual = abs(yvalue(i) - (intercept + slope *
>> xvalue(i)))
>> if residual > maxresidual then maxresidual = residual
>> next i
>>
>> This should be pretty fast so long as you don't have too many
>> datapoints.
>>
>> Cheers
>> Richard
>>
>> 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
>

[Bob Nelson]

Thanks for your message, Dana. I'm unable to view the VBA code for
atpvbaen.xls, though I can see the description for the "Regress"
subroutine in the object browser. The View | Code option is greyed out.
I'm presuming I need to be recognized as a developer (which I am not)
to gain access to the code.

Thanks again,
Bob

Dana DeLouis wrote:
> >>> ... The Analysis ToolPak-VBA seems
> >>> promising for a template, but it is password protected-any potential
> >>> for accessing this?
>
> Hi. Not sure what you mean for "accessing" the ATP, so I'll just throw
> this out as an idea.
> As a small example, the ATP function "LCM" is not available to vba.
> However, in the vba editor, go to Tools | References...and select
> "atpvbaen.xls"
> Now you can run ATP codes like the following simple example:
>
> Sub Demo()
> MsgBox LCM(3, 5)
> End Sub
>
> To see what's available in the vba editor, pull up the vba editor, and
> open the object browser (F2). Then, pull up just the atpvbaen.xls
> library from the upper left corner.
>
> HTH
> Dana DeLouis
>
>
>
> Bob Nelson wrote:
> > Hi Richard,
> >
> > Elegant solution! Many thanks! I had been trying to go work out a
> > tenable loop, but was having trouble with the syntax and had not
> > considered using the built-in functions in VBA to get me to the
> > residuals.
> >
> > By the way, this is my first foray into a usenet group and I am in awe
> > of the helpfulness and willingness to field questions from complete
> > strangers. I look forward to gathering enough expertise so that I can
> > "pay it forward".
> >
> > Thanks again for saving me many hours of headscratching and best of
> > luck with your own adventures with Excel.
> >
> > Cheers,
> > Bob
> >
> > [email protected] wrote:
> >> Hi Bob.
> >>
> >> I would suggest the following:
> >> 1) Obtain the slope & intercept from LINEST (or SLOPE and INTERCEPT)
> >> using your code
> >> 2) Calculate the maximum residual for each datapoint using code
> >> eg
> >> for i = 1 to N
> >> residual = yvalue(i) - (intercept + slope * xvalue(i))
> >> ' or residual = abs(yvalue(i) - (intercept + slope *
> >> xvalue(i)))
> >> if residual > maxresidual then maxresidual = residual
> >> next i
> >>
> >> This should be pretty fast so long as you don't have too many
> >> datapoints.
> >>
> >> Cheers
> >> Richard
> >>
> >> 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
> >

[Jerry W. Lewis]

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
>
>

[Bob Nelson]

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
> >
> >

[Jerry W. Lewis]

You're welcome. Glad it helped.

Jerry

"Bob Nelson" wrote:

> 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