excel_2009
Active Member
- Joined
- Sep 14, 2009
- Messages
- 318
Hi excel gurus,
I have a bit of a dilemma in regards to an IF formula, here is a breakdown of the issue i am facing:
I have a commission structure:
£0-12,000 = 10%
£12,001 – £24,000 = 15%
£24,001+ = 20%
This is worked out on what we have done in the month – but isn’t as simple as one fixed percentage. Let me explain:
So If I bill £14,000 in one month, I earn 10% on the first £12,000 and 15% on the remaining £2,000. So I’ll get £1,500 commission. Or if I do £25,000 in one month, I’ll earn 10% on the first £12,000 (£1,200) plus 15% on the next 12,000 (£1,800) plus 20% on whatever I have left over £24,000 (£200). So all in all, I will have made £3,200.
The problem I have is that I can’t seem to do multiple “IF” formulas. I thought I had fixed it with =IF((J3*1)<12001,(J3*10%),IF(J3*1)<24001,(J3*15%)) but unfortunately it doesn't work as I cant seem to add a third IF rule.
Please can someone help me? perhaps there's a far more simpler method?
Thank you!
![Smile :) :)](data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7)
I have a bit of a dilemma in regards to an IF formula, here is a breakdown of the issue i am facing:
I have a commission structure:
£0-12,000 = 10%
£12,001 – £24,000 = 15%
£24,001+ = 20%
This is worked out on what we have done in the month – but isn’t as simple as one fixed percentage. Let me explain:
So If I bill £14,000 in one month, I earn 10% on the first £12,000 and 15% on the remaining £2,000. So I’ll get £1,500 commission. Or if I do £25,000 in one month, I’ll earn 10% on the first £12,000 (£1,200) plus 15% on the next 12,000 (£1,800) plus 20% on whatever I have left over £24,000 (£200). So all in all, I will have made £3,200.
The problem I have is that I can’t seem to do multiple “IF” formulas. I thought I had fixed it with =IF((J3*1)<12001,(J3*10%),IF(J3*1)<24001,(J3*15%)) but unfortunately it doesn't work as I cant seem to add a third IF rule.
Please can someone help me? perhaps there's a far more simpler method?
Thank you!