Averaging with #div/0! function

[KPF]

I have two different ranges that are averaged separately at L18 and L49
Each is averaged using the following formula so zeroes will not b
included :
=AVERAGE(IF(L6:L17<>0,L6:L17," "))

My problem is that I then have to average these two results together
and if I get a #div/0! error in one of them, then my formula will no
work. This is the formula that I was trying:

=AVERAGE(IF(L18,L49<>0,L18,L49," "))

Any ideas out there?
Thanks

 

[Peo Sjoblom]

First of all take off the stupid space " " change that to ""

Secondly you can't use an array formula on 2 non contiguous cells

Thirdly make sure that neither of L18 or L49 can return the error,
thus trap the error.. The formula

=AVERAGE(IF(L6:L17<>0,L6:L17,""))

can return the div error if all cells are empty or if any of the cells
contain the div error

=IF(SUM(L6:L17)>0,your_formula,AVERAGE(IF(L6:L17<>0,L6:L17,""))


If any of the cells in L6:L17 holds the error fix that error

 

[KPF]

Thanks Peo,
Unfortunately, this must be above my head, or I have just messed wit
it long enough that I am just not getting it. I am attaching a
example file in case you could possibly help me further.

Sincerely,
Kim Fros

 

[KPF]

Thanks Peo,
Unfortunately, this must be above my head, or I have just messed wit
it long enough that I am just not getting it. I am attaching a
example file in case you could possibly help me further.

Sincerely,
Kim Fros

Attachment filename: example formula.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=53232

 

[Harlan Grove]

I have two different ranges that are averaged separately at L18 and L49.
Each is averaged using the following formula so zeroes will not be
included :
=AVERAGE(IF(L6:L17<>0,L6:L17," "))

You don't need a 3rd argument to IF. You could use the array formula

=AVERAGE(IF(L6:L17<>0,L6:L17))

This would only return #DIV/0! if there were no nonzero numbers in the range. If
that were a possibility, then would you prefer showing nothing, i.e., "", if
there were no average due to no nonzero numbers? If so, change this formula to

=IF(COUNTIF(L6:L17,"<>0"),AVERAGE(IF(L6:L17<>0,L6:L17)),"")

Then you could average the averages using

=IF(COUNT(Subgrp1Mean,Subgrp2Mean),AVERAGE(Subgrp1Mean,Subgrp2Mean),"")

I've seen your follow-ups showing the link to your sample file. Few people open
sample files (I won't myself). They're never needed when it comes to figuring
out formulas, and the pose the risk of macro viruses.