Array formulas

[Guest]

I have a problem using array formulas in EXCEL 2002. When I use COUNT or
AVERAGE in an array formula, it treats blank cells as zeros instead of
ignoring them. I can get around it by filling the blanks with text, but this
is not always a good solution. Does anyone know of a better way, and why
array formulas change the way that blank cells are treated?

This is an example of the type of formula I am using

=COUNT(IF(Weights!$B$3:$B$392='Weight Calculations'!$C4,Weights!D$3$392))

 

[Biff]

Hi!

=COUNT(IF(Weights!$B$3:$B$392='Weight

Calculations'$C4,Weights!D$3$392))

The COUNT function counts cells with numbers in them so
I'm assuming that you want a conditional count. Not an
ARRAY, enter normally:

=SUMPRODUCT(--(Weights!$B$3:$B$392='Weight Calculations'!
$C4),--(ISNUMBER(Weights!D$3$392)))

If you want to average based on those same criteria, ARRAY
entered:

=AVERAGE(IF(Weights!$B$3:$B$392='Weight Calculations'!
$C4,IF(Weights!D$3$392>0,Weights!D$3$392)))

Biff