
Author 
Message 
sowalsky

Posted: Wed Nov 21 14:58:00 PST 2007 
Top 
worksheet functions >> LINREGR
I have a data table where I want to put in a variable set of x's and
corresponding y values.
Because the dataset is variable in size, I cannot use the LINREGR to
automatically calculate the regression, because some of the cells in its
reference area might be empty, because of data lack.
Is there a way I can make the regression function automatically (so that it
only uses cells in the reference area where the is data typed in), so that I
do not have to make the chance the referance area for the regression every
time I have at data point (x,y) more or less?
Maybe macros can help me?
Excel311





CharlesChickering

Posted: Wed Nov 21 14:58:00 PST 2007 
Top 
worksheet functions >> LINREGR
You could do this with macros, but have you tried using a combination of
indirect to return only the used range?
See this array formula from Bob Umlas on retrieving the last used cell in a
range, then use the indirect function to build your range to pass as the
argument for your LINREGR function.
http://www.emailoffice.com/excel/arraysbobumlas.html

Charles Chickering
"A good example is twice the value of good advice."
> I have a data table where I want to put in a variable set of x's and
> corresponding y values.
> Because the dataset is variable in size, I cannot use the LINREGR to
> automatically calculate the regression, because some of the cells in its
> reference area might be empty, because of data lack.
> Is there a way I can make the regression function automatically (so that it
> only uses cells in the reference area where the is data typed in), so that I
> do not have to make the chance the referance area for the regression every
> time I have at data point (x,y) more or less?
>
> Maybe macros can help me?
>
>
>





Bernard

Posted: Wed Nov 21 15:11:51 PST 2007 
Top 
worksheet functions >> LINREGR
Visit
http://www.ozgrid.com/Excel/DynamicRanges.htm
to learn how to make a dynamic range
The use LINEST in the form
=LINEST(myYvalues,myXvalues,true.true) where myYvalues and myXvalues are
dynamic range names
best wishes

Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email
>I have a data table where I want to put in a variable set of x's and
> corresponding y values.
> Because the dataset is variable in size, I cannot use the LINREGR to
> automatically calculate the regression, because some of the cells in its
> reference area might be empty, because of data lack.
> Is there a way I can make the regression function automatically (so that
> it
> only uses cells in the reference area where the is data typed in), so that
> I
> do not have to make the chance the referance area for the regression every
> time I have at data point (x,y) more or less?
>
> Maybe macros can help me?
>
>
>






