# HELP... Commission of 20% if an employee makes more than 2.7 times their wages

1. ## HELP... Commission of 20% if an employee makes more than 2.7 times their wages

I have an issue in Excel 2007 that I do not have the formula knowledge to write.

We own a hairdressers and wish to display targets to our employees. We have a spreadsheet that is working fine for each employee.

As an example say “Tom” has earned £897.03 (I15) this month and we have paid him £353.92 (I25)

We have used the below calculation to work out a simple % =I15/I25-1 = 153%

Here is my issue.

We want to be able to give a commission of 20% if he makes more than 2.7 times his wages.

Can this be done?

2. ## Re: HELP... Commission of 20% if an employee makes more than 2.7 times their wages

This should give you the commission amount

=IF(I15>I25*2.7,0.2*I15,0)

3. ## Re: HELP... Commission of 20% if an employee makes more than 2.7 times their wages

Hi, I have just tried that formula and if the *2.7 is stepped up or down to say 2.6 or 2.8 then it displays 0.00 until 2.4 which is odd to me. Also can I ask what the 0.2 plays in the formular?

4. ## Re: HELP... Commission of 20% if an employee makes more than 2.7 times their wages

I have just tried that formula and if the *2.7 is stepped up or down to say 2.6 or 2.8 then it displays 0.00 until 2.4 which is odd to me
This would depend on the values in I15 and I25. As you suggested, commission to be calculated only if it exceeds 2.7 times. Perhaps, your input values equate to I15 being higher than I25 times 2.4.

what the 0.2 plays in the formular
0.2 or 20% is the commission rate

5. ## Re: HELP... Commission of 20% if an employee makes more than 2.7 times their wages

Ok, I think I see where I have not explained it very well. The 20% is only paid out on the amount above 2.7 times their wage and not the whole amount. So for instance, if the x2.7 hteir wage took them to say £300, only the amount above this would be paid at 20%. Sorry.

6. ## Re: HELP... Commission of 20% if an employee makes more than 2.7 times their wages

In that case use

=IF(I15>I25*2.7,0.2*(I15-(I25*2.7)),0)

7. ## Re: HELP... Commission of 20% if an employee makes more than 2.7 times their wages

Thank you very much, so easy when you know how.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1