Average function

[Jeff Kimbell]

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.

 

[Harlan Grove]

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].