Conditional Formatting based on a % of a min / max value

[MarkD72]

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

[#2]

@MarkD72 

On which column(s) should the color of Appetite Status be based?

[#3]

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

[#4 · Accepted 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:

[#5]

@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

[#6]

Highly recommend learning Lambda and Let but if needed you could just replace the
LcolVal(xxx) with the INDEX(xxx, XMATCH(TRUE, ISNUMBER(xxx), 0, -1)) from the LAMBDA in each case