Excel Multiple Company KPI lookup

Biz

Well-known Member
Joined
May 18, 2009
Messages
1,677
Dear All,

I have constructed the formula for Company A and it works but it fails to work for other companies.
I used formulas in Col K-L and Should Be col P-R.

Your help would be greatly appreciated.

Values
Excel 2013 32 bit
A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
R
1
Conditional Formatting Criteria
Criteria
CompanyKPI %RedAmberGreenShould BeRedAmberGreen
2
CompanyKPILow BoundUpper BoundColour DescriptionA
26%​
TRUE​
FALSE​
FALSE​
TRUE​
FALSE​
FALSE​
3
A​
KPA %
75%​
100%​
RedB
91%​
FALSE​
FALSE​
TRUE​
FALSE​
FALSE​
TRUE​
4
A​
80%​
85%​
AmberC
9%​
TRUE​
FALSE​
FALSE​
TRUE​
FALSE​
FALSE​
5
A​
85%​
100%​
GreenD
18%​
TRUE​
FALSE​
FALSE​
TRUE​
FALSE​
FALSE​
6
B​
KPA %
55%​
100%​
RedA
80%​
FALSE​
TRUE​
FALSE​
FALSE​
TRUE​
FALSE​
7
B​
60%​
65%​
AmberB
70%​
TRUE​
FALSE​
FALSE​
FALSE​
FALSE​
TRUE​
8
B​
65%​
100%​
GreenC
110%​
TRUE​
FALSE​
FALSE​
TRUE​
FALSE​
FALSE​
9
C​
KPA %
40%​
100%​
RedD
60%​
TRUE​
FALSE​
FALSE​
FALSE​
FALSE​
TRUE​
10
C​
45%​
50%​
AmberA
71%​
TRUE​
FALSE​
FALSE​
TRUE​
FALSE​
FALSE​
11
C​
50%​
100%​
GreenB
9%​
TRUE​
FALSE​
FALSE​
TRUE​
FALSE​
FALSE​
12
D​
KPA %
45%​
100%​
RedC
99%​
FALSE​
FALSE​
TRUE​
FALSE​
FALSE​
TRUE​
13
D​
50%​
55%​
AmberD
38%​
TRUE​
FALSE​
FALSE​
TRUE​
FALSE​
FALSE​
14
D​
55%​
100%​
GreenA
16%​
TRUE​
FALSE​
FALSE​
TRUE​
FALSE​
FALSE​
15
E​
KPA %
50%​
100%​
RedB
23%​
TRUE​
FALSE​
FALSE​
TRUE​
FALSE​
FALSE​
16
E​
55%​
60%​
AmberC
66%​
TRUE​
FALSE​
FALSE​
FALSE​
FALSE​
TRUE​
17
E​
60%​
100%​
GreenD
59%​
TRUE​
FALSE​
FALSE​
FALSE​
FALSE​
TRUE​
18
F​
KPA %
45%​
100%​
RedA
92%​
FALSE​
FALSE​
TRUE​
FALSE​
FALSE​
TRUE​
19
F​
50%​
55%​
AmberB
100%​
FALSE​
FALSE​
TRUE​
FALSE​
FALSE​
TRUE​
20
F​
55%​
100%​
GreenC
61%​
TRUE​
FALSE​
FALSE​
FALSE​
FALSE​
TRUE​
21
D
95%​
FALSE​
FALSE​
TRUE​
FALSE​
FALSE​
TRUE​
22
E
61%​
TRUE​
FALSE​
FALSE​
FALSE​
FALSE​
TRUE​
23
F
95%​
FALSE​
FALSE​
TRUE​
FALSE​
FALSE​
TRUE​
24
E
56%​
TRUE​
FALSE​
FALSE​
FALSE​
TRUE​
FALSE​
25
F
53%​
TRUE​
FALSE​
FALSE​
FALSE​
TRUE​
FALSE​

<tbody>
</tbody>
Sheet: Sheet1

<tbody>
</tbody>

Formulas Used
Excel 2013 32 bit
A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
R
1
Conditional Formatting Criteria
Criteria
CompanyKPI %RedAmberGreenShould BeRedAmberGreen
2
CompanyKPILow BoundUpper BoundColour DescriptionA
26%​
=OR(AND(J2<>0,OR(J2<=$C$3,J2<$C$4)),J2>$D$3)​
=AND(J2>=$C$4,J2<=$D$4)​
=AND(J2>=$C$5,J2<=$D$5)​
TRUE​
FALSE​
FALSE​
3
A​
KPA %
=C4-5%​
100%​
RedB
91%​
=OR(AND(J3<>0,OR(J3<=$C$3,J3<$C$4)),J3>$D$3)​
=AND(J3>=$C$4,J3<=$D$4)​
=AND(J3>=$C$5,J3<=$D$5)​
FALSE​
FALSE​
TRUE​
4
A​
=C5-5%​
=C5-0.01%​
AmberC
9%​
=OR(AND(J4<>0,OR(J4<=$C$3,J4<$C$4)),J4>$D$3)​
=AND(J4>=$C$4,J4<=$D$4)​
=AND(J4>=$C$5,J4<=$D$5)​
TRUE​
FALSE​
FALSE​
5
A​
85%​
=D3​
GreenD
18%​
=OR(AND(J5<>0,OR(J5<=$C$3,J5<$C$4)),J5>$D$3)​
=AND(J5>=$C$4,J5<=$D$4)​
=AND(J5>=$C$5,J5<=$D$5)​
TRUE​
FALSE​
FALSE​
6
B​
KPA %
=C7-5%​
100%​
RedA
80%​
=OR(AND(J6<>0,OR(J6<=$C$3,J6<$C$4)),J6>$D$3)​
=AND(J6>=$C$4,J6<=$D$4)​
=AND(J6>=$C$5,J6<=$D$5)​
FALSE​
TRUE​
FALSE​
7
B​
=C8-5%​
=C8-0.01%​
AmberB
70%​
=OR(AND(J7<>0,OR(J7<=$C$3,J7<$C$4)),J7>$D$3)​
=AND(J7>=$C$4,J7<=$D$4)​
=AND(J7>=$C$5,J7<=$D$5)​
FALSE​
FALSE​
TRUE​
8
B​
65%​
=D6​
GreenC
110%​
=OR(AND(J8<>0,OR(J8<=$C$3,J8<$C$4)),J8>$D$3)​
=AND(J8>=$C$4,J8<=$D$4)​
=AND(J8>=$C$5,J8<=$D$5)​
TRUE​
FALSE​
FALSE​
9
C​
KPA %
=C10-5%​
100%​
RedD
60%​
=OR(AND(J9<>0,OR(J9<=$C$3,J9<$C$4)),J9>$D$3)​
=AND(J9>=$C$4,J9<=$D$4)​
=AND(J9>=$C$5,J9<=$D$5)​
FALSE​
FALSE​
TRUE​
10
C​
=C11-5%​
=C11-0.01%​
AmberA
71%​
=OR(AND(J10<>0,OR(J10<=$C$3,J10<$C$4)),J10>$D$3)​
=AND(J10>=$C$4,J10<=$D$4)​
=AND(J10>=$C$5,J10<=$D$5)​
TRUE​
FALSE​
FALSE​
11
C​
50%​
=D9​
GreenB
9%​
=OR(AND(J11<>0,OR(J11<=$C$3,J11<$C$4)),J11>$D$3)​
=AND(J11>=$C$4,J11<=$D$4)​
=AND(J11>=$C$5,J11<=$D$5)​
TRUE​
FALSE​
FALSE​
12
D​
KPA %
=C13-5%​
100%​
RedC
99%​
=OR(AND(J12<>0,OR(J12<=$C$3,J12<$C$4)),J12>$D$3)​
=AND(J12>=$C$4,J12<=$D$4)​
=AND(J12>=$C$5,J12<=$D$5)​
FALSE​
FALSE​
TRUE​
13
D​
=C14-5%​
=C14-0.01%​
AmberD
38%​
=OR(AND(J13<>0,OR(J13<=$C$3,J13<$C$4)),J13>$D$3)​
=AND(J13>=$C$4,J13<=$D$4)​
=AND(J13>=$C$5,J13<=$D$5)​
TRUE​
FALSE​
FALSE​
14
D​
55%​
=D12​
GreenA
16%​
=OR(AND(J14<>0,OR(J14<=$C$3,J14<$C$4)),J14>$D$3)​
=AND(J14>=$C$4,J14<=$D$4)​
=AND(J14>=$C$5,J14<=$D$5)​
TRUE​
FALSE​
FALSE​
15
E​
KPA %
=C16-5%​
100%​
RedB
23%​
=OR(AND(J15<>0,OR(J15<=$C$3,J15<$C$4)),J15>$D$3)​
=AND(J15>=$C$4,J15<=$D$4)​
=AND(J15>=$C$5,J15<=$D$5)​
TRUE​
FALSE​
FALSE​
16
E​
=C17-5%​
=C17-0.01%​
AmberC
66%​
=OR(AND(J16<>0,OR(J16<=$C$3,J16<$C$4)),J16>$D$3)​
=AND(J16>=$C$4,J16<=$D$4)​
=AND(J16>=$C$5,J16<=$D$5)​
FALSE​
FALSE​
TRUE​
17
E​
60%​
=D15​
GreenD
59%​
=OR(AND(J17<>0,OR(J17<=$C$3,J17<$C$4)),J17>$D$3)​
=AND(J17>=$C$4,J17<=$D$4)​
=AND(J17>=$C$5,J17<=$D$5)​
FALSE​
FALSE​
TRUE​
18
F​
KPA %
=C19-5%​
100%​
RedA
92%​
=OR(AND(J18<>0,OR(J18<=$C$3,J18<$C$4)),J18>$D$3)​
=AND(J18>=$C$4,J18<=$D$4)​
=AND(J18>=$C$5,J18<=$D$5)​
FALSE​
FALSE​
TRUE​
19
F​
=C20-5%​
=C20-0.01%​
AmberB
100%​
=OR(AND(J19<>0,OR(J19<=$C$3,J19<$C$4)),J19>$D$3)​
=AND(J19>=$C$4,J19<=$D$4)​
=AND(J19>=$C$5,J19<=$D$5)​
FALSE​
FALSE​
TRUE​
20
F​
55%​
=D18​
GreenC
61%​
=OR(AND(J20<>0,OR(J20<=$C$3,J20<$C$4)),J20>$D$3)​
=AND(J20>=$C$4,J20<=$D$4)​
=AND(J20>=$C$5,J20<=$D$5)​
FALSE​
FALSE​
TRUE​
21
D
95%​
=OR(AND(J21<>0,OR(J21<=$C$3,J21<$C$4)),J21>$D$3)​
=AND(J21>=$C$4,J21<=$D$4)​
=AND(J21>=$C$5,J21<=$D$5)​
FALSE​
FALSE​
TRUE​
22
E
61%​
=OR(AND(J22<>0,OR(J22<=$C$3,J22<$C$4)),J22>$D$3)​
=AND(J22>=$C$4,J22<=$D$4)​
=AND(J22>=$C$5,J22<=$D$5)​
FALSE​
FALSE​
TRUE​
23
F
95%​
=OR(AND(J23<>0,OR(J23<=$C$3,J23<$C$4)),J23>$D$3)​
=AND(J23>=$C$4,J23<=$D$4)​
=AND(J23>=$C$5,J23<=$D$5)​
FALSE​
FALSE​
TRUE​
24
E
56%​
=OR(AND(J24<>0,OR(J24<=$C$3,J24<$C$4)),J24>$D$3)​
=AND(J24>=$C$4,J24<=$D$4)​
=AND(J24>=$C$5,J24<=$D$5)​
FALSE​
TRUE​
FALSE​
25
F
53%​
=OR(AND(J25<>0,OR(J25<=$C$3,J25<$C$4)),J25>$D$3)​
=AND(J25>=$C$4,J25<=$D$4)​
=AND(J25>=$C$5,J25<=$D$5)​
FALSE​
TRUE​
FALSE​

<tbody>
</tbody>
Sheet: Sheet1

<tbody>
</tbody>



Biz
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

LateralThinker

New Member
Joined
May 7, 2014
Messages
11
Hi

Try this:



Conditional Formatting Criteria Criteria     CompanyKPI %RedAmberGreenShould BeRedAmberGreen
CompanyKPILow BoundUpper BoundColour Description   A0.26=OR(AND(J2<>0,OR(J2<=OFFSET($C$3,MATCH(I2,$A$3:$A$20,FALSE)-1,0),J2<OFFSET($C$4,MATCH(I2,$A$3:$A$20,FALSE)-1,0))),J2>OFFSET($D$3,MATCH(I2,$A$3:$A$20,FALSE)-1,0))=AND(J2>=OFFSET($C$4,MATCH(I2,$A$3:$A$20,FALSE)-1,0),J2<=OFFSET($D$4,MATCH(I2,$A$3:$A$20,FALSE)-1,0))=AND(J2>=OFFSET($C$5,MATCH(I2,$A$3:$A$20,FALSE)-1,0),J2<=OFFSET($D$5,MATCH(I2,$A$3:$A$20,FALSE)-1,0))  TRUEFALSEFALSE
AKPA %=C4-5%1Red   B0.91=OR(AND(J3<>0,OR(J3<=OFFSET($C$3,MATCH(I3,$A$3:$A$20,FALSE)-1,0),J3<OFFSET($C$4,MATCH(I3,$A$3:$A$20,FALSE)-1,0))),J3>OFFSET($D$3,MATCH(I3,$A$3:$A$20,FALSE)-1,0))=AND(J3>=OFFSET($C$4,MATCH(I3,$A$3:$A$20,FALSE)-1,0),J3<=OFFSET($D$4,MATCH(I3,$A$3:$A$20,FALSE)-1,0))=AND(J3>=OFFSET($C$5,MATCH(I3,$A$3:$A$20,FALSE)-1,0),J3<=OFFSET($D$5,MATCH(I3,$A$3:$A$20,FALSE)-1,0))  FALSEFALSETRUE
A =C5-5%=C5-0.01%Amber   C0.09=OR(AND(J4<>0,OR(J4<=OFFSET($C$3,MATCH(I4,$A$3:$A$20,FALSE)-1,0),J4<OFFSET($C$4,MATCH(I4,$A$3:$A$20,FALSE)-1,0))),J4>OFFSET($D$3,MATCH(I4,$A$3:$A$20,FALSE)-1,0))=AND(J4>=OFFSET($C$4,MATCH(I4,$A$3:$A$20,FALSE)-1,0),J4<=OFFSET($D$4,MATCH(I4,$A$3:$A$20,FALSE)-1,0))=AND(J4>=OFFSET($C$5,MATCH(I4,$A$3:$A$20,FALSE)-1,0),J4<=OFFSET($D$5,MATCH(I4,$A$3:$A$20,FALSE)-1,0))  TRUEFALSEFALSE
A 0.85=D3Green   D0.18=OR(AND(J5<>0,OR(J5<=OFFSET($C$3,MATCH(I5,$A$3:$A$20,FALSE)-1,0),J5<OFFSET($C$4,MATCH(I5,$A$3:$A$20,FALSE)-1,0))),J5>OFFSET($D$3,MATCH(I5,$A$3:$A$20,FALSE)-1,0))=AND(J5>=OFFSET($C$4,MATCH(I5,$A$3:$A$20,FALSE)-1,0),J5<=OFFSET($D$4,MATCH(I5,$A$3:$A$20,FALSE)-1,0))=AND(J5>=OFFSET($C$5,MATCH(I5,$A$3:$A$20,FALSE)-1,0),J5<=OFFSET($D$5,MATCH(I5,$A$3:$A$20,FALSE)-1,0))  TRUEFALSEFALSE
BKPA %=C7-5%1Red   A0.8=OR(AND(J6<>0,OR(J6<=OFFSET($C$3,MATCH(I6,$A$3:$A$20,FALSE)-1,0),J6<OFFSET($C$4,MATCH(I6,$A$3:$A$20,FALSE)-1,0))),J6>OFFSET($D$3,MATCH(I6,$A$3:$A$20,FALSE)-1,0))=AND(J6>=OFFSET($C$4,MATCH(I6,$A$3:$A$20,FALSE)-1,0),J6<=OFFSET($D$4,MATCH(I6,$A$3:$A$20,FALSE)-1,0))=AND(J6>=OFFSET($C$5,MATCH(I6,$A$3:$A$20,FALSE)-1,0),J6<=OFFSET($D$5,MATCH(I6,$A$3:$A$20,FALSE)-1,0))  FALSETRUEFALSE
B =C8-5%=C8-0.01%Amber   B0.7=OR(AND(J7<>0,OR(J7<=OFFSET($C$3,MATCH(I7,$A$3:$A$20,FALSE)-1,0),J7<OFFSET($C$4,MATCH(I7,$A$3:$A$20,FALSE)-1,0))),J7>OFFSET($D$3,MATCH(I7,$A$3:$A$20,FALSE)-1,0))=AND(J7>=OFFSET($C$4,MATCH(I7,$A$3:$A$20,FALSE)-1,0),J7<=OFFSET($D$4,MATCH(I7,$A$3:$A$20,FALSE)-1,0))=AND(J7>=OFFSET($C$5,MATCH(I7,$A$3:$A$20,FALSE)-1,0),J7<=OFFSET($D$5,MATCH(I7,$A$3:$A$20,FALSE)-1,0))  FALSEFALSETRUE
B 0.65=D6Green   C1.1=OR(AND(J8<>0,OR(J8<=OFFSET($C$3,MATCH(I8,$A$3:$A$20,FALSE)-1,0),J8<OFFSET($C$4,MATCH(I8,$A$3:$A$20,FALSE)-1,0))),J8>OFFSET($D$3,MATCH(I8,$A$3:$A$20,FALSE)-1,0))=AND(J8>=OFFSET($C$4,MATCH(I8,$A$3:$A$20,FALSE)-1,0),J8<=OFFSET($D$4,MATCH(I8,$A$3:$A$20,FALSE)-1,0))=AND(J8>=OFFSET($C$5,MATCH(I8,$A$3:$A$20,FALSE)-1,0),J8<=OFFSET($D$5,MATCH(I8,$A$3:$A$20,FALSE)-1,0))  TRUEFALSEFALSE
CKPA %=C10-5%1Red   D0.6=OR(AND(J9<>0,OR(J9<=OFFSET($C$3,MATCH(I9,$A$3:$A$20,FALSE)-1,0),J9<OFFSET($C$4,MATCH(I9,$A$3:$A$20,FALSE)-1,0))),J9>OFFSET($D$3,MATCH(I9,$A$3:$A$20,FALSE)-1,0))=AND(J9>=OFFSET($C$4,MATCH(I9,$A$3:$A$20,FALSE)-1,0),J9<=OFFSET($D$4,MATCH(I9,$A$3:$A$20,FALSE)-1,0))=AND(J9>=OFFSET($C$5,MATCH(I9,$A$3:$A$20,FALSE)-1,0),J9<=OFFSET($D$5,MATCH(I9,$A$3:$A$20,FALSE)-1,0))  FALSEFALSETRUE
C =C11-5%=C11-0.01%Amber   A0.71=OR(AND(J10<>0,OR(J10<=OFFSET($C$3,MATCH(I10,$A$3:$A$20,FALSE)-1,0),J10<OFFSET($C$4,MATCH(I10,$A$3:$A$20,FALSE)-1,0))),J10>OFFSET($D$3,MATCH(I10,$A$3:$A$20,FALSE)-1,0))=AND(J10>=OFFSET($C$4,MATCH(I10,$A$3:$A$20,FALSE)-1,0),J10<=OFFSET($D$4,MATCH(I10,$A$3:$A$20,FALSE)-1,0))=AND(J10>=OFFSET($C$5,MATCH(I10,$A$3:$A$20,FALSE)-1,0),J10<=OFFSET($D$5,MATCH(I10,$A$3:$A$20,FALSE)-1,0))  TRUEFALSEFALSE
C 0.5=D9Green   B0.09=OR(AND(J11<>0,OR(J11<=OFFSET($C$3,MATCH(I11,$A$3:$A$20,FALSE)-1,0),J11<OFFSET($C$4,MATCH(I11,$A$3:$A$20,FALSE)-1,0))),J11>OFFSET($D$3,MATCH(I11,$A$3:$A$20,FALSE)-1,0))=AND(J11>=OFFSET($C$4,MATCH(I11,$A$3:$A$20,FALSE)-1,0),J11<=OFFSET($D$4,MATCH(I11,$A$3:$A$20,FALSE)-1,0))=AND(J11>=OFFSET($C$5,MATCH(I11,$A$3:$A$20,FALSE)-1,0),J11<=OFFSET($D$5,MATCH(I11,$A$3:$A$20,FALSE)-1,0))  TRUEFALSEFALSE
DKPA %=C13-5%1Red   C0.99=OR(AND(J12<>0,OR(J12<=OFFSET($C$3,MATCH(I12,$A$3:$A$20,FALSE)-1,0),J12<OFFSET($C$4,MATCH(I12,$A$3:$A$20,FALSE)-1,0))),J12>OFFSET($D$3,MATCH(I12,$A$3:$A$20,FALSE)-1,0))=AND(J12>=OFFSET($C$4,MATCH(I12,$A$3:$A$20,FALSE)-1,0),J12<=OFFSET($D$4,MATCH(I12,$A$3:$A$20,FALSE)-1,0))=AND(J12>=OFFSET($C$5,MATCH(I12,$A$3:$A$20,FALSE)-1,0),J12<=OFFSET($D$5,MATCH(I12,$A$3:$A$20,FALSE)-1,0))  FALSEFALSETRUE
D =C14-5%=C14-0.01%Amber   D0.38=OR(AND(J13<>0,OR(J13<=OFFSET($C$3,MATCH(I13,$A$3:$A$20,FALSE)-1,0),J13<OFFSET($C$4,MATCH(I13,$A$3:$A$20,FALSE)-1,0))),J13>OFFSET($D$3,MATCH(I13,$A$3:$A$20,FALSE)-1,0))=AND(J13>=OFFSET($C$4,MATCH(I13,$A$3:$A$20,FALSE)-1,0),J13<=OFFSET($D$4,MATCH(I13,$A$3:$A$20,FALSE)-1,0))=AND(J13>=OFFSET($C$5,MATCH(I13,$A$3:$A$20,FALSE)-1,0),J13<=OFFSET($D$5,MATCH(I13,$A$3:$A$20,FALSE)-1,0))  TRUEFALSEFALSE
D 0.55=D12Green   A0.16=OR(AND(J14<>0,OR(J14<=OFFSET($C$3,MATCH(I14,$A$3:$A$20,FALSE)-1,0),J14<OFFSET($C$4,MATCH(I14,$A$3:$A$20,FALSE)-1,0))),J14>OFFSET($D$3,MATCH(I14,$A$3:$A$20,FALSE)-1,0))=AND(J14>=OFFSET($C$4,MATCH(I14,$A$3:$A$20,FALSE)-1,0),J14<=OFFSET($D$4,MATCH(I14,$A$3:$A$20,FALSE)-1,0))=AND(J14>=OFFSET($C$5,MATCH(I14,$A$3:$A$20,FALSE)-1,0),J14<=OFFSET($D$5,MATCH(I14,$A$3:$A$20,FALSE)-1,0))  TRUEFALSEFALSE
EKPA %=C16-5%1Red   B0.23=OR(AND(J15<>0,OR(J15<=OFFSET($C$3,MATCH(I15,$A$3:$A$20,FALSE)-1,0),J15<OFFSET($C$4,MATCH(I15,$A$3:$A$20,FALSE)-1,0))),J15>OFFSET($D$3,MATCH(I15,$A$3:$A$20,FALSE)-1,0))=AND(J15>=OFFSET($C$4,MATCH(I15,$A$3:$A$20,FALSE)-1,0),J15<=OFFSET($D$4,MATCH(I15,$A$3:$A$20,FALSE)-1,0))=AND(J15>=OFFSET($C$5,MATCH(I15,$A$3:$A$20,FALSE)-1,0),J15<=OFFSET($D$5,MATCH(I15,$A$3:$A$20,FALSE)-1,0))  TRUEFALSEFALSE
E =C17-5%=C17-0.01%Amber   C0.66=OR(AND(J16<>0,OR(J16<=OFFSET($C$3,MATCH(I16,$A$3:$A$20,FALSE)-1,0),J16<OFFSET($C$4,MATCH(I16,$A$3:$A$20,FALSE)-1,0))),J16>OFFSET($D$3,MATCH(I16,$A$3:$A$20,FALSE)-1,0))=AND(J16>=OFFSET($C$4,MATCH(I16,$A$3:$A$20,FALSE)-1,0),J16<=OFFSET($D$4,MATCH(I16,$A$3:$A$20,FALSE)-1,0))=AND(J16>=OFFSET($C$5,MATCH(I16,$A$3:$A$20,FALSE)-1,0),J16<=OFFSET($D$5,MATCH(I16,$A$3:$A$20,FALSE)-1,0))  FALSEFALSETRUE
E 0.6=D15Green   D0.59=OR(AND(J17<>0,OR(J17<=OFFSET($C$3,MATCH(I17,$A$3:$A$20,FALSE)-1,0),J17<OFFSET($C$4,MATCH(I17,$A$3:$A$20,FALSE)-1,0))),J17>OFFSET($D$3,MATCH(I17,$A$3:$A$20,FALSE)-1,0))=AND(J17>=OFFSET($C$4,MATCH(I17,$A$3:$A$20,FALSE)-1,0),J17<=OFFSET($D$4,MATCH(I17,$A$3:$A$20,FALSE)-1,0))=AND(J17>=OFFSET($C$5,MATCH(I17,$A$3:$A$20,FALSE)-1,0),J17<=OFFSET($D$5,MATCH(I17,$A$3:$A$20,FALSE)-1,0))  FALSEFALSETRUE
FKPA %=C19-5%1Red   A0.92=OR(AND(J18<>0,OR(J18<=OFFSET($C$3,MATCH(I18,$A$3:$A$20,FALSE)-1,0),J18<OFFSET($C$4,MATCH(I18,$A$3:$A$20,FALSE)-1,0))),J18>OFFSET($D$3,MATCH(I18,$A$3:$A$20,FALSE)-1,0))=AND(J18>=OFFSET($C$4,MATCH(I18,$A$3:$A$20,FALSE)-1,0),J18<=OFFSET($D$4,MATCH(I18,$A$3:$A$20,FALSE)-1,0))=AND(J18>=OFFSET($C$5,MATCH(I18,$A$3:$A$20,FALSE)-1,0),J18<=OFFSET($D$5,MATCH(I18,$A$3:$A$20,FALSE)-1,0))  FALSEFALSETRUE
F =C20-5%=C20-0.01%Amber   B1=OR(AND(J19<>0,OR(J19<=OFFSET($C$3,MATCH(I19,$A$3:$A$20,FALSE)-1,0),J19<OFFSET($C$4,MATCH(I19,$A$3:$A$20,FALSE)-1,0))),J19>OFFSET($D$3,MATCH(I19,$A$3:$A$20,FALSE)-1,0))=AND(J19>=OFFSET($C$4,MATCH(I19,$A$3:$A$20,FALSE)-1,0),J19<=OFFSET($D$4,MATCH(I19,$A$3:$A$20,FALSE)-1,0))=AND(J19>=OFFSET($C$5,MATCH(I19,$A$3:$A$20,FALSE)-1,0),J19<=OFFSET($D$5,MATCH(I19,$A$3:$A$20,FALSE)-1,0))  FALSEFALSETRUE
F 0.55=D18Green   C0.61=OR(AND(J20<>0,OR(J20<=OFFSET($C$3,MATCH(I20,$A$3:$A$20,FALSE)-1,0),J20<OFFSET($C$4,MATCH(I20,$A$3:$A$20,FALSE)-1,0))),J20>OFFSET($D$3,MATCH(I20,$A$3:$A$20,FALSE)-1,0))=AND(J20>=OFFSET($C$4,MATCH(I20,$A$3:$A$20,FALSE)-1,0),J20<=OFFSET($D$4,MATCH(I20,$A$3:$A$20,FALSE)-1,0))=AND(J20>=OFFSET($C$5,MATCH(I20,$A$3:$A$20,FALSE)-1,0),J20<=OFFSET($D$5,MATCH(I20,$A$3:$A$20,FALSE)-1,0))  FALSEFALSETRUE
        D0.95=OR(AND(J21<>0,OR(J21<=OFFSET($C$3,MATCH(I21,$A$3:$A$20,FALSE)-1,0),J21<OFFSET($C$4,MATCH(I21,$A$3:$A$20,FALSE)-1,0))),J21>OFFSET($D$3,MATCH(I21,$A$3:$A$20,FALSE)-1,0))=AND(J21>=OFFSET($C$4,MATCH(I21,$A$3:$A$20,FALSE)-1,0),J21<=OFFSET($D$4,MATCH(I21,$A$3:$A$20,FALSE)-1,0))=AND(J21>=OFFSET($C$5,MATCH(I21,$A$3:$A$20,FALSE)-1,0),J21<=OFFSET($D$5,MATCH(I21,$A$3:$A$20,FALSE)-1,0))  FALSEFALSETRUE
        E0.61=OR(AND(J22<>0,OR(J22<=OFFSET($C$3,MATCH(I22,$A$3:$A$20,FALSE)-1,0),J22<OFFSET($C$4,MATCH(I22,$A$3:$A$20,FALSE)-1,0))),J22>OFFSET($D$3,MATCH(I22,$A$3:$A$20,FALSE)-1,0))=AND(J22>=OFFSET($C$4,MATCH(I22,$A$3:$A$20,FALSE)-1,0),J22<=OFFSET($D$4,MATCH(I22,$A$3:$A$20,FALSE)-1,0))=AND(J22>=OFFSET($C$5,MATCH(I22,$A$3:$A$20,FALSE)-1,0),J22<=OFFSET($D$5,MATCH(I22,$A$3:$A$20,FALSE)-1,0))  FALSEFALSETRUE
        F0.95=OR(AND(J23<>0,OR(J23<=OFFSET($C$3,MATCH(I23,$A$3:$A$20,FALSE)-1,0),J23<OFFSET($C$4,MATCH(I23,$A$3:$A$20,FALSE)-1,0))),J23>OFFSET($D$3,MATCH(I23,$A$3:$A$20,FALSE)-1,0))=AND(J23>=OFFSET($C$4,MATCH(I23,$A$3:$A$20,FALSE)-1,0),J23<=OFFSET($D$4,MATCH(I23,$A$3:$A$20,FALSE)-1,0))=AND(J23>=OFFSET($C$5,MATCH(I23,$A$3:$A$20,FALSE)-1,0),J23<=OFFSET($D$5,MATCH(I23,$A$3:$A$20,FALSE)-1,0))  FALSEFALSETRUE
        E0.56=OR(AND(J24<>0,OR(J24<=OFFSET($C$3,MATCH(I24,$A$3:$A$20,FALSE)-1,0),J24<OFFSET($C$4,MATCH(I24,$A$3:$A$20,FALSE)-1,0))),J24>OFFSET($D$3,MATCH(I24,$A$3:$A$20,FALSE)-1,0))=AND(J24>=OFFSET($C$4,MATCH(I24,$A$3:$A$20,FALSE)-1,0),J24<=OFFSET($D$4,MATCH(I24,$A$3:$A$20,FALSE)-1,0))=AND(J24>=OFFSET($C$5,MATCH(I24,$A$3:$A$20,FALSE)-1,0),J24<=OFFSET($D$5,MATCH(I24,$A$3:$A$20,FALSE)-1,0))  FALSETRUEFALSE
        F0.53=OR(AND(J25<>0,OR(J25<=OFFSET($C$3,MATCH(I25,$A$3:$A$20,FALSE)-1,0),J25<OFFSET($C$4,MATCH(I25,$A$3:$A$20,FALSE)-1,0))),J25>OFFSET($D$3,MATCH(I25,$A$3:$A$20,FALSE)-1,0))=AND(J25>=OFFSET($C$4,MATCH(I25,$A$3:$A$20,FALSE)-1,0),J25<=OFFSET($D$4,MATCH(I25,$A$3:$A$20,FALSE)-1,0))=AND(J25>=OFFSET($C$5,MATCH(I25,$A$3:$A$20,FALSE)-1,0),J25<=OFFSET($D$5,MATCH(I25,$A$3:$A$20,FALSE)-1,0))     

<tbody>
</tbody><colgroup><col><col><col><col><col><col span="3"><col><col><col><col><col><col><col><col span="3"></colgroup>
 
Last edited:

jorismoerings

Well-known Member
Joined
Jul 4, 2014
Messages
1,284
Alternative:
ABCDEFGHIJKLMNOPQR
1Conditional Formatting CriteriaCriteriaCompanyKPI %RedAmberGreenShould BeRedAmberGreen
2CompanyKPILow BoundUpper BoundColour DescriptionA26%TRUEFALSEFALSETRUEFALSEFALSE
3AKPA %75%100%RedB91%FALSEFALSETRUEFALSEFALSETRUE
4A80%85%AmberC9%TRUEFALSEFALSETRUEFALSEFALSE
5A85%100%GreenD18%TRUEFALSEFALSETRUEFALSEFALSE
6BKPA %55%100%RedA80%FALSETRUEFALSEFALSETRUEFALSE
7B60%65%AmberB70%FALSEFALSETRUEFALSEFALSETRUE
8B65%100%GreenC110%TRUEFALSEFALSETRUEFALSEFALSE
9CKPA %40%100%RedD60%FALSEFALSETRUEFALSEFALSETRUE
10C45%50%AmberA71%TRUEFALSEFALSETRUEFALSEFALSE
11C50%100%GreenB9%TRUEFALSEFALSETRUEFALSEFALSE
12DKPA %45%100%RedC99%FALSEFALSETRUEFALSEFALSETRUE
13D50%55%AmberD38%TRUEFALSEFALSETRUEFALSEFALSE
14D55%100%GreenA16%TRUEFALSEFALSETRUEFALSEFALSE
15EKPA %50%100%RedB23%TRUEFALSEFALSETRUEFALSEFALSE
16E55%60%AmberC66%FALSEFALSETRUEFALSEFALSETRUE
17E60%100%GreenD59%FALSEFALSETRUEFALSEFALSETRUE
18FKPA %45%100%RedA92%FALSEFALSETRUEFALSEFALSETRUE
19F50%55%AmberB100%FALSEFALSETRUEFALSEFALSETRUE
20F55%100%GreenC61%FALSEFALSETRUEFALSEFALSETRUE
21D95%FALSEFALSETRUEFALSEFALSETRUE
22E61%FALSEFALSETRUEFALSEFALSETRUE
23F95%FALSEFALSETRUEFALSEFALSETRUE
24E56%FALSETRUEFALSEFALSETRUEFALSE
25F53%FALSETRUEFALSEFALSETRUEFALSE

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
K2=AND($J2<>0,OR($J2<SUMPRODUCT(($A$3:$A$20=$I2)*($E$3:$E$20="Red")*($C$3:$C$20)),$J2>SUMPRODUCT(($A$3:$A$20=$I2)*($E$3:$E$20="Red")*($D$3:$D$20))))
L2=AND($J2>=SUMPRODUCT(($A$3:$A$20=$I2)*($E$3:$E$20="Amber")*($C$3:$C$20)),$J2<=SUMPRODUCT(($A$3:$A$20=$I2)*($E$3:$E$20="Amber")*($D$3:$D$20)))
M2=AND($J2>=SUMPRODUCT(($A$3:$A$20=$I2)*($E$3:$E$20="Green")*($C$3:$C$20)),$J2<=SUMPRODUCT(($A$3:$A$20=$I2)*($E$3:$E$20="Green")*($D$3:$D$20)))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

@ lateralthinker: your formula is missing the operator for the RED column:
=OR(AND(J2<>0,OR(J2<=OFFSET($C$3,MATCH(I2,$A$3:$A$20,FALSE)-1,0),J2>OFFSET($D$3,MATCH(I2,$A$3:$A$20,FALSE)-1,0))
 
Last edited:

LateralThinker

New Member
Joined
May 7, 2014
Messages
11
Sorry for my fomulas mistake.

I modified them.

thanks

Hi

Try this:

Conditional Formatting Criteria
 
Criteria
 
 
 
 
 
Company
KPI %
Red
Amber
Green
 
Should Be
Red
Amber
Green
Company
KPI
Low Bound
Upper Bound
Colour Description
 
 
 
A
0.26
=OR(AND(J2<>0,OR(J2<=OFFSET($C$3,MATCH(I2,$A$3:$A$20,FALSE)-1,0),J2><OFFSET($C$4,MATCH(I2,$A$3:$a$20,false)-1,0))),j2>OFFSET($D$3,MATCH(I2,$A$3:$A$20,FALSE)-1,0))</OFFSET($C$4,MATCH(I2,$A$3:$a$20,false)-1,0))),j2>
=AND(J2>=OFFSET($C$4,MATCH(I2,$A$3:$A$20,FALSE)-1,0),J2<=OFFSET($D$4,MATCH(I2,$A$3:$A$20,FALSE)-1,0))
=AND(J2>=OFFSET($C$5,MATCH(I2,$A$3:$A$20,FALSE)-1,0),J2<=OFFSET($D$5,MATCH(I2,$A$3:$A$20,FALSE)-1,0))
 
 
TRUE
FALSE
FALSE
A
KPA %
=C4-5%
1
Red
 
 
 
B
0.91
=OR(AND(J3<>0,OR(J3<=OFFSET($C$3,MATCH(I3,$A$3:$A$20,FALSE)-1,0),J3><OFFSET($C$4,MATCH(I3,$A$3:$a$20,false)-1,0))),j3>OFFSET($D$3,MATCH(I3,$A$3:$A$20,FALSE)-1,0))</OFFSET($C$4,MATCH(I3,$A$3:$a$20,false)-1,0))),j3>
=AND(J3>=OFFSET($C$4,MATCH(I3,$A$3:$A$20,FALSE)-1,0),J3<=OFFSET($D$4,MATCH(I3,$A$3:$A$20,FALSE)-1,0))
=AND(J3>=OFFSET($C$5,MATCH(I3,$A$3:$A$20,FALSE)-1,0),J3<=OFFSET($D$5,MATCH(I3,$A$3:$A$20,FALSE)-1,0))
 
 
FALSE
FALSE
TRUE
A
 
=C5-5%
=C5-0.01%
Amber
 
 
 
C
0.09
=OR(AND(J4<>0,OR(J4<=OFFSET($C$3,MATCH(I4,$A$3:$A$20,FALSE)-1,0),J4><OFFSET($C$4,MATCH(I4,$A$3:$a$20,false)-1,0))),j4>OFFSET($D$3,MATCH(I4,$A$3:$A$20,FALSE)-1,0))</OFFSET($C$4,MATCH(I4,$A$3:$a$20,false)-1,0))),j4>
=AND(J4>=OFFSET($C$4,MATCH(I4,$A$3:$A$20,FALSE)-1,0),J4<=OFFSET($D$4,MATCH(I4,$A$3:$A$20,FALSE)-1,0))
=AND(J4>=OFFSET($C$5,MATCH(I4,$A$3:$A$20,FALSE)-1,0),J4<=OFFSET($D$5,MATCH(I4,$A$3:$A$20,FALSE)-1,0))
 
 
TRUE
FALSE
FALSE
A
 
0.85
=D3
Green
 
 
 
D
0.18
=OR(AND(J5<>0,OR(J5<=OFFSET($C$3,MATCH(I5,$A$3:$A$20,FALSE)-1,0),J5><OFFSET($C$4,MATCH(I5,$A$3:$a$20,false)-1,0))),j5>OFFSET($D$3,MATCH(I5,$A$3:$A$20,FALSE)-1,0))</OFFSET($C$4,MATCH(I5,$A$3:$a$20,false)-1,0))),j5>
=AND(J5>=OFFSET($C$4,MATCH(I5,$A$3:$A$20,FALSE)-1,0),J5<=OFFSET($D$4,MATCH(I5,$A$3:$A$20,FALSE)-1,0))
=AND(J5>=OFFSET($C$5,MATCH(I5,$A$3:$A$20,FALSE)-1,0),J5<=OFFSET($D$5,MATCH(I5,$A$3:$A$20,FALSE)-1,0))
 
 
TRUE
FALSE
FALSE
B
KPA %
=C7-5%
1
Red
 
 
 
A
0.8
=OR(AND(J6<>0,OR(J6<=OFFSET($C$3,MATCH(I6,$A$3:$A$20,FALSE)-1,0),J6><OFFSET($C$4,MATCH(I6,$A$3:$a$20,false)-1,0))),j6>OFFSET($D$3,MATCH(I6,$A$3:$A$20,FALSE)-1,0))</OFFSET($C$4,MATCH(I6,$A$3:$a$20,false)-1,0))),j6>
=AND(J6>=OFFSET($C$4,MATCH(I6,$A$3:$A$20,FALSE)-1,0),J6<=OFFSET($D$4,MATCH(I6,$A$3:$A$20,FALSE)-1,0))
=AND(J6>=OFFSET($C$5,MATCH(I6,$A$3:$A$20,FALSE)-1,0),J6<=OFFSET($D$5,MATCH(I6,$A$3:$A$20,FALSE)-1,0))
 
 
FALSE
TRUE
FALSE
B
 
=C8-5%
=C8-0.01%
Amber
 
 
 
B
0.7
=OR(AND(J7<>0,OR(J7<=OFFSET($C$3,MATCH(I7,$A$3:$A$20,FALSE)-1,0),J7><OFFSET($C$4,MATCH(I7,$A$3:$a$20,false)-1,0))),j7>OFFSET($D$3,MATCH(I7,$A$3:$A$20,FALSE)-1,0))</OFFSET($C$4,MATCH(I7,$A$3:$a$20,false)-1,0))),j7>
=AND(J7>=OFFSET($C$4,MATCH(I7,$A$3:$A$20,FALSE)-1,0),J7<=OFFSET($D$4,MATCH(I7,$A$3:$A$20,FALSE)-1,0))
=AND(J7>=OFFSET($C$5,MATCH(I7,$A$3:$A$20,FALSE)-1,0),J7<=OFFSET($D$5,MATCH(I7,$A$3:$A$20,FALSE)-1,0))
 
 
FALSE
FALSE
TRUE
B
 
0.65
=D6
Green
 
 
 
C
1.1
=OR(AND(J8<>0,OR(J8<=OFFSET($C$3,MATCH(I8,$A$3:$A$20,FALSE)-1,0),J8><OFFSET($C$4,MATCH(I8,$A$3:$a$20,false)-1,0))),j8>OFFSET($D$3,MATCH(I8,$A$3:$A$20,FALSE)-1,0))</OFFSET($C$4,MATCH(I8,$A$3:$a$20,false)-1,0))),j8>
=AND(J8>=OFFSET($C$4,MATCH(I8,$A$3:$A$20,FALSE)-1,0),J8<=OFFSET($D$4,MATCH(I8,$A$3:$A$20,FALSE)-1,0))
=AND(J8>=OFFSET($C$5,MATCH(I8,$A$3:$A$20,FALSE)-1,0),J8<=OFFSET($D$5,MATCH(I8,$A$3:$A$20,FALSE)-1,0))
 
 
TRUE
FALSE
FALSE
C
KPA %
=C10-5%
1
Red
 
 
 
D
0.6
=OR(AND(J9<>0,OR(J9<=OFFSET($C$3,MATCH(I9,$A$3:$A$20,FALSE)-1,0),J9><OFFSET($C$4,MATCH(I9,$A$3:$a$20,false)-1,0))),j9>OFFSET($D$3,MATCH(I9,$A$3:$A$20,FALSE)-1,0))</OFFSET($C$4,MATCH(I9,$A$3:$a$20,false)-1,0))),j9>
=AND(J9>=OFFSET($C$4,MATCH(I9,$A$3:$A$20,FALSE)-1,0),J9<=OFFSET($D$4,MATCH(I9,$A$3:$A$20,FALSE)-1,0))
=AND(J9>=OFFSET($C$5,MATCH(I9,$A$3:$A$20,FALSE)-1,0),J9<=OFFSET($D$5,MATCH(I9,$A$3:$A$20,FALSE)-1,0))
 
 
FALSE
FALSE
TRUE
C
 
=C11-5%
=C11-0.01%
Amber
 
 
 
A
0.71
=OR(AND(J10<>0,OR(J10<=OFFSET($C$3,MATCH(I10,$A$3:$A$20,FALSE)-1,0),J10><OFFSET($C$4,MATCH(I10,$A$3:$a$20,false)-1,0))),j10>OFFSET($D$3,MATCH(I10,$A$3:$A$20,FALSE)-1,0))</OFFSET($C$4,MATCH(I10,$A$3:$a$20,false)-1,0))),j10>
=AND(J10>=OFFSET($C$4,MATCH(I10,$A$3:$A$20,FALSE)-1,0),J10<=OFFSET($D$4,MATCH(I10,$A$3:$A$20,FALSE)-1,0))
=AND(J10>=OFFSET($C$5,MATCH(I10,$A$3:$A$20,FALSE)-1,0),J10<=OFFSET($D$5,MATCH(I10,$A$3:$A$20,FALSE)-1,0))
 
 
TRUE
FALSE
FALSE
C
 
0.5
=D9
Green
 
 
 
B
0.09
=OR(AND(J11<>0,OR(J11<=OFFSET($C$3,MATCH(I11,$A$3:$A$20,FALSE)-1,0),J11<OFFSET($C$4,MATCH(I11,$A$3:$a$20,false)-1,0))),j11>O>FFSET($D$3,MATCH(I11,$A$3:$A$20,FALSE)-1,0))</OFFSET($C$4,MATCH(I11,$A$3:$a$20,false)-1,0))),j11>
=AND(J11>=OFFSET($C$4,MATCH(I11,$A$3:$A$20,FALSE)-1,0),J11<=OFFSET($D$4,MATCH(I11,$A$3:$A$20,FALSE)-1,0))
=AND(J11>=OFFSET($C$5,MATCH(I11,$A$3:$A$20,FALSE)-1,0),J11<=OFFSET($D$5,MATCH(I11,$A$3:$A$20,FALSE)-1,0))
 
 
TRUE
FALSE
FALSE
D
KPA %
=C13-5%
1
Red
 
 
 
C
0.99
=OR(AND(J12<>0,OR(J12<=OFFSET($C$3,MATCH(I12,$A$3:$A$20,FALSE)-1,0),J12><OFFSET($C$4,MATCH(I12,$A$3:$a$20,false)-1,0))),j12>OFFSET($D$3,MATCH(I12,$A$3:$A$20,FALSE)-1,0))</OFFSET($C$4,MATCH(I12,$A$3:$a$20,false)-1,0))),j12>
=AND(J12>=OFFSET($C$4,MATCH(I12,$A$3:$A$20,FALSE)-1,0),J12<=OFFSET($D$4,MATCH(I12,$A$3:$A$20,FALSE)-1,0))
=AND(J12>=OFFSET($C$5,MATCH(I12,$A$3:$A$20,FALSE)-1,0),J12<=OFFSET($D$5,MATCH(I12,$A$3:$A$20,FALSE)-1,0))
 
 
FALSE
FALSE
TRUE
D
 
=C14-5%
=C14-0.01%
Amber
 
 
 
D
0.38
=OR(AND(J13<>0,OR(J13<=OFFSET($C$3,MATCH(I13,$A$3:$A$20,FALSE)-1,0),J13><OFFSET($C$4,MATCH(I13,$A$3:$a$20,false)-1,0))),j13>OFFSET($D$3,MATCH(I13,$A$3:$A$20,FALSE)-1,0))</OFFSET($C$4,MATCH(I13,$A$3:$a$20,false)-1,0))),j13>
=AND(J13>=OFFSET($C$4,MATCH(I13,$A$3:$A$20,FALSE)-1,0),J13<=OFFSET($D$4,MATCH(I13,$A$3:$A$20,FALSE)-1,0))
=AND(J13>=OFFSET($C$5,MATCH(I13,$A$3:$A$20,FALSE)-1,0),J13<=OFFSET($D$5,MATCH(I13,$A$3:$A$20,FALSE)-1,0))
 
 
TRUE
FALSE
FALSE
D
 
0.55
=D12
Green
 
 
 
A
0.16
=OR(AND(J14<>0,OR(J14<=OFFSET($C$3,MATCH(I14,$A$3:$A$20,FALSE)-1,0),J14><OFFSET($C$4,MATCH(I14,$A$3:$a$20,false)-1,0))),j14>OFFSET($D$3,MATCH(I14,$A$3:$A$20,FALSE)-1,0))</OFFSET($C$4,MATCH(I14,$A$3:$a$20,false)-1,0))),j14>
=AND(J14>=OFFSET($C$4,MATCH(I14,$A$3:$A$20,FALSE)-1,0),J14<=OFFSET($D$4,MATCH(I14,$A$3:$A$20,FALSE)-1,0))
=AND(J14>=OFFSET($C$5,MATCH(I14,$A$3:$A$20,FALSE)-1,0),J14<=OFFSET($D$5,MATCH(I14,$A$3:$A$20,FALSE)-1,0))
 
 
TRUE
FALSE
FALSE
E
KPA %
=C16-5%
1
Red
 
 
 
B
0.23
=OR(AND(J15<>0,OR(J15<=OFFSET($C$3,MATCH(I15,$A$3:$A$20,FALSE)-1,0),J15><OFFSET($C$4,MATCH(I15,$A$3:$a$20,false)-1,0))),j15>OFFSET($D$3,MATCH(I15,$A$3:$A$20,FALSE)-1,0))</OFFSET($C$4,MATCH(I15,$A$3:$a$20,false)-1,0))),j15>
=AND(J15>=OFFSET($C$4,MATCH(I15,$A$3:$A$20,FALSE)-1,0),J15<=OFFSET($D$4,MATCH(I15,$A$3:$A$20,FALSE)-1,0))
=AND(J15>=OFFSET($C$5,MATCH(I15,$A$3:$A$20,FALSE)-1,0),J15<=OFFSET($D$5,MATCH(I15,$A$3:$A$20,FALSE)-1,0))
 
 
TRUE
FALSE
FALSE
E
 
=C17-5%
=C17-0.01%
Amber
 
 
 
C
0.66
=OR(AND(J16<>0,OR(J16<=OFFSET($C$3,MATCH(I16,$A$3:$A$20,FALSE)-1,0),J16><OFFSET($C$4,MATCH(I16,$A$3:$a$20,false)-1,0))),j16>OFFSET($D$3,MATCH(I16,$A$3:$A$20,FALSE)-1,0))</OFFSET($C$4,MATCH(I16,$A$3:$a$20,false)-1,0))),j16>
=AND(J16>=OFFSET($C$4,MATCH(I16,$A$3:$A$20,FALSE)-1,0),J16<=OFFSET($D$4,MATCH(I16,$A$3:$A$20,FALSE)-1,0))
=AND(J16>=OFFSET($C$5,MATCH(I16,$A$3:$A$20,FALSE)-1,0),J16<=OFFSET($D$5,MATCH(I16,$A$3:$A$20,FALSE)-1,0))
 
 
FALSE
FALSE
TRUE
E
 
0.6
=D15
Green
 
 
 
D
0.59
=OR(AND(J17<>0,OR(J17<=OFFSET($C$3,MATCH(I17,$A$3:$A$20,FALSE)-1,0),J17><OFFSET($C$4,MATCH(I17,$A$3:$a$20,false)-1,0))),j17>OFFSET($D$3,MATCH(I17,$A$3:$A$20,FALSE)-1,0))</OFFSET($C$4,MATCH(I17,$A$3:$a$20,false)-1,0))),j17>
=AND(J17>=OFFSET($C$4,MATCH(I17,$A$3:$A$20,FALSE)-1,0),J17<=OFFSET($D$4,MATCH(I17,$A$3:$A$20,FALSE)-1,0))
=AND(J17>=OFFSET($C$5,MATCH(I17,$A$3:$A$20,FALSE)-1,0),J17<=OFFSET($D$5,MATCH(I17,$A$3:$A$20,FALSE)-1,0))
 
 
FALSE
FALSE
TRUE
F
KPA %
=C19-5%
1
Red
 
 
 
A
0.92
=OR(AND(J18<>0,OR(J18<=OFFSET($C$3,MATCH(I18,$A$3:$A$20,FALSE)-1,0),J18><OFFSET($C$4,MATCH(I18,$A$3:$a$20,false)-1,0))),j18>OFFSET($D$3,MATCH(I18,$A$3:$A$20,FALSE)-1,0))</OFFSET($C$4,MATCH(I18,$A$3:$a$20,false)-1,0))),j18>
=AND(J18>=OFFSET($C$4,MATCH(I18,$A$3:$A$20,FALSE)-1,0),J18<=OFFSET($D$4,MATCH(I18,$A$3:$A$20,FALSE)-1,0))
=AND(J18>=OFFSET($C$5,MATCH(I18,$A$3:$A$20,FALSE)-1,0),J18<=OFFSET($D$5,MATCH(I18,$A$3:$A$20,FALSE)-1,0))
 
 
FALSE
FALSE
TRUE
F
 
=C20-5%
=C20-0.01%
Amber
 
 
 
B
1
=OR(AND(J19<>0,OR(J19<=OFFSET($C$3,MATCH(I19,$A$3:$A$20,FALSE)-1,0),J19><OFFSET($C$4,MATCH(I19,$A$3:$a$20,false)-1,0))),j19>OFFSET($D$3,MATCH(I19,$A$3:$A$20,FALSE)-1,0))</OFFSET($C$4,MATCH(I19,$A$3:$a$20,false)-1,0))),j19>
=AND(J19>=OFFSET($C$4,MATCH(I19,$A$3:$A$20,FALSE)-1,0),J19<=OFFSET($D$4,MATCH(I19,$A$3:$A$20,FALSE)-1,0))
=AND(J19>=OFFSET($C$5,MATCH(I19,$A$3:$A$20,FALSE)-1,0),J19<=OFFSET($D$5,MATCH(I19,$A$3:$A$20,FALSE)-1,0))
 
 
FALSE
FALSE
TRUE
F
 
0.55
=D18
Green
 
 
 
C
0.61
=OR(AND(J20<>0,OR(J20<=OFFSET($C$3,MATCH(I20,$A$3:$A$20,FALSE)-1,0),J20><OFFSET($C$4,MATCH(I20,$A$3:$a$20,false)-1,0))),j20>OFFSET($D$3,MATCH(I20,$A$3:$A$20,FALSE)-1,0))</OFFSET($C$4,MATCH(I20,$A$3:$a$20,false)-1,0))),j20>
=AND(J20>=OFFSET($C$4,MATCH(I20,$A$3:$A$20,FALSE)-1,0),J20<=OFFSET($D$4,MATCH(I20,$A$3:$A$20,FALSE)-1,0))
=AND(J20>=OFFSET($C$5,MATCH(I20,$A$3:$A$20,FALSE)-1,0),J20<=OFFSET($D$5,MATCH(I20,$A$3:$A$20,FALSE)-1,0))
 
 
FALSE
FALSE
TRUE
 
 
 
 
 
 
 
 
D
0.95
=OR(AND(J21<>0,OR(J21<=OFFSET($C$3,MATCH(I21,$A$3:$A$20,FALSE)-1,0),J21><OFFSET($C$4,MATCH(I21,$A$3:$a$20,false)-1,0))),j21>OFFSET($D$3,MATCH(I21,$A$3:$A$20,FALSE)-1,0))</OFFSET($C$4,MATCH(I21,$A$3:$a$20,false)-1,0))),j21>
=AND(J21>=OFFSET($C$4,MATCH(I21,$A$3:$A$20,FALSE)-1,0),J21<=OFFSET($D$4,MATCH(I21,$A$3:$A$20,FALSE)-1,0))
=AND(J21>=OFFSET($C$5,MATCH(I21,$A$3:$A$20,FALSE)-1,0),J21<=OFFSET($D$5,MATCH(I21,$A$3:$A$20,FALSE)-1,0))
 
 
FALSE
FALSE
TRUE
 
 
 
 
 
 
 
 
E
0.61
=OR(AND(J22<>0,OR(J22<=OFFSET($C$3,MATCH(I22,$A$3:$A$20,FALSE)-1,0),J22><OFFSET($C$4,MATCH(I22,$A$3:$a$20,false)-1,0))),j22>OFFSET($D$3,MATCH(I22,$A$3:$A$20,FALSE)-1,0))</OFFSET($C$4,MATCH(I22,$A$3:$a$20,false)-1,0))),j22>
=AND(J22>=OFFSET($C$4,MATCH(I22,$A$3:$A$20,FALSE)-1,0),J22<=OFFSET($D$4,MATCH(I22,$A$3:$A$20,FALSE)-1,0))
=AND(J22>=OFFSET($C$5,MATCH(I22,$A$3:$A$20,FALSE)-1,0),J22<=OFFSET($D$5,MATCH(I22,$A$3:$A$20,FALSE)-1,0))
 
 
FALSE
FALSE
TRUE
 
 
 
 
 
 
 
 
F
0.95
=OR(AND(J23<>0,OR(J23<=OFFSET($C$3,MATCH(I23,$A$3:$A$20,FALSE)-1,0),J23><OFFSET($C$4,MATCH(I23,$A$3:$a$20,false)-1,0))),j23>OFFSET($D$3,MATCH(I23,$A$3:$A$20,FALSE)-1,0))</OFFSET($C$4,MATCH(I23,$A$3:$a$20,false)-1,0))),j23>
=AND(J23>=OFFSET($C$4,MATCH(I23,$A$3:$A$20,FALSE)-1,0),J23<=OFFSET($D$4,MATCH(I23,$A$3:$A$20,FALSE)-1,0))
=AND(J23>=OFFSET($C$5,MATCH(I23,$A$3:$A$20,FALSE)-1,0),J23<=OFFSET($D$5,MATCH(I23,$A$3:$A$20,FALSE)-1,0))
 
 
FALSE
FALSE
TRUE
 
 
 
 
 
 
 
 
E
0.56
=OR(AND(J24<>0,OR(J24<=OFFSET($C$3,MATCH(I24,$A$3:$A$20,FALSE)-1,0),J24><OFFSET($C$4,MATCH(I24,$A$3:$a$20,false)-1,0))),j24>OFFSET($D$3,MATCH(I24,$A$3:$A$20,FALSE)-1,0))</OFFSET($C$4,MATCH(I24,$A$3:$a$20,false)-1,0))),j24>
=AND(J24>=OFFSET($C$4,MATCH(I24,$A$3:$A$20,FALSE)-1,0),J24<=OFFSET($D$4,MATCH(I24,$A$3:$A$20,FALSE)-1,0))
=AND(J24>=OFFSET($C$5,MATCH(I24,$A$3:$A$20,FALSE)-1,0),J24<=OFFSET($D$5,MATCH(I24,$A$3:$A$20,FALSE)-1,0))
 
 
FALSE
TRUE
FALSE
 
 
 
 
 
 
 
 
F
0.53
=OR(AND(J25<>0,OR(J25<=OFFSET($C$3,MATCH(I25,$A$3:$A$20,FALSE)-1,0),J25><OFFSET($C$4,MATCH(I25,$A$3:$a$20,false)-1,0))),j25>OFFSET($D$3,MATCH(I25,$A$3:$A$20,FALSE)-1,0))</OFFSET($C$4,MATCH(I25,$A$3:$a$20,false)-1,0))),j25>
=AND(J25>=OFFSET($C$4,MATCH(I25,$A$3:$A$20,FALSE)-1,0),J25<=OFFSET($D$4,MATCH(I25,$A$3:$A$20,FALSE)-1,0))
=AND(J25>=OFFSET($C$5,MATCH(I25,$A$3:$A$20,FALSE)-1,0),J25<=OFFSET($D$5,MATCH(I25,$A$3:$A$20,FALSE)-1,0))
 
 
 
 
 

<tbody>
</tbody>
 
Last edited:

Biz

Well-known Member
Joined
May 18, 2009
Messages
1,677
Thank You LateralThinker and jorismoerings for the very nice solutions.

@ jorismoerings missing items in red text

Red Formula should be
=AND($J2<>0,OR($J2<=SUMPRODUCT(($A$3:$A$20=$I2)*($E$3:$E$20="Red")*($C$3:$C$20)),$J2>SUMPRODUCT(($A$3:$A$20=$I2)*($E$3:$E$20="Red")*($D$3:$D$20))))

Thank you again guys.

Biz
 

Watch MrExcel Video

Forum statistics

Threads
1,122,335
Messages
5,595,576
Members
413,996
Latest member
mabelO

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top