If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|