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.

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
---------------
Jacob Skaria


"johnnyk" wrote:

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.

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.