Dec 15 2022 02:06 AM
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
Dec 15 2022 05:26 AM
On which column(s) should the color of Appetite Status be based?
Dec 15 2022 07:47 AM
Hi @Hans Vogelaar , 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
Dec 15 2022 08:53 AM
Solution@MarkD72 I created 1 Lambda function and 6 conditional formatting rules
the lambda was:
LcolVal = LAMBDA(in, INDEX(in, XMATCH(TRUE, ISNUMBER(in), 0, -1)))
the rules are:
Dec 15 2022 12:11 PM
@mtarler Brilliant piece of work :)
Never heard of LAMDA before so will spend a bit of time understanding the function and formulas you have used.
Thanks for this
Mark
Dec 15 2022 12:14 PM
Dec 15 2022 08:53 AM
Solution@MarkD72 I created 1 Lambda function and 6 conditional formatting rules
the lambda was:
LcolVal = LAMBDA(in, INDEX(in, XMATCH(TRUE, ISNUMBER(in), 0, -1)))
the rules are: