How to calculate incremental percentages of a total.

Hi everyone I`m having trouble with completion of a formula.

We pay our staff bonuses on product sales we have 11 staff and as they sell more the bonus % increases This has become and effort to work out so I was trying ti create a spred sheet to calculate this but hit a wall.
Staff member 1.
sells $2500 of product. Bonus works 0-1000 nothing 1000 - 2000 =10% 2000 - 3000 = 15%

I got this far then could not stop the % in the 1000 amount. =IF(A1<1000,0,A1-1000)*10%

In this example: First $1000 = 0 Bonus Second $1000=10% Bonus Last $500= 15% Bonus

I would like to input Just one amount of sales and have the system work out the total amount of bonus to be paid.

Any assistance with this would be well received, Thankyou in advance.

John McGimpsey shows how to do it here:

http://www.mcgimpsey.com/excel/variablerate.html

Hope this helps.

Pete

If your data is in A1, In the cell you require the formula:

Formula:

Please Login or Register  to view this content.

and format the cell as a %