Average only cells that contain numbers

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.

Hi!

Try this:

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

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

Biff

"April" <[email protected]> wrote in message
news:[email protected]...
>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.

Hi,

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

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

Regards

Ashish Mathur

"April" wrote:

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

Those answers didn't work for me but I found something that did. My table is set up with either a number or "---" and I wanted to average just the ones with numbers. I used:

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

It may also be worth mentioning that some of my numbers were formatted as text, as numbers and as general, so that doesn't seem to make a difference. I did do it again averaging percentages in the same way but needed to have those cells and the formula cell formatted as percentages for it to work correctly.