This formula works nicely as an array for counting the number of values.

=COUNT(IF(('Enroll I'!$C$2:$C$2741=A4)*('Enroll
I'!$H$2:$H$27410)*(YEAR('Enroll I'!$F$2:$F$2741)=2006+{0,1}),'Enroll
I'!$H$2:$H$2741,""))

I was expecting this formula to work nicely as an array for getting the
median (I may want to change to average) the number of values.

=MEDIAN(IF(('Enroll I'!$C$2:$C$2741=A4)*('Enroll
I'!$H$2:$H$27410)*(YEAR('Enroll I'!$F$2:$F$2741)=2006+{0,1}),'Enroll
I'!$H$2:$H$2741,""))

.....Instead I get the infamous "#Value"

Ideas? Thanks.

Both formulas work OK for me.

The MEDIAN formula will only throw a #VALUE! error if any of the ranges
already contain #VALUE! errors or if there is TEXT in F2:F2741.

--
Biff
Microsoft Excel MVP


"PAL" wrote in message
...
This formula works nicely as an array for counting the number of values.

=COUNT(IF(('Enroll I'!$C$2:$C$2741=A4)*('Enroll
I'!$H$2:$H$27410)*(YEAR('Enroll I'!$F$2:$F$2741)=2006+{0,1}),'Enroll
I'!$H$2:$H$2741,""))

I was expecting this formula to work nicely as an array for getting the
median (I may want to change to average) the number of values.

=MEDIAN(IF(('Enroll I'!$C$2:$C$2741=A4)*('Enroll
I'!$H$2:$H$27410)*(YEAR('Enroll I'!$F$2:$F$2741)=2006+{0,1}),'Enroll
I'!$H$2:$H$2741,""))

....Instead I get the infamous "#Value"

Ideas? Thanks.