Multiple Conditonal Comparison

[Alan]

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

 

[John C]

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"),(rngQty)),"A
is less than B and/or C","A is greater than or equal to B and C combined")