The Excel function Linest does not return a value if your independant
and dependant variable count :i.e. Linest(C3:C18,D3:D6) do not match.
Is there a way to code this in VBA?

What do you propose to do with observations C7:C18 that have no explanatory
variables?

Jerry

" wrote:

The Excel function Linest does not return a value if your independant
and dependant variable count :i.e. Linest(C3:C18,D3:D6) do not match.
Is there a way to code this in VBA?

Linest works on XY pairs. It isn't designed to work with missing data points.

In your example why wouldn't you use C3:C6, D3:D6

--
Regards,
Tom Ogilvy


" wrote:

The Excel function Linest does not return a value if your independant
and dependant variable count :i.e. Linest(C3:C18,D3:D6) do not match.
Is there a way to code this in VBA?

On Jun 19, 3:16 pm, Tom Ogilvy
wrote:
Linest works on XY pairs. It isn't designed to work with missing data points.

In your example why wouldn't you use C3:C6, D3:D6

--
Regards,
Tom Ogilvy



" wrote:
The Excel function Linest does not return a value if your independant
and dependant variable count :i.e. Linest(C3:C18,D3:D6) do not match.
Is there a way to code this in VBA?- Hide quoted text -

- Show quoted text -

It's a problem when you have a data dump in columns (Linest at the
bottom cell) where there is varying rows between columns- trying to
automate the beta calculation whenever information is updated.

Are you the one that was saying the client wants you to do a t-test based on
using a Beta distribution rather than a Normal distribution. While that
doesn't compute for me, you might take this opportunity to ask Jerry - he has
the Phd in Statistics and may be able to figure out what you are talking
about.

Anyway, you might use dynamic range formulas to only do the Linest on
paired values. http://www.contextures.com/xlNames01.html#Dynamic

--
Regards,
Tom Ogilvy






" wrote:

On Jun 19, 3:16 pm, Tom Ogilvy
wrote:
Linest works on XY pairs. It isn't designed to work with missing data points.

In your example why wouldn't you use C3:C6, D3:D6

--
Regards,
Tom Ogilvy



" wrote:
The Excel function Linest does not return a value if your independant
and dependant variable count :i.e. Linest(C3:C18,D3:D6) do not match.
Is there a way to code this in VBA?- Hide quoted text -

- Show quoted text -

It's a problem when you have a data dump in columns (Linest at the
bottom cell) where there is varying rows between columns- trying to
automate the beta calculation whenever information is updated.

You still have not explained what you want done with all the extra cells.

I will take a wild guess and assume that you have a constant model (D3:D6)
and a data loading process that puts exactly four numeric values in proper
order, but with an unpredictable number of line feeds (<=16) in and around
the data. As a result, I am guessing that you want to align the first
numeric value in C3:C18 with D3, the second numeric value in C3:C18 with D4,
....

The most reliable way to do this in VBA is to loop through the cells in
C3:C18 and populate an array (dimensioned to hold four values) with the
values that you find. That way you can include a diagnostic as to whether
there really are exactly four values or not.

Doing it in an array formula is a bit tricky. If the values in C3:C18 will
always be ascending, you could use

=LINEST(SMALL(C3:C18,{1;2;3;4}),D3:D6)

Otherwise the problem becomes much more messy. The following will work for
the limited size problem that you posed, but does not extend to large sample
sizes

=LINEST(CHOOSE(SMALL(IF(ISNUMBER(C3:C18),ROW(C3:C1 8)),{1;2;3;4})-2,C3,C4,C5,C6,C7,C8,C9,C10,C11,C12,C13,C14,C15,C16 ,C17,C18),D3:D6)

The more obvious approaches using INDEX(), OFFSET(), or INDIRECT() fail,
because their results are not recognized as arrays within array formulas.

Note that the VBA function EVALUATE() will evaluate the string argument as
though it were an array entered Excel expression.

Jerry

" wrote:

On Jun 19, 3:16 pm, Tom Ogilvy
wrote:
Linest works on XY pairs. It isn't designed to work with missing data points.

In your example why wouldn't you use C3:C6, D3:D6

--
Regards,
Tom Ogilvy



" wrote:
The Excel function Linest does not return a value if your independant
and dependant variable count :i.e. Linest(C3:C18,D3:D6) do not match.
Is there a way to code this in VBA?- Hide quoted text -

- Show quoted text -

It's a problem when you have a data dump in columns (Linest at the
bottom cell) where there is varying rows between columns- trying to
automate the beta calculation whenever information is updated.

Sorry for the late response!

Good approach. Thanks alot- got it, use a loop to match the cells in
an array to retrieve a beta (an index vs a fund). Great!

Hello,

You are perhaps looking for an "outlier resistant" beta?

A pragmatic approach:
http://www.sulprobil.com/html/outlie...tant_beta.html

Regards,
Bernd

You're welcome, glad it helped.

Jerry

" wrote:

Sorry for the late response!

Good approach. Thanks alot- got it, use a loop to match the cells in
an array to retrieve a beta (an index vs a fund). Great!