null vs zero in regression analysis

R

R Vaughn

Is there a way to run a regression analysis with input data that contain some
null cells? I have a large matrix of data within there are many cells
containing zero value. As I continue to add data to the matrix, rather than
be forced to enter the number "0" in all zero value cells of a regression
input range, I prefer to just leave them blank. Any help would be
appreciated.
 
J

Jerry W. Lewis

LINEST (which is the basis for the ATP regression tool) does not permit empty
cells, missing or non-numeric values. If you are doing simple linear
regression, the functions SLOPE, INTERCEPT, STEYX, and RSQ ignore empty and
non-numeric cells (provided they do not contain error values).

http://groups.google.com/group/microsoft.public.excel.worksheet.functions/msg/d6a03470e7a1c650
gives equivalents for all the results from LINEST for simple linear
regression. You could embed array if formulas to insure that 1-variable
formulas discard values that correspond to null cells in the other variable.
Everything in the ATP regression analysis derive from these values.

For multiple regression, you will either have to roll your own or else
maintain a separate copy of the data that has no null values.

If the data can be arranged such that the null values occur below the
complete data, then you might be able to use a dynamic range
http://www.ozgrid.com/Excel/DynamicRanges.htm

Jerry
 
R

R Vaughn

Thank you.


Jerry W. Lewis said:
LINEST (which is the basis for the ATP regression tool) does not permit empty
cells, missing or non-numeric values. If you are doing simple linear
regression, the functions SLOPE, INTERCEPT, STEYX, and RSQ ignore empty and
non-numeric cells (provided they do not contain error values).

http://groups.google.com/group/microsoft.public.excel.worksheet.functions/msg/d6a03470e7a1c650
gives equivalents for all the results from LINEST for simple linear
regression. You could embed array if formulas to insure that 1-variable
formulas discard values that correspond to null cells in the other variable.
Everything in the ATP regression analysis derive from these values.

For multiple regression, you will either have to roll your own or else
maintain a separate copy of the data that has no null values.

If the data can be arranged such that the null values occur below the
complete data, then you might be able to use a dynamic range
http://www.ozgrid.com/Excel/DynamicRanges.htm

Jerry
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top