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.

Answer: Average only cells that contain numbers
 

To average only the cells that contain numbers and ignore cells that contain #DIV/0, you can use the AVERAGEIF function in Excel. Here are the steps to do it:

1. Select the cell where you want to display the average.
2. Type the formula "=AVERAGEIF(range,"0")" in the formula bar, where "range" is the range of cells you want to average.
3. Press Enter to calculate the average.

Note that this formula will only work if the cells containing #DIV/0 are actually blank cells with the #DIV/0 error displayed. If the cells containing #DIV/0 are not blank, you will need to use a different formula to ignore them. One option is to use the IFERROR function to replace the #DIV/0 error with a blank cell, like this:

Where "formula" is the formula that is causing the #DIV/0 error. This will replace the error with a blank cell, which can then be included in the AVERAGEIF formula without affecting the average.

Average only cells that contain numbers
 

Hi!

Try this:

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

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

Biff

"April" wrote in message
...
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.

Average only cells that contain numbers
 

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.