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

HansVogelaar

MVP

Dec 15, 2022

MarkD72

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

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