Averages in arrays excluding 0's

[Lara Shook]

Here's my array formula.

{=AVERAGE(($A$9:$A$170>=BeginYear)*($A$9:$A$170<=EndYear)*
($B$9:$B$170=DitchName)*(C$9:C$170>0)*E$9:E$170)}

I have years in column A, ditch names in column B, data
in columns C and D, and ratios in column E. My formula
should calculate the average of the ratios for the
particular ditch for the range of years entered in C177
and C178. Sum works fine. Average assumes 0's for all
rows that don't meet the criteria. Like this:
=average(0,0,0,0,0,0,1.45,0,0,0,2.5,0,0,0,0,0...).

How do I get it to exclude the zeros? I did calculate
the average by using the sum/#rows, but there are some
years where there is no data (in column C), which messes
this average up as well. Thanks in advance!

 

[Bernard V Liengme]

Hi Lara,
I would try using SUMPRODUCT (remember it does not need to nee array
entered)
For the sum of the data you could use
SUMPRODUCT(--($A$9:$A$170>=BeginYear), --($A$9:$A$170<=EndYear),--($B$9:$B$1
70=DitchName),---(C$9:C$170>0,E$9:E$170)
For the count
SUMPRODUCT(--($A$9:$A$170>=BeginYear), --($A$9:$A$170<=EndYear),--($B$9:$B$1
70=DitchName), --(C$9:C$170>0)
For the average use the ratio of the two.

Best wishes
Bernard

 

[Lara Shook]

Thank you so much! That is SOOO wonderful. I am REALLY
NOT familiar with Sumproducts, but I think I'm about to
see what I can learn.

 

[Aladin Akyurek]

=AVERAGE(IF(($A$9:$A$170>=BeginYear)*($A$9:$A$170<=EndYear)*
($B$9:$B$170=DitchName)*(C$9:C$170>0),E$9:E$170))

which must be confirmed with control+shift+enter instead of just with enter.