Boolean comparison: range vs. single cell

[msimms]

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)

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

WHY ? key issue here: 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 ?

Is there a better, MORE APPROPRIATE way to do this ?

[Alan]

Try
=IF(COUNTIF(A2:A26,"<="&H1)>0,1,0)
or
=IF(COUNTIF(A2:A26,"<="&H1),1,0)
Regards,
Alan.
"msimms" <[email protected]> wrote in
message news:[email protected]...
>
> 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)
>
> 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
>
> WHY ? key issue here: 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 ?
>
> Is there a better, MORE APPROPRIATE way to do this ?
>
>
> --
> msimms
> ------------------------------------------------------------------------
> msimms's Profile:
> http://www.excelforum.com/member.php...o&userid=30382
> View this thread: http://www.excelforum.com/showthread...hreadid=500441
>

[Harlan Grove]

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 here: 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.

[msimms]

re: 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.

Wow, thanks guys for the fast response....
but how does a TRUE or FALSE result get translated into a NUMERIC VALUE ?
0=false 1=true ????
(I tried entering "=True()" into a cell and formatting it, but it did not return a number)

If you notice, an array is then multiplied to another array....
I am assuming the number of multiplications is only equivalent to the length of the vector, correct ?

[Harlan Grove]

msimms wrote...
....
>but how does a TRUE or FALSE result get translated into a NUMERIC VALUE?
....

Boolean (TRUE/FALSE) values used as operands to arithmetic operators
are automatically converted into 1s for TRUE and 0s for FALSE.