Skipping bad data in cells

[nicmoo]

How do I get excel to skip over bad cells (containing none numeric errors)
and procede with auto calculation?

 

[Gary''s Student]

In place of:
=SUM(A1:A10)
use:
=SUM(IF(ISERROR(A1:A10),"",(A1:A10)))

this is an array formula that must be entered with CNTRL-SHFT-ENTER rather
than just the ENTER key.

 

[nicmoo]

Thanks for the reply...but what I'm looking for is something that will ignore
anything other than a numeric values ISERROR only work for specific errors.
The data I'm using in imported from another database it displays a text
description if the value is none numeric e.g bad input or shutdown.

 

[Gord Dibben]

Fix the bad cells is the best solution.

Why are they bad?

Can you trap for the errors and return "" instead of errors?

Posr a few examples of bad cells.


Gord Dibben MS Excel MVP

 

[Dave Peterson]

You may want to share the formula. For instance:

=sum(a1:a10)

will ignore text cells.

 

[nicmoo]

Example Data

29-Jan-09 05:30 1.0000 35.344 -34.344
30-Jun-08 08:00 356.0000 29.710 326.290
02-Dec-02 09:50 120.0000 21.236 98.764
28-Nov-02 09:20 280.0000 17.800 262.200
21-Nov-02 17:26 Pt Created 20.579 #VALUE!
#N/A #VALUE! #N/A No Good Data
#N/A #VALUE! #N/A No Good Data
#N/A #VALUE! #N/A No Good Data

I need the speadsheet to ignore the cells with none numeric entries as the
cells are used in calculations for an SPC chart. columns B C and D extract
data from an external database (PI). I think I may need some sort of macro
to do this?

Help!