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
<tbody>
</tbody>
<tbody>
</tbody>
Formulas Used
Excel 2013 32 bit
<tbody>
</tbody>
<tbody>
</tbody>
Biz
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 | Company | KPI % | Red | Amber | Green | Should Be | Red | Amber | Green | |||||||
2 | Company | KPI | Low Bound | Upper Bound | Colour Description | A | 26% | TRUE | FALSE | FALSE | TRUE | FALSE | FALSE | |||||
3 | A | KPA % | 75% | 100% | Red | B | 91% | FALSE | FALSE | TRUE | FALSE | FALSE | TRUE | |||||
4 | A | 80% | 85% | Amber | C | 9% | TRUE | FALSE | FALSE | TRUE | FALSE | FALSE | ||||||
5 | A | 85% | 100% | Green | D | 18% | TRUE | FALSE | FALSE | TRUE | FALSE | FALSE | ||||||
6 | B | KPA % | 55% | 100% | Red | A | 80% | FALSE | TRUE | FALSE | FALSE | TRUE | FALSE | |||||
7 | B | 60% | 65% | Amber | B | 70% | TRUE | FALSE | FALSE | FALSE | FALSE | TRUE | ||||||
8 | B | 65% | 100% | Green | C | 110% | TRUE | FALSE | FALSE | TRUE | FALSE | FALSE | ||||||
9 | C | KPA % | 40% | 100% | Red | D | 60% | TRUE | FALSE | FALSE | FALSE | FALSE | TRUE | |||||
10 | C | 45% | 50% | Amber | A | 71% | TRUE | FALSE | FALSE | TRUE | FALSE | FALSE | ||||||
11 | C | 50% | 100% | Green | B | 9% | TRUE | FALSE | FALSE | TRUE | FALSE | FALSE | ||||||
12 | D | KPA % | 45% | 100% | Red | C | 99% | FALSE | FALSE | TRUE | FALSE | FALSE | TRUE | |||||
13 | D | 50% | 55% | Amber | D | 38% | TRUE | FALSE | FALSE | TRUE | FALSE | FALSE | ||||||
14 | D | 55% | 100% | Green | A | 16% | TRUE | FALSE | FALSE | TRUE | FALSE | FALSE | ||||||
15 | E | KPA % | 50% | 100% | Red | B | 23% | TRUE | FALSE | FALSE | TRUE | FALSE | FALSE | |||||
16 | E | 55% | 60% | Amber | C | 66% | TRUE | FALSE | FALSE | FALSE | FALSE | TRUE | ||||||
17 | E | 60% | 100% | Green | D | 59% | TRUE | FALSE | FALSE | FALSE | FALSE | TRUE | ||||||
18 | F | KPA % | 45% | 100% | Red | A | 92% | FALSE | FALSE | TRUE | FALSE | FALSE | TRUE | |||||
19 | F | 50% | 55% | Amber | B | 100% | FALSE | FALSE | TRUE | FALSE | FALSE | TRUE | ||||||
20 | F | 55% | 100% | Green | C | 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 | Company | KPI % | Red | Amber | Green | Should Be | Red | Amber | Green | |||||||
2 | Company | KPI | Low Bound | Upper Bound | Colour Description | A | 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% | Red | B | 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% | Amber | C | 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 | Green | D | 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% | Red | A | 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% | Amber | B | 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 | Green | C | 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% | Red | D | 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% | Amber | A | 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 | Green | B | 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% | Red | C | 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% | Amber | D | 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 | Green | A | 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% | Red | B | 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% | Amber | C | 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 | Green | D | 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% | Red | A | 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% | Amber | B | 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 | Green | C | 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