Multiple Criteria Conditional Formatting with Threshold

[calvinc123]

My objective is to establish a conditional format across the table based on a certain threshold establish for multiple categories. Each category will have a different threshold, which adds another layer of complexity to the issue. Below is an example breaking it down. Feel free to ask what ever questions you have if it's not clear! Thanks for your help!!

Example: A, B, C are all values that will be static inputs. Column D will be checked versus the type of category and the threshold established in column A. Column E will be the conditional formatting for how much Column C is out of balance based on the type of category it is from column A.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]A1[/TD]
[TD]B1[/TD]
[TD]C1[/TD]
[TD]D1[/TD]
[TD]E1[/TD]
[/TR]
[TR]
[TD]Category[/TD]
[TD]Product[/TD]
[TD]Current (%)[/TD]
[TD]Check[/TD]
[TD]Imbalance[/TD]
[/TR]
[TR]
[TD]Basket 1[/TD]
[TD]Apple[/TD]
[TD]10%[/TD]
[TD]OK[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]Basket 2[/TD]
[TD]Apple[/TD]
[TD]12%[/TD]
[TD]Out of Balance[/TD]
[TD]-1%[/TD]
[/TR]
[TR]
[TD]Basket 3[/TD]
[TD]Apple[/TD]
[TD]24%[/TD]
[TD]Out of Balance[/TD]
[TD]2%[/TD]
[/TR]
[TR]
[TD]Basket 1[/TD]
[TD]Apple[/TD]
[TD]13%[/TD]
[TD]Out of Balance[/TD]
[TD]1%[/TD]
[/TR]
[TR]
[TD]Basket 3[/TD]
[TD]Apple[/TD]
[TD]22%[/TD]
[TD]OK[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]Basket 2[/TD]
[TD]Apple[/TD]
[TD]5%[/TD]
[TD]Out of Balance[/TD]
[TD]-7%[/TD]
[/TR]
[TR]
[TD]Basket 3[/TD]
[TD]Apple[/TD]
[TD]8%[/TD]
[TD]Out of Balance[/TD]
[TD]-10%[/TD]
[/TR]
</tbody>[/TABLE]


Here is the set of "Rules" for the Categories and the Threshold Level.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Category[/TD]
[TD]Product[/TD]
[TD]Rule (%)[/TD]
[/TR]
[TR]
[TD]Basket 1[/TD]
[TD]Apple[/TD]
[TD]10%[/TD]
[/TR]
[TR]
[TD]Basket 2[/TD]
[TD]Apple[/TD]
[TD]15%[/TD]
[/TR]
[TR]
[TD]Basket 3[/TD]
[TD]Apple[/TD]
[TD]20%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Threshold (+/-)[/TD]
[TD]2%[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Hope this is clear! Best.

 

[etaf]

you could use a lookup
=LOOKUP(2,1/(Sheet2!$A$1:$A$10=A2)/(Sheet2!$B$1:$B$10=B2),Sheet2!$C$1:$C$10)
this is assuming the reference is on Sheet 2

that returns the reference value
then we can use an AND() to compare the tolerance

is
C2 > = the lookup - the tolerance
C2 < = the lookup + the tolerance

using an AND()
then an IF for the check

then a little more complicated for the amount out

I'll work on a sample

for the OK or out of balance
=IF(AND(C2 > = LOOKUP(2,1/(Sheet2!$A$1:$A$10=A2)/(Sheet2!$B$1:$B$10=B2)-Sheet2!$B$6,Sheet2!$C$1:$C$10),C2 < = LOOKUP(2,1/(Sheet2!$A$1:$A$10=A2)/(Sheet2!$B$1:$B$10=B2),Sheet2!$C$1:$C$10)+Sheet2!$B$6),"OK","Out of Balance")

 

[etaf]

i can nolonger edit

so for the value
=IF(AND(C2 > = LOOKUP(2,1/(Sheet2!$A$1:$A$10=A2)/(Sheet2!$B$1:$B$10=B2)-Sheet2!$B$6,Sheet2!$C$1:$C$10),C2 < = LOOKUP(2,1/(Sheet2!$A$1:$A$10=A2)/(Sheet2!$B$1:$B$10=B2),Sheet2!$C$1:$C$10)+Sheet2!$B$6),"-",IF(C2 < = LOOKUP(2,1/(Sheet2!$A$1:$A$10=A2)/(Sheet2!$B$1:$B$10=B2),Sheet2!$C$1:$C$10)-Sheet2!$B$6, C2-(LOOKUP(2,1/(Sheet2!$A$1:$A$10=A2)/(Sheet2!$B$1:$B$10=B2),Sheet2!$C$1:$C$10)-Sheet2!$B$6),C2-(LOOKUP(2,1/(Sheet2!$A$1:$A$10=A2)/(Sheet2!$B$1:$B$10=B2),Sheet2!$C$1:$C$10)+Sheet2!$B$6)))

I make the 7% out of balance 8%

https://www.dropbox.com/s/c51qr9s3x0z56gg/tolerance.xlsx?dl=0

 

[calvinc123]

Thanks so much! I will check this out and see if it will accomplish what I need it to. I'll let you know if I have any questions.