Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
OR in SUMPRODUCT formula
I recently read Bob Phillips famous paper on SUMPRUCT in
<http://www.xldynamic.com/source/xld.sumproduct.html It has a lot to say about AND operations using multiplication. It has a little to say about OR operations using addition. I need to use both. I have some difficulty in turning FALSE and TRUE into numbers 0 and 1 - Yes I know TRUE is usually -1. I have 'slices' of 3 rows: Row R S T U V W X Y Z AA AB AC Column 19 0 26 18 0 18 0 33 0 0 0 0 0 34 0 0 33 24 0 0 0 0 0 0 0 0 35 0 15 34 25 0 0 0 0 0 0 0 0 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 )) which produces the result 2 as intended. Can the formula be simplified? 34 and 35 OR'd together should be R S T U V W X Y Z AA AB AC 34 OR 35 0 1 1 1 0 0 0 0 0 0 0 0 19 is ANDed 19 0 1 1 0 1 0 1 0 0 0 0 0 giving the result 34 OR 35 & 19 0 1 1 0 0 0 0 0 0 0 0 0 That 'row' contains one twice and I get the result 2 as intended. I previously misanalysed my requirement. I had multiplied the 3 'rows' to produce A and B and C in =SUMPRODUCT((($R19:$AC19<0)*(($R$34:$AC$34)<0)*( $R$35:$AC$35)<0))) That produces the answer 1 where 2 is correct. ;) P.S. I add another component to tweak that formula: +IF(ISERROR(SEARCH("CIRCLE",$AD19)),0,N(SEARCH("CI RCLE",$AD19)<0)) $AD19 can case-insensitively hold "Circle" or not - not includes $AD19 = "" which causes SEARCH to give an error. That formula excerpt is about twice as complicated as I would like it to be. Any simplification suggestions? I will explain my data. Rows represent London Underground stations. Columns represent lines. My A and (B or C) calculates the number of lines at A common to B or C. My tweak is used to force an addition to the sumproduct which takes the values: 0 The stations are not connected 1 One line connects the stations 2 etc. more than 1 line connects or should be considered to connect the stations. -- Walter Briscoe |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
OR in SUMPRODUCT formula
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. 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)) Some things to note: 1. When using "+" for OR in this context, generally you should test the sum for "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. 2. You do not need double-negative (--) if you are using other arithmetic operators. 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). |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
OR in SUMPRODUCT formula
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
OR in SUMPRODUCT formula
On Jun 13, 8:22*am, joeu2004 wrote:
On Jun 13, 7:06*am, Walter Briscoe 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 got lost in your use of parentheses and the fact that a right parenthesis is missing. Rereading, I think your SUMPRODUCT is equivalent to mine as long as $R $34:$AC$34 and $R$35:$AC$35 are all non-negative. In that case, you could write: =SUMPRODUCT(($R19:$AC19<0)*($R$34:$AC$34 + $R$35:$AC$35 < 0)) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
OR in SUMPRODUCT formula
On Jun 13, 9:11*am, Walter Briscoe
wrote: =SUMPRODUCT(($R19:$AC19<0)*(($R$34:$AC$34<0)+($ R$35:$AC$35<0)0)) [....] 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. This isn't about preference; it's about correctness. I guess my explanation in the subsequent paragraph was not clear. If both $R$34:$AC$34<0 and $R$35:$AC$35<0 are true, then ($R$34:$AC $34<0)+($R$35:$AC$35<0) is 2, not 1. If $R19:$AC19<0 is also true, then ($R19:$AC19<0)*(($R$34:$AC$34<0)+ ($R$35:$AC$35<0)) is 2, not 1. Thus, SUMPRODUCT would double-account (2 instead of 1) for the one condition. I assumed that you want to count only once when "x AND (y OR z)" is true. However, if you want to count "x AND y" and "x AND z" separately, then the "0" should indeed be omitted. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nesting a sumproduct formula within a sumif formula. | Excel Discussion (Misc queries) | |||
sumproduct formula help | Excel Discussion (Misc queries) | |||
SUMPRODUCT Formula Help | Excel Worksheet Functions | |||
Help - Looking for a Sumproduct formula | Excel Worksheet Functions | |||
Sumproduct Formula | Excel Worksheet Functions |