Tiered Incentive Calculation

  • Hello Experts!

    I am trying to calculate the incentive (Points Accrued in the attached Excel file) for a staffer, based on the number of chats he handles for a particular channel count.

    Though I can get it to calculate for the ONE criteria, I'm stuck trying to nest the IF and ANDs. I tried Google and other sites that suggest a solution, but I'm either too stupid to figure it out, or it doesn't cover enough tiers.

    Would love to have a helping hand figuring this out.

    Much thanks in advance.

  • This is what I managed to do:

    =IF(AND(OR(E2="4 Channels"),G2>20),"15",IF(AND(OR(E2="4 Channels"),G2<18),"0",IF(AND(OR(E2="4 Channels"),G2<=20,G2>=18),"10",IF(AND(OR(E2="3 Channels"),G2>16),"15",IF(AND(OR(E2="3 Channels"),G2<14),"0",IF(AND(OR(E2="3 Channels"),G2<=16,G2>=14),"10",IF(AND(OR(E2="2 Channels"),G2>13),"15",IF(AND(OR(E2="2 Channels"),G2<11),"0",IF(AND(OR(E2="2 Channels"),G2<=13,G2>=11),"10","")))))))))

    looks ungainly, though.. I'm sure the experts will have a simpler solution to this, as always..


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!