Array formulas
 

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:D$392))

Hi!

=COUNT(IF(Weights!$B$3:$B$392='Weight
Calculations'$C4,Weights!D$3:D$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:D$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:D$3920,Weights!D$3:D$392)))

Biff

-----Original Message-----
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:D$392))
.