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!

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" <(E-Mail Removed)> wrote in message
news:08B47FAA-7FC9-4D9A-B401-(E-Mail Removed)...
> 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!

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" <(E-Mail Removed)> wrote in message
> news:08B47FAA-7FC9-4D9A-B401-(E-Mail Removed)...
> > 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!
>

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