software

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$26lt;=H$1,1,0)

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

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

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

Is there a better, MORE APPROPRIATE way to do this ?--
msimms
------------------------------------------------------------------------
msimms's Profile: www.excelforum.com/member.php...oamp;userid=30382
View this thread: www.excelforum.com/showthread...hreadid=500441Try
=IF(COUNTIF(A2:A26,quot;lt;=quot;amp;H1)gt;0,1,0)
or
=IF(COUNTIF(A2:A26,quot;lt;=quot;amp;H1),1,0)
Regards,
Alan.
quot;msimmsquot; gt; wrote in
message ...
gt;
gt; This is driving me crazy....I am trying to debug and understand some
gt; cell computations using Excel 2003...but all it does it return an
gt; #VALUE error:
gt; =IF($A$2:$A$26lt;=H$1,1,0)
gt;
gt; But the following WORKS and produces the correct result:
gt; = 100 * SUM(IF(($A$2:$A$26lt;=G$1) *( $B$2:$B$26 lt;=
gt; $F2),$C$2:$C$26,0))/$C$27
gt;
gt; WHY ? key issue he how to properly compare a vector to a single
gt; cell.
gt;
gt; My take on this: $A$2:$A$26lt;=G$1
gt; Each cell in Column A from 2-gt;26 is being compared to G1.
gt; If ANY ONE OF THEM is less than G1, then the result is quot;Truequot;....
gt; correct ?
gt;
gt; Is there a better, MORE APPROPRIATE way to do this ?
gt;
gt;
gt; --
gt; msimms
gt; ------------------------------------------------------------------------
gt; msimms's Profile:
gt; www.excelforum.com/member.php...oamp;userid=30382
gt; View this thread: www.excelforum.com/showthread...hreadid=500441
gt;
msimms wrote...
gt;This is driving me crazy....I am trying to debug and understand some
gt;cell computations using Excel 2003...but all it does it return an
gt;#VALUE error:
gt;=IF($A$2:$A$26lt;=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.

gt;But the following WORKS and produces the correct result:
gt;=100*SUM(IF(($A$2:$A$26lt;=G$1)*($B$2:$B$26lt;=$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.

gt;WHY ? key issue he how to properly compare a vector to a single
gt;cell.
gt;
gt;My take on this: $A$2:$A$26lt;=G$1
gt;Each cell in Column A from 2-gt;26 is being compared to G1.
gt;If ANY ONE OF THEM is less than G1, then the result is quot;Truequot;....
gt;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)gt;0.
gt; Wrong. It compares *EACH* cell in A2:A26 to G1 and returns an
gt; *ARRAY*
gt; of TRUE/FALSE results. If you want to check whether ANY cell in A2:A26
gt; equals G1 and return a SINGLE TRUE/FALSE result, use
gt; COUNTIF($A$2:$A$26,G$1)gt;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 quot;=True()quot; 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 ?--
msimms
------------------------------------------------------------------------
msimms's Profile: www.excelforum.com/member.php...oamp;userid=30382
View this thread: www.excelforum.com/showthread...hreadid=500441msimms wrote...
....
gt;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.