Forum Discussion
Conditional Formatting based on a % of a min / max value
Hello
I've created a table where I would like to track a monthly actual value against a set min and max tolerance threshold - example below.
| Key Risk Indicator (over 12 month period) | Jun 22 Value | Jul 22 Value | Aug 22 Value | Sep 22 Value | Oct 22 Value | Nov 22 Value | Dec 22 Value | Min Tol | Max Tol | Appetite Status |
| KRI 1 | 0.00 | 0.01 | 0.00 | 0.00 | 0.01 | 0.07 | 0.30 | -0.05 | 0.05 | Inside |
| KRI 2 | 159000.00 | 1512.00 | 0.00 | 0.00 | 3496106.00 | 1334678.00 | 45998.00 | 500000.00 | 2000000.00 | Outside |
| KRI 3 | 1.20 | 0.97 | 0.96 | 0.94 | 0.96 | 0.96 | 1.20 | 0.92 | 1.00 | Inside |
| KRI 5 | 0.13 | 0.06 | 0.08 | 0.04 | 0.05 | 0.17 | 1.00 | -0.05 | 0.05 | Outside |
| KRI 7 | 0.02 | 0.03 | 0.05 | 0.10 | 0.03 | 0.02 | 0.04 | 0.02 | 0.05 | Inside |
| KRI 9 | 0.00 | 0.00 | 2.00 | 0.00 | 2.00 | 2.00 | 7.00 | 0.00 | 5.00 | Inside |
| KRI 10 | 3.00 | 5.00 | 7.00 | 3.00 | 3.00 | 1.00 | 8.00 | 3.00 | 7.00 | Inside |
My thinking is this:
If actual cell value x is within 10% of min tol cell value, then, actual cell value fill = Amber and Appetite Status cell = Amber
If actual cell value x within 20% of max tol value, then, actual cell value fill = Amber and Appetite Status cell = Amber
If actual value x is <= to min tol value, then actual cell value fill = Red and Appetite Status cell = Red
If actual value x is => to max value, then actual cell value fill = Red and Appetite Status cell = Red
Else actual cell value = Green and Appetite Status cell = Green
I have played around with Excel Conditional Formatting options without any success and was hoping somebody may be able to help with a suggested formula to achieve what I'm trying to do?
Thanks in advance
5 Replies
On which column(s) should the color of Appetite Status be based?
Hi HansVogelaar , thanks for replying. The Appetite status will be based on the last or most recent actual available. I've tried to visualise what I'm aiming for below
Regards
Mark
