Average only cells that contain numbers

[Guest]

I have set up a table in which I need to average only the cells that contain
numbers and I need to ignore cells that contain #DIV/0 from a formula that I
have entered without changing them to zero. I still need the formula for the
other cells. I need to setup a formula that is consistent for workbook so I
will need to be able to include the #DIV/0 cells in my formula but still
average only the cells that have a number in them. I have tried the Count
function but I need it to recognize the numbers and average them while
ignoring #DIV/0.

 

[Biff]

Hi!

Try this:

Entered as an array using the key combo of CTRL,SHIFT,ENTER:

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

Biff

 

[Guest]

Hi,

Another array formula (Ctrl+Shift+Enter) for achieving this result is:

=AVERAGE(IF(NOT(ISERROR(range)),range))

Regards

Ashish Mathur