Average function

When I use the average function over a range of cells that
includes a cell with "#VALUE!", the function returns
with "#VALUE!". The description of the average function
is that it ignores non-numerical values. That is what I
want. How can I get it to ignore "#VALUE!"? Thanks.

AVERAGE does not ignore error values. You should either try to eliminate the
cause of #VALUE! error or use:

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

which you need to confirm with control+shift+enter, instead of just with
enter.

"Jeff Kimbell" wrote in message
...
When I use the average function over a range of cells that
includes a cell with "#VALUE!", the function returns
with "#VALUE!". The description of the average function
is that it ignores non-numerical values. That is what I
want. How can I get it to ignore "#VALUE!"? Thanks.

"Jeff Kimbell" wrote...
When I use the average function over a range of cells that
includes a cell with "#VALUE!", the function returns
with "#VALUE!". The description of the average function
is that it ignores non-numerical values. That is what I
want. How can I get it to ignore "#VALUE!"? Thanks.

=AVERAGE(IF(ISNUMBER(YourRangeHere),YourRangeHere) )

entered as an array formula, meaning hold down [Ctrl] and [Shift] keys before
pressing [Enter].

--
Never attach files.
Snip unnecessary quoted text.
Never multipost (though crossposting is usually OK).
Don't change subject lines because it corrupts Google newsgroup archives.