| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
AND/OR in Array Formula
They seemingly do not work as anticipated. I'm probably misinterpreting
something. =SUM(($B$3:$B$1000=M4)*($C$3:$C$1000=$AH$2)*(OR($F $3:$F$1000<0,$J$3:$J$1000<0))*($H$3:$H$1000=1)) The OR section is what I am having trouble with. This formual returns success when clearly this is not true. For example, in columns F and J, the values are 0 but the above formula is counting them. I do not want to use IFs. I can get it to work using IF. Still, I would like to use ORs and ANDs. :) To make it easier, strip out the |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
AND/OR in Array Formula
I meant to add:
To make it easier, strip out the non OR'ed chunks. Why doesn't OR and AND work here? "Rif" wrote: They seemingly do not work as anticipated. I'm probably misinterpreting something. =SUM(($B$3:$B$1000=M4)*($C$3:$C$1000=$AH$2)*(OR($F $3:$F$1000<0,$J$3:$J$1000<0))*($H$3:$H$1000=1)) The OR section is what I am having trouble with. This formual returns success when clearly this is not true. For example, in columns F and J, the values are 0 but the above formula is counting them. I do not want to use IFs. I can get it to work using IF. Still, I would like to use ORs and ANDs. :) To make it easier, strip out the |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
AND/OR in Array Formula
Yeah....that's one of the quirks of the OR function....it returns the first
values from the list and ignores the others. Try something like this: =SUMPRODUCT(($B$3:$B$1000=M4)*($C$3:$C$1000=$AH$2) *(($F$3:$F$1000+$J$3:$J$1000)<0)*($H$3:$H$1000=1) ) Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Rif" wrote: They seemingly do not work as anticipated. I'm probably misinterpreting something. =SUM(($B$3:$B$1000=M4)*($C$3:$C$1000=$AH$2)*(OR($F $3:$F$1000<0,$J$3:$J$1000<0))*($H$3:$H$1000=1)) The OR section is what I am having trouble with. This formual returns success when clearly this is not true. For example, in columns F and J, the values are 0 but the above formula is counting them. I do not want to use IFs. I can get it to work using IF. Still, I would like to use ORs and ANDs. :) To make it easier, strip out the |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
AND/OR in Array Formula
Hmm.. if they were all numerical, that trick would work. They aren't. I
wish I could combine ISTEXT in there... Thank you for the reply! "Ron Coderre" wrote: Yeah....that's one of the quirks of the OR function....it returns the first values from the list and ignores the others. Try something like this: =SUMPRODUCT(($B$3:$B$1000=M4)*($C$3:$C$1000=$AH$2) *(($F$3:$F$1000+$J$3:$J$1000)<0)*($H$3:$H$1000=1) ) Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Rif" wrote: They seemingly do not work as anticipated. I'm probably misinterpreting something. =SUM(($B$3:$B$1000=M4)*($C$3:$C$1000=$AH$2)*(OR($F $3:$F$1000<0,$J$3:$J$1000<0))*($H$3:$H$1000=1)) The OR section is what I am having trouble with. This formual returns success when clearly this is not true. For example, in columns F and J, the values are 0 but the above formula is counting them. I do not want to use IFs. I can get it to work using IF. Still, I would like to use ORs and ANDs. :) To make it easier, strip out the |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
AND/OR in Array Formula
OK....You really need to post ALL of the rules and some sample data....That
way we don't have to guess things like: When you're testing if cells <0, you have text mixed in with numbers. You'll get the answer you need faster and with less iterations. *********** Regards, Ron XL2002, WinXP "Rif" wrote: Hmm.. if they were all numerical, that trick would work. They aren't. I wish I could combine ISTEXT in there... Thank you for the reply! "Ron Coderre" wrote: Yeah....that's one of the quirks of the OR function....it returns the first values from the list and ignores the others. Try something like this: =SUMPRODUCT(($B$3:$B$1000=M4)*($C$3:$C$1000=$AH$2) *(($F$3:$F$1000+$J$3:$J$1000)<0)*($H$3:$H$1000=1) ) Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Rif" wrote: They seemingly do not work as anticipated. I'm probably misinterpreting something. =SUM(($B$3:$B$1000=M4)*($C$3:$C$1000=$AH$2)*(OR($F $3:$F$1000<0,$J$3:$J$1000<0))*($H$3:$H$1000=1)) The OR section is what I am having trouble with. This formual returns success when clearly this is not true. For example, in columns F and J, the values are 0 but the above formula is counting them. I do not want to use IFs. I can get it to work using IF. Still, I would like to use ORs and ANDs. :) To make it easier, strip out the |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
AND/OR in Array Formula
I don't think OR and AND work well in array formulas. You could try:
=SUM(($B$3:$B$1000=M4)*($C$3:$C$1000=$AH$2)*((($F$ 3:$F$1000<0)+($J$3:$J$1000<0))0)*($H$3:$H$1000= 1)) "Rif" wrote: They seemingly do not work as anticipated. I'm probably misinterpreting something. =SUM(($B$3:$B$1000=M4)*($C$3:$C$1000=$AH$2)*(OR($F $3:$F$1000<0,$J$3:$J$1000<0))*($H$3:$H$1000=1)) The OR section is what I am having trouble with. This formual returns success when clearly this is not true. For example, in columns F and J, the values are 0 but the above formula is counting them. I do not want to use IFs. I can get it to work using IF. Still, I would like to use ORs and ANDs. :) To make it easier, strip out the |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
AND/OR in Array Formula
You're right. I was just after the OR / AND problem. The fact that you and
JMB showed ways around the problem (the identity and other attribute solutions): look at both columns combined. Rated all. Thanks guys. :) "Ron Coderre" wrote: OK....You really need to post ALL of the rules and some sample data....That way we don't have to guess things like: When you're testing if cells <0, you have text mixed in with numbers. You'll get the answer you need faster and with less iterations. *********** Regards, Ron XL2002, WinXP "Rif" wrote: Hmm.. if they were all numerical, that trick would work. They aren't. I wish I could combine ISTEXT in there... Thank you for the reply! "Ron Coderre" wrote: Yeah....that's one of the quirks of the OR function....it returns the first values from the list and ignores the others. Try something like this: =SUMPRODUCT(($B$3:$B$1000=M4)*($C$3:$C$1000=$AH$2) *(($F$3:$F$1000+$J$3:$J$1000)<0)*($H$3:$H$1000=1) ) Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Rif" wrote: They seemingly do not work as anticipated. I'm probably misinterpreting something. =SUM(($B$3:$B$1000=M4)*($C$3:$C$1000=$AH$2)*(OR($F $3:$F$1000<0,$J$3:$J$1000<0))*($H$3:$H$1000=1)) The OR section is what I am having trouble with. This formual returns success when clearly this is not true. For example, in columns F and J, the values are 0 but the above formula is counting them. I do not want to use IFs. I can get it to work using IF. Still, I would like to use ORs and ANDs. :) To make it easier, strip out the |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| meaning of : IF(Switch; Average(array A, array B); array A) | Excel Worksheet Functions | |||
| Array Formula Help | Excel Worksheet Functions | |||
| Array Formula | Excel Worksheet Functions | |||
| Array Formula help pls. | Excel Worksheet Functions | |||
| Array Formula - using LEFT("text",4) in formula | Excel Worksheet Functions | |||
Linear Mode
