Forum Discussion

wmfw2360's avatar
wmfw2360
Copper Contributor
Jul 27, 2025
Solved

Incentive calculations with multiple conditions


I need to formulate the special incentive calculation based on the below conditions :- 

If agent has an achievement of <70% in any 1 category then he is eligible for special permium in the categories where his achievement is 90% and above.
If agent has an achievement of >70% in all 5 categories he is not eligble for the special premium


And this is the incentive scheme

For Example

The attached file is what I'm currently working with, but it is more detailed with multiple agents.

How can I formulate this ?

BI & Data Analysis
excel
Formulas and Functions
  • m_tarler's avatar
    m_tarler
    Jul 28, 2025

    Here is a formula:

    =IF([@[Achievement %]]<90%,
      0,
      IF(MIN(FILTER([Achievement %],[@[Agent Name]]=[Agent Name]))>70%,
          0,
          INDEX($J$4:$N$7,XMATCH([@[Achievement %]],$I$4:$I$7,-1,1),XMATCH([@Category],$J$3:$N$3))))

     

2 Replies

  • m_tarler's avatar
    m_tarler
    Bronze Contributor
    Jul 28, 2025

    Here is a formula:

    =IF([@[Achievement %]]<90%,
      0,
      IF(MIN(FILTER([Achievement %],[@[Agent Name]]=[Agent Name]))>70%,
          0,
          INDEX($J$4:$N$7,XMATCH([@[Achievement %]],$I$4:$I$7,-1,1),XMATCH([@Category],$J$3:$N$3))))

     

Resources