Average with #div
 

Hello.
I need to make an average but some of the cells in range contain#div because
they are not completed with data in range in order to make my average
function work.
What i need i a formula which calculates the average of the cells with
content, but ignores the ones with #div.
Thank you!

Average with #div
 

Alin,

Try the following array formula. Of course, change A1:A5 to your range:

=AVERAGE(IF(ISNUMBER(A1:A5),A1:A5,FALSE))

Since this is an array formula, you must press CTRL+SHIFT+ENTER rather than
just ENTER when you first enter the formula and whenever you edit it later.
If you do this properly, Excel will display the formula enclosed in curly
braces { }. For more information about array formulas, see
www.cpearson.com/excel/array.htm


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)



"Alin Stanciu" wrote in message
...
Hello.
I need to make an average but some of the cells in range contain#div
because
they are not completed with data in range in order to make my average
function work.
What i need i a formula which calculates the average of the cells with
content, but ignores the ones with #div.
Thank you!

Average with #div
 

Thank you for help. Job done!

"Chip Pearson" wrote:

Alin,

Try the following array formula. Of course, change A1:A5 to your range:

=AVERAGE(IF(ISNUMBER(A1:A5),A1:A5,FALSE))

Since this is an array formula, you must press CTRL+SHIFT+ENTER rather than
just ENTER when you first enter the formula and whenever you edit it later.
If you do this properly, Excel will display the formula enclosed in curly
braces { }. For more information about array formulas, see
www.cpearson.com/excel/array.htm


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)



"Alin Stanciu" wrote in message
...
Hello.
I need to make an average but some of the cells in range contain#div
because
they are not completed with data in range in order to make my average
function work.
What i need i a formula which calculates the average of the cells with
content, but ignores the ones with #div.
Thank you!

Average with #div
 

hello

have a problem that continous from your solution.

my cells are on different sheets so i try to use

=AVERAGE(IF(ISNUMBER(Sheet1:Sheet5!A1),Sheet1:Shee t5!A1))

and i use the array function, but i always get a 0 as result

where is the problem? thank you