Skip blank or N/A in data analysis

[Sammy]

Hi,
I try to do data analysis (such as regression) to two
data columns X ~ Y. Under some condition, some of the Y
values are blank or (#N/A). When I do X-Y chart, those
points are ignored. However, when I select the same
columns to do regress or any other data analysis, I got
the error saying "Input range containing non-numeric
data". I don't want to put any number like 0 for those to
distort my result. I just want to ignore or skip those
N/A data. Is there a way I can do it without removing
those data? Because the N/A location depends on a
variable (a condition). I can not remove those rows every
time I change the other condition.
Thank you for any input!

Sammy

 

[Jerry W. Lewis]

=SLOPE(IF(ISNUMBER(ydata),ydata),IF(ISNUMBER(xdata),xdata))
array entered (Ctrl-Shift-Enter).

Use INTERCEPT, RSQ, STEYX, etc. similarly.

You cannot do this with LINEST, since LINEST does not permit missing data.

Jerry

 

[Guest]

Jerry,
Thank you for your reply.
But when I select the block of data to do Regression
analysis, I still get the non-numeric error for those
empty cells which I don't want to be replaced with 0 or
any other value. How can I bypass this error?

Sammy

 

[Jerry W. Lewis]

What sort of error are you getting from the slope function? If #VALUE!,
then you have not array entered it.

Your characterization as "the non-numeric error" makes me suspect that
you are using the regression tool in the Analysis ToolPak. That uses
LINEST, which I previously noted cannot deal with missing data of any type.

As I previously noted, the only way to do what you want in Excel is to
use worksheet functions with embedded IF statements.
http://groups.google.com/[email protected]
discusses how to use worksheet functions to calculate all items returned
by LINEST, for the special case of simple linear regression.

Jerry