msimms wrote...
This is driving me crazy....I am trying to debug and understand some
cell computations using Excel 2003...but all it does it return an
#VALUE error:
=IF($A$2:$A$26<=H$1,1,0)

If you don't enter this as an array formula, it'll return #VALUE!. No
good reason, it's just they way Excel's IF function works. However,
it'd also return #VALUE! if H1 evaluated to #VALUE!. Also, if entered
as an array formula, it'd return an array of 1s and 0s.

But the following WORKS and produces the correct result:
=100*SUM(IF(($A$2:$A$26<=G$1)*($B$2:$B$26<=$F2),$ C$2:$C$26,0))/$C$27

This shouldn't have worked unless you enter it as an array formula,
though it'd return a single value rather than an array.

WHY ? key issue he how to properly compare a vector to a single
cell.

My take on this: $A$2:$A$26<=G$1
Each cell in Column A from 2-26 is being compared to G1.
If ANY ONE OF THEM is less than G1, then the result is "True"....
correct ?

Wrong. It compares *EACH* cell in A2:A26 to G1 and returns an *ARRAY*
of TRUE/FALSE results. If you want to check whether ANY cell in A2:A26
equals G1 and return a SINGLE TRUE/FALSE result, use
COUNTIF($A$2:$A$26,G$1)0.