Function help needed urgently!!!

[Ann Shaw]

Hi anyone who can help...

I have two sets of figures 1 is sales and 1 is target.
Eg:

Sales Target Incentive
5 5

In the Incentive column I want a formula that will
calculate the Incentive payment for employees who exceed
there target. The easy part is the if statement for
example 10% of their sales if they exceed target:
=if(sales>target,Sales*10%,0)

The complicated part comes in to play when only when they
hit multiples of 10 are they entitled to an incentive:

If the Sales are 5 and the Target is 5 - no incentive.
If the Sales are 10 and the Target is 5 - no incentive.
If the Sales are 15 then the Incentive should show 5
If the Sales are 20 then the Incentive is still 5.
If the Sales are 25 the the Incentive should show 10.
If the Sales are 30 then the Incentive is still 10.
If the Sales are 35 then the Incentive should show 15.

For every multiple of 10 in the Sales the Incentive
increases by 5. Any ideas???

In other words every time they hit 10 over the target they
get 5 (Euro)

Thanks in advance
Ann Ireland

 

[JE McGimpsey]

One way:

=MAX(0,INT((A1-0.1)/10)*5)