Incentives calculation

[st_judeu@yahoo.com]

Good day all.

Please the attached is a table of sales for a period and the incentives to be paid. I have included the criteria for incentive payment in the sheet.

Please what I want is a formular that will calculate incentives for all the team members based on the criteria given.

Thanks for your usual help.

[martindwilson]

N0/crate
N2/crate
N3/crate
N4/crate
N5/crate
what does all that mean?

[st_judeu@yahoo.com]

N means Naira. That is our currency Unit here in nigeria.
N0/crate means Zero Naira per crate
N2/crate means Two Naira per crate
etc

[st_judeu@yahoo.com]

I've attached another file using dollar which is a more generic currency. I hope my explanation is better understood now.

[martindwilson]

try this
=SUMPRODUCT(--(C3>{0,2000,3000,4500,6000}), (C3-{0,2000,3000,4500,6000}), {0,2,1,1,1})
derived from here
http://www.mcgimpsey.com/excel/variablerate.html

[st_judeu@yahoo.com]

Oh thanks Martindwilson. It worked like magic. I was using:

=IF(C3<=2000,0,IF(C3<=3000,(C3-2000)*2,IF(C3<=4500,(1000*2)+((C3-3000)*3),IF(C3<=6000,(1000*2)+(1500*3)+((C3-4500)*4),(1000*2)+(1500*3)+(1500*4)+((C3-6000)*5)))))

but felt I could get something better from this house. Thanks greatly.