# Microsoft Office Application Help - Excel Help forum > Excel Formulas & Functions >  >  Average only cells that contain numbers

## April

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

"April" <April@discussions.microsoft.com> wrote in message
news:B5BB65E8-2BE8-44A3-BDEF-B6D8820C3B11@microsoft.com...
>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.

----------


## Ashish Mathur

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.

----------


## Fix8ed

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.

----------


## daddylonglegs

AVERAGE function ignores text anyway so if you have just numbers and --- you can use a simple average like

=AVERAGE(range)

----------


## Tony Valko

It's only appropriate that I reply to this thread!  :Wink: 

If some of your "numbers" are formatted as text then a simple AVERAGE won't work.

Are there any empty cells in the range?

----------

