Working on an incentive structure and looking for some help with formula around this.
Assuming IF AND should work here or would this be easier with look up?
I have included a solution that conforms to my way of using Excel. It is far from standard and I make no attempt to achieve compatibility with solutions other than 365 or Excel 2021.
Code:
= MAP(threshold,limit,percentage,
LAMBDA(th,lt,p, p * MAX(MIN(eligibleAmt,lt)-th,0)) )
Out of curiosity, your result for eligible amounts >100,000 are not what I would expect.
Hi Peter - the codes are a bit of a challenge for me...will surely dig deep to understand.
On the result for >100,000 - the intention was to get the team to push past the target for the full 3% (hence inconsistent with the scale on the other thresholds)
-K