Need help to work out formula

[Lmaonade]

Hi,

I have this spreadsheet.

I want this cell to work out how much commission is calculated.

If the "GM this month" is equal or higher than the number in the 4 or 5 week month row (depending on whether the cell E19 is 4 or 5) then I want the cell to give me E20* 10% if it is Equal or higher than the 100% mark, 12.5% if equal or higher than the 125% mark, 15% if equal or higher than the 150% mark and 20% if equal or higher than the 200% mark. If it doesn't meet any of these targets then I'd want it to return 0.

For example: for the amount in the image (8400) I would want this to see that it's a 4 week month, and then see that it falls into the 100% colomn as it's above the 100% mark but below the 125% mark, so I'd want it to calculate 8400*10%

And another example: If it said 13000 and E19 was 5 I'd want it to return the value of 13000*15%

I'm not sure if this is possible. I'm not an expert by any means with formula.

I appreciate any help on this.

Thanks,
Andy

 

[wideboydixon]

This is what I came up with:

Book1
	C	D	E	F	G	H	I
5
6		Consultant Name:	Andy
7		Salary:	17000
8		Multiple	5
9
10
11		Monthly Calculator
12		Percentage of Target	100%	125%	150%	200%
13		Weekly	1700	2125	2550	3400
14		4 Week Month	6800	8500	10200	13600
15		5 Week Month	8500	10625	12750	17000
16		Quarterly	22100	27625	33150	44200
17		Annually	85000	106250	127500	170000
18
19		4 or 5 Week Month	4
20		GM This Month	8400
21		Commission Threshold Met	YES
22		Gross Commission Amount (If YTD Met)	840
23
Sheet1

Cell Formulas
Range	Formula
E21	=IF($E$20>=INDEX($E$11:$H$15,$E$19,1),"YES","NO")
E22	=IF($E$21="NO","",$E$20*CHOOSE(MATCH($E$20,INDEX($E$11:$H$15,$E$19,),1),0.1,0.125,0.15,0.2))

WBD

 

[Lmaonade]

That seems to work perfectly! Thank you so much!