How to use list instead of multiple if statements on sales commission spreadsheet

[bythecshore]

I'm trying to design a formula for a commission spreadsheet and can't figure this out.

The way it works is that if the salesperson sells up to $60k he gets 15%. If he sells $60k to $65k, he gets 17%. If he sells $65k to $70k he gets 18%. And so on; there are 12 commission levels.

What I need to do is enter the sale amount in a cell and have it calculate the commission amount in a cell next to it. It should look up the commission percentage from a list that's in the spreadsheet, that lists the sales brackets and corresponding percentages.

I guess I could do this with multiple IF statements but that would be a nightmare (at least for me). There must be a simpler way to do this, right?

Thanks in advance.

 

[mse330]

Try the below formula ... You just need to set up your commission table

Book1
	A	B	C	D	E
1	Sales Amount	Commission		Sales Amount	Commission
2	$66,375.00	17%		$ 60,000.00	15%
3				$ 65,000.00	17%
4				$ 70,000.00	18%
Sheet1

Cell Formulas
Range	Formula
B2	=LOOKUP(A2,D2:D4,E2:E4)

 

[Dave Patton]

Excel 2010
	A	B	C	D	E
1	Sales Amount	Commission		Sales Amount	Commission
2	$50,000.00	7,500		0.00	15%
3				60,000.00	17%
4				65,000.00	18%
5
2a

Cell Formulas
Range	Formula
B2	=LOOKUP(A2,D2:D4,E2:E4)*A2

 

[jtakw]

Hi,

OP's description sounds more like a "Tiered" commission schedule:

Book1
	A	B	C	D	E	F
1	Sales Amount	Commission		Sales Amount	Commission	Delta
2	$62,000.00	9,340		0	15%	15%
3				60,001	17%	2%
4				65,001	18%	1%
5				70,001	19%	1%
6				75,001	20%	1%
Sheet294

Cell Formulas
Range	Formula
B2	=SUMPRODUCT((D2:D6)*(A2-D2:D6)*(F2:F6))

Add the rest of your Commission levels to Columns D and E, Column F is the "Delta", difference between current commission level compared to previous commission level.
Adjust range in formula to include complete table.

 

[bythecshore]

Thanks for the fast responses. The LOOKUP worked perfectly. (I didn't try the SUMPRODUCT because the other one worked right away, but thanks to you too.)

 

[mse330]

Glad to help & thanks for reporting back