Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Conditonal Comparison
Hi,
I am wondering what the best way to do a multiple conditional comparison is? For example I have 3 columns of data where I need the first column compared, then if TRUE, compare data in the second column which if also TRUE, then flag the result. P/N Class Qty 12345 A 3 12345 B 5 12345 C 2 54321 A 4 54321 C 1 So in the above data example, I need to essentially compare the class codes within the same part number and if the quantity of class "A" is less than "B" OR "C" (in situations where only one these class codes exist) then place a flag in "A's" row. However in situations where both class "B" AND "C" exist for the same part number, I need to flag "A" if it's quantity is less than the COMBINED quantity of "B" AND "C". Any help would be appreciated. I'm getting a headache trying to figure out the forumla. Thanks. Ella |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Conditonal Comparison
Assuming your data starts in cell B2, and you want the 'flag' in column D2,
you could use the following formula (expand range as needed). =IF($B2<"A","",IF(SUMPRODUCT(--($A$2:$A$6=$A2),--($B$2:$B$6=$B2),($C$2:$C$6))<SUMPRODUCT(--($A$2:$A$6=$A2),($B$2:$B$6="B")+($B$2:$B$6="C"),($ C$2:$C$6)),"A is less than B and/or C","A is greater than or equal to B and C combined")) If, however, you want to have a cell where you type in a part number, and then it tells you if qty(A) is or is not less than sum(qty(b&c)), you can use the following formula (adjust range/rangenames to suit your needs). =IF(SUMPRODUCT(--(rngPart=pnCheck),--(rngClass="A"),(rngQty))<SUMPRODUCT(--(rngPart=pnCheck),(rngClass="B")+(rngClass="C"),(r ngQty)),"A is less than B and/or C","A is greater than or equal to B and C combined") -- John C "Alan" wrote: Hi, I am wondering what the best way to do a multiple conditional comparison is? For example I have 3 columns of data where I need the first column compared, then if TRUE, compare data in the second column which if also TRUE, then flag the result. P/N Class Qty 12345 A 3 12345 B 5 12345 C 2 54321 A 4 54321 C 1 So in the above data example, I need to essentially compare the class codes within the same part number and if the quantity of class "A" is less than "B" OR "C" (in situations where only one these class codes exist) then place a flag in "A's" row. However in situations where both class "B" AND "C" exist for the same part number, I need to flag "A" if it's quantity is less than the COMBINED quantity of "B" AND "C". Any help would be appreciated. I'm getting a headache trying to figure out the forumla. Thanks. Ella |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
multiple column comparison on 2 worksheets | Excel Worksheet Functions | |||
Multiple Conditonal Formats | Excel Discussion (Misc queries) | |||
multiple comparison between means | New Users to Excel | |||
multiple comparison between means | New Users to Excel | |||
multiple comparison between means | New Users to Excel |