exclude non numbers

[johnnyk]

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.

 

[Jacob Skaria]

Please note that this is an array formula. You create array formulas in the
same way that you create other formulas, except you press CTRL+SHIFT+ENTER to
enter the formula. If successful in 'Formula Bar' you can notice the curly
braces at both ends like "{=<formula>}"

=AVERAGE(IF(ISNUMBER(A2:A20),A2:A20))

If this post helps click Yes

 

[T. Valko]

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.