null vs zero in regression analysis

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

 

[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 Vaughn]

Thank you.

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