Forum Discussion

Copper Contributor

Dec 15, 2022

Solved

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 per...

excel

Formulas and Functions

mtarler
Dec 15, 2022
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:
appetite.xlsx16 KB

MarkD72

Copper Contributor

Dec 15, 2022

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

mtarler

Silver Contributor

Dec 15, 2022

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:

appetite.xlsx16 KB

MarkD72
Copper Contributor
Dec 15, 2022
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
- mtarler
  Silver Contributor
  Dec 15, 2022
  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