Array average excluding zeros

**ChrisNor** · 12-06-2010, 08:22 AM

Hi,

I'm using the Average array formula to calculate average for certain values with specific months and year

=AVERAGE(IF(YEAR($C$4:$C$14)=Z9;IF(MONTH($C$4:$C$14)=AA9;$F$4:$F$14;""));"")

The two first IF just checks whether it is the right month and year. The last bit gets the numbers, that is going to make up the average. However my problem it seems is that the average also includes Not true = "", when calculating the average.

**arthurbr** · 12-06-2010, 08:31 AM

Try

Please Login or Register  to view this content.

**DonkeyOte** · 12-06-2010, 08:48 AM

You can add an ISNUMBER test to your existing Array

Please Login or Register  to view this content.

However, given the analysis is monthly and use of XL2007 you could just use:

Please Login or Register  to view this content.

which would be more efficient

**ChrisNor** · 12-06-2010, 08:48 AM

arthurbr:

Thanks, but something is not working with your code

=SUMPRODUCT((YEAR($C$4:$C$14)=Z9)*(MONTH($C$4:$C$14)=AA9)*($F$4:$F$14<>""))/COUNTIF($F$4:$F$14;"<>0")

$F$4:$F$14 if this is 0 then is affects the result, I need to exclude zeros so they do not affect the result

**ChrisNor** · 12-06-2010, 08:52 AM

DonkeyOte:

Thanks your first example worked fine

Only problem is that it gets a divided by zero error, if no data is available for that month/year... how do I fix taht?

**DonkeyOte** · 12-06-2010, 08:55 AM

If you're using XL2007+ you should use the AVERAGEIFS approach and add an IFERROR handler.

If you're using with versions prior to XL2007 then the most appropriate handler will depend on the values being returned - if for ex. you know the Average (where an Avg exist) will always exceed 0 you could avoid double evaluation with:

Please Login or Register  to view this content.

and then apply a Custom Format (if nec.) to mask the 0 output as and when it occurs.
Generally speaking it's always a good idea to avoid double evaluation of "expensive" formulae