Hi,

I would like to use the LINEST() function on two columns of data (Y,X for
example) in a worksheet in Excel- but I only want to include certain values.
I've used a function to return the X and Y values for the entries that
satisfy certain criteria, and #N/A for those that don't (e.g. there are some
rows that are full and rows that both have #N/A). Is there any way of only
including the elements that are numbers (as opposed to na) in the regression?

Alternatively, I've sorted the array so that the valued elements are at the
top and the #N/A elements are at the bottom. In theory I could then apply the
regression to the first n rows of the array only(where n = no. of rows of
numbers). Is there VBA code that I could write to do this? I'm struggling
with the element indexing system.

Thanks,

Sam

LINEST() can only work with contiguous data. You must either create
intermediate arrays that contain only the data that you want to use, or else
not use LINEST().

If you are doing simple linear regression instead of multiple regression,
then you can use SLOPE(), INTERCEPT(). RSQ(), STEYX(), etc. These functions
do permit missing values, but error values (such as #N/A propagate, so you
probably want to use "NA" or "" for the values to omit.

Jerry

"SamCar" wrote:

Hi,

I would like to use the LINEST() function on two columns of data (Y,X for
example) in a worksheet in Excel- but I only want to include certain values.
I've used a function to return the X and Y values for the entries that
satisfy certain criteria, and #N/A for those that don't (e.g. there are some
rows that are full and rows that both have #N/A). Is there any way of only
including the elements that are numbers (as opposed to na) in the regression?

Alternatively, I've sorted the array so that the valued elements are at the
top and the #N/A elements are at the bottom. In theory I could then apply the
regression to the first n rows of the array only(where n = no. of rows of
numbers). Is there VBA code that I could write to do this? I'm struggling
with the element indexing system.

Thanks,

Sam

Thanks Jerry, useful stuff to know. I've found a way around it now.

Sam

"Jerry W. Lewis" wrote:

LINEST() can only work with contiguous data. You must either create
intermediate arrays that contain only the data that you want to use, or else
not use LINEST().

If you are doing simple linear regression instead of multiple regression,
then you can use SLOPE(), INTERCEPT(). RSQ(), STEYX(), etc. These functions
do permit missing values, but error values (such as #N/A propagate, so you
probably want to use "NA" or "" for the values to omit.

Jerry

"SamCar" wrote:

Hi,

I would like to use the LINEST() function on two columns of data (Y,X for
example) in a worksheet in Excel- but I only want to include certain values.
I've used a function to return the X and Y values for the entries that
satisfy certain criteria, and #N/A for those that don't (e.g. there are some
rows that are full and rows that both have #N/A). Is there any way of only
including the elements that are numbers (as opposed to na) in the regression?

Alternatively, I've sorted the array so that the valued elements are at the
top and the #N/A elements are at the bottom. In theory I could then apply the
regression to the first n rows of the array only(where n = no. of rows of
numbers). Is there VBA code that I could write to do this? I'm struggling
with the element indexing system.

Thanks,

Sam

Try this link: http://www.wwz.unibas.ch/witheo/yvan....html#LinEstNA
That should solve your problem, I hope.


EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com