Two conditions most be met for each or

[johnnyhammer]

I HAVE 4 OUTCOME COMBINATIONS. I couldn't figure it out. it only changed if all three B:E exceeded 84, but I need to change if any of the cell exceeds 84, along with A1

IF A1 IS LESS THAN 1, AND any if these cells exceed B2>84 OR C2>84 OR D2>84 OR E2>84 ="A"

IF A1 IS LESS THAN 1, AND any if these cells is less than B2<84 OR C2<84 OR D2<84 OR E2<84 ="C"

IF A1 IS GREAT THAN 1, AND any if these cells exceed B2>84 OR C2>84 OR D2>84 OR E2>84 ="D"

IF A1 IS GREAT THAN 1,AND any if these cells is less than B2<84 OR C2<84 OR D2<84 OR E2<84="Z"



=IF(AND(G17>1,C16<85),"A",IF(AND(G17>1,E16<85),"A",IF(AND(G17=1,C16<85),"C",IF(AND(RFID'!G17=1,!E16<85),"C",IF(AND(G17>1,C16>84),"D",IF(AND(G17>1,!E16>84),"D",IF(AND(G17=1,C16>84),"Z",IF(AND(G17=1,E16>84),"Z"))))))))

 

[Eric W]

Try:

=IF(COUNTIF(B2:C2,">84"),IF(A1<1,"A","D"),IF(COUNTIF(B2:E2,"<84"),IF(A1<1,"C","Z")))

 

[mikerickson]

The situation where A1=.5, B2=85 and C2=83 meets both the first and second conditions. Do you want "A" or "C"?

 

[johnnyhammer]

thank you for the help but I'm still only getting 2 accurate results

this the Text I'm using to try and create the formula. length x width x height
C1 E1 G1

[TABLE="width: 598"]
<colgroup><col><col span="7"><col></colgroup><tbody>[TR]
[TD]A[/TD]
[TD="colspan: 8"]Shipment is not a consolidation and does not exceed 84 inches in any Dimension[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="colspan: 8"]Shipment is a consolidation, but does not exceed 84 inches in any dimension[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD="colspan: 8"]Shipment is a consolidation and exceeds 84 inches in one or more dimension[/TD]
[/TR]
[TR]
[TD]Z[/TD]
[TD="colspan: 8"]Shipment is not a consolidation, and exceeds 84 inches in one or more dimension[/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

So A1 is the consolidation indicator? Something like 0=not a consolidation, 1=it is a consolidation? And B2 to E2 are the dimensions? If you have 3 dimensions, shouldn't it just be B2:D2?

 

[johnnyhammer]

Yes A1 is the consolidation indicator.
consolidated =1
not consolidated >1

Yes, it is 3 dimensions,
(I tried adapting the formula to 3, but I came out 100 ways how not to do it)

 

[johnnyhammer]

Thank you for your help, it was the "=" in the consolidation factor

=IF(COUNTIF(C16:E16:G16,">84"),IF(G17=1,"D","Z"),IF(COUNTIF(C16:E16:G16,"<=84"),IF(G17=1,"C","A")))

thank you so much

 

[Eric W]

COUNTIF only works if the range is contiguous, that is, if all the values are right next to each other. If they are separated, you'd need to use something like an OR. But based on your latest descriptions, we can shorten the formula a bit more. Try:

=IF(OR(C16>84,E16>84,G16>84),IF(G17=1,"D","Z"),IF(G17=1,"C","A"))

The OR condition checks to see if the item exceeds 84 in any dimension. If not, it checks G17 for the consolidation flag and returns D or Z. If the item does exceed 84 in any dimension, it checks the consolidation flag and returns C or A. As far as I can tell, this matches your requirements.