Excluding cells from calculations in Excel 2002

[jhans]

Hi all,

is there any way to exclude specific cells from calculations (e.g. RSQ,
SLOPE, etc.) without actually having to delete them in the spreadsheet?

Background is this: I am working on Image Analysis using a pixel patch
tracing routine, which delivers sets of coordinates (x,y). To further
analyse the data, I check for linearity of the resulting trajectories
as a first step. Scatter plots show that some data points are just
outlyers resulting from erroneous pixel tracing. Is there any way to
exlcude these values for simple regression analysis without actually
having to delete the values?
Example: values in rows 1-3 are suitable, No. 4 is an outlyer, 5-6 are
OK again. If I try to just select the appropriate cells (excluding row
4), it gives me error messages. There seems to be a problem with the
delimiter of categories and ranges, because both use a comma (,).
Any way around that? Unfortunately, I cannot think of any calculation
to check the consistency of my data, so it's really down to manual
(visual) inspection and deciding about keeping or discarding the data
point for the analysis.
Any help would be greatly appreciated.

Thanks,

Jo

 

[Jerry W. Lewis]

=RSQ(IF(ROW(A1:A6)<>4,A1:A6),B1:B6)
array entered (Ctrl-Shift-Enter).

This will work with RSQ, STEYX, SLOPE, and INTERCEPT, but not with LINEST.

More generally, use a third column to indicate which observations to
include/exclude.

Jerry

 

[jhans]

Hi Jerry,

thanks for the tip.Seems to work just fine.

Your help is really appreciated.

Thanks

Jo

 

[Jerry W. Lewis]

You're welcome. Glad it helped.

Jerry