In message
s.com of Mon, 13 Jun 2011 08:22:33 in microsoft.public.excel.worksheet.
functions, joeu2004 writes
On Jun 13, 7:06*am, Walter Briscoe
wrote:
Yes I know TRUE is usually -1.

No. Excel TRUE is 1. VBA True is -1.

So much for my plans to divert side issues. ;)
You're right and I was wrong after too much VBA. ;(



Walter wrote:
I want to OR rows 34 and 35 and AND the result with row 19.
Rows 34 and 35 are fixed. 19 is copied through.
I currently have
=SUMPRODUCT(--(($R19:$AC19<0)*(($R$34:$AC$34+($R$35:$AC$35))<0 ))

I think you want:

=SUMPRODUCT(($R19:$AC19<0)*(($R$34:$AC$34<0)+($ R$35:$AC$35<0)0))


I agree that hits the spot and is simpler than my work.

Some things to note:

1. When using "+" for OR in this context, generally you should test
the sum for "0".

Why? I prefer 0 as a synonym for FALSE and think of TRUE as < 0.


You can get away without "0" only when the summed (ORed) conditions
are mutually exclusive. For example, (A1:A10="a") + (A1:A10="b").
Both conditions cannot be true simultaneously. But you need
((A1:A10="a")+(B1:B10="b")0) because both conditions can be true,
resulting in a sum of 2, whereas we usually want 1 or 0.

I had that problem. I first was trying things like
OR(A1:A10="a",B1:B10="b"). I was surprised this produced a single
FALSE/TRUE result (i.e.. a scalar) rather than an array of FALSE/TRUE
values.



2. You do not need double-negative (--) if you are using other
arithmetic operators.

I like that rule of thumb.


The purpose of the double-negative is to convert TRUE and FALSE into 1
and 0, which SUMPRODUCT requires. But any arithmetic operation will
have the same result. For example, TRUE+TRUE is 2. No need to write
(--TRUE)+(--TRUE).

I understood that to some extent after reading
<http://www.mcgimpsey.com/excel/formulae/doubleneg.html

Many thanks for the help. I should find my next sumproduct operation
easier as a result of that help.
--
Walter Briscoe