Average with #div

[Guest]

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!

 

[Chip Pearson]

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)

 

[Guest]

Thank you for help. Job done!

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)

 

[roland.raimondi]

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:Sheet5!A1))

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

where is the problem? thank you