Need help with sliding commission scale formula

[HOTFLIP]

I am trying to set up a sliding commission scale for a nail salon. I really need help with the formula.

sales between $1-$300 = 40% commission
sales between $301-$500= 45% commission
sales between $501-$700= 50% commission
sales between $701-$900= 55% commission
sales from $900 & up= 60% commission

any help is greatly appreciated....Thank you in advance

 

[AhoyNC]

Here are two ways.
You could use an IF statement as in cell B1 below.
Or set up a rate table and use a LOOKUP function as in cell B2.

Change cell references to fit your data.

Excel Workbook
	A	B	C	D
1	250	100
2		100
3
4		Comm.	Rate
5		1	40%
6		301	45%
7		501	50%
8		701	55%
9		901	60%
10
Sheet1

 

[MARK858]

Or another option is the McGimpsey way. Column G uses the table and Column I uses a straight formula version.

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	income		threshold	rate	rate diff		commission A		commission B
2	300		0	0.4	0.4		120		120
3	301		300	0.45	0.05		120.45		120.45
4	1000		500	0.5	0.05		480		480
5			700	0.55	0.05
6			900	0.6	0.05
Sheet5