Automate bonus payment calculations

[Excelbeginner1]

Hi,

Please see attached.
I am in need for help on column J which I need to auto populate depending on the criteria mentioned in the free text on my spreadsheet..
To give you some back ground, it is to automate bonus payment calculations. Column J is a deduction field which will feed in another sum.

Column G = amount paid after one week of no errors
Column H = errors/picks
Column I = % of above
Column J = Error Adjustment (deductions) - As a rule, A picker can have 1 error per 100 picks (1% before being deducted £3 thereafter)
For example: picked 100 orders, no errors = £0 deductions
picked 100 orders, 1 error = £0 deductions
picked 99 orders, 1 error = £3 deductions
picked 199 orders, 1 error = £3 deductions
picked 200 orders, 2 errors = £0 deductions
picked 199 orders, 2 errors = £3 deductions

The thresholds are:
0 - 99 - no errors allowance
100 - 199 - 1 error allowance
200 - 299 - 2 error allowance
300 - 399 - 3 error allowance etc…

Deductions are as follows
£3 per occasion error above the allowance

Thanks in anticipation

Paul

[p24leclerc]

Try this formula in J4:

Please Login or Register  to view this content.