Bonus calculation with certain criteria

Hi,

Please require help on this formula, new to the forum but the internet hasnt helped me find a solution or correct method.

e.g.
Person 1 has a salary of 10000, they generate revenue of 40000.
Criteria 1: if they earn 2.5x or above the salary but less than 3x the salary they earn 20% of the difference
Criteria 2: if they earn above 3x salary then 30% of difference in revenue and 3x salary.

e.g. 10000 salary, 40000 revenue
2.5x salary = 25000
3x salary = 30000

20% bonus is met as revenue exceeds 2.5x salary so = 5000 x 20% = 1000
30% bonus is met as revenue exceeds 3x salary so = 10000 x 30% = 3000 (as previous bonus for previous criteria met for 2.5x)

total bonus = 4000

I need a formula that if the criteria is not met, then flags up as a 0, for instance if under the 2.5x revenue then no bonus, if met the 2,5x criteria but not the 3x criteria then only bonus for that, if met 2.5x and 3 x then bonus for both.

I hope that makes sense!

See if this is what you want...

	A	B	C
1	salary	Revenue
2	10000	40000	3000
3	10000	30000	1000
4	10000	20000	0

C2=IF(B2>A2*3,(B2-(A2*3))*0.3,IF(B2>A2*2.5,(B2-(A2*2.5))*0.2,0))
copied down

I only have one issue. The total difference of the 20% and 30% bonus needs to be added together as a total bonus. The end answer is showing just the 30% bonus.

OK, I didnt catch that part...

=IF(B2>A2*3,1000+(B2-(A2*3))*0.3,IF(B2>A2*2.5,(B2-(A2*2.5))*0.2,0))

Rather than email, can you give me a dummy workbook here with a few samples at each "range"?
To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.