Try this array formula** :

=AVERAGE(IF(ISNUMBER(A1:A10),A1:A10))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"johnnyk" wrote in message
...
I have a column, with each cell containing a number or the "#value" error.
(Please forget about the "#value" problem; I know why it comes up). I am
looking for a formula that will average the column but exclude the
"#value"
cells. Right now my average = "#value" due to those cells.

I am looking for a one time formula that will work. In other words, I
don't
want to do a "=average" and just pick out the cells with numbers because I
am
constantly taking out rows and adding some in.

In other words, I am looking for the formula that says, "average only the
cells with numbers". Any help much appreciated.