Average of multilpe Sumproduct formulas

[jmersing]

I'm using the following formulas to get an average of three different ranges. The problem is if one of the ranges has a zero it waters down the average of the whole formula. For example

If the results for each formula are: 10 - 10 - 0 I would want to return the avergae of 10 not 6.6.

Formula
=AVERAGE((SUMPRODUCT(--(SiteRange=Q$9),--(WeekRange=TrendWk1),--(AreaRange=$B154),Trend!$N$2:$N$1250)),(SUMPRODUCT(--(SiteRange=Q$9),--(WeekRange=TrendWk2),--(AreaRange=$B154),Trend!$N$2:$N$1250)),(SUMPRODUCT(--(SiteRange=Q$9),--(WeekRange=TrendWk3),--(AreaRange=$B154),Trend!$N$2:$N$1250)))

 

[Gerald Higgins]

I guess one way would be to put the individual sumproduct formulas into three separate cells (eg A1, B1 and C1) and then work out your average as

=sum(A1:C1)/countif(A1:C1,">0")

There might be a more elegant solution . . .

 

[Jon von der Heyden]

Or along the same lines:

=AVERAGE(IF(A1:C1<>0,A1:C1))

confirmed with ctrl+shift + enter as this is an array formula

 

[jmersing]

Thanks Guys I'll give it a try