If Then Statement in Excel

[Scudang]

I am looking for help with an If statement for Excel. I am trying to
build a commission calculator with a tiered payout schedule. This is
what the tiers will look like;

2% paid on all sales between $30000 and $70000
3% paid on all sales between $70000 and $120000
4.5% paid on all sales between $120000 and $150000
5.5% paid on all sales above $150000

Total sales will be found in cell C16

example
$100000 in sales would net;
2% paid on $40000 (sales between 30 and 70K)
+ 3% paid on $30000 (sales between 70 and 100K)

Any assistance would be greatly appreciated. Thank you in advance.

Anthony

 

[Biff]

Hi!

See this:

http://mcgimpsey.com/excel/variablerate.html

Biff

 

[Ron Rosenfeld]

I am looking for help with an If statement for Excel. I am trying to
build a commission calculator with a tiered payout schedule. This is
what the tiers will look like;

2% paid on all sales between $30000 and $70000
3% paid on all sales between $70000 and $120000
4.5% paid on all sales between $120000 and $150000
5.5% paid on all sales above $150000

Total sales will be found in cell C16

example
$100000 in sales would net;
2% paid on $40000 (sales between 30 and 70K)
+ 3% paid on $30000 (sales between 70 and 100K)

Any assistance would be greatly appreciated. Thank you in advance.

Anthony

Set up a table someplace on your sheet:

0 $0 0%
$30,000 $0 2%
$70,000 $800 3%
$120,000 $2,300 4.50%
$150,000 $3,650 5.50%

If the table is in E1:G5, the formula in F2 is:
=F1+G1*(E2-E1)
and copy/dragged down to F5. The other entries are manual.

NAME the table ComTbl.

Use this formula:

=VLOOKUP(A1,ComTbl,2)+
(A1-VLOOKUP(A1,ComTbl,1))*
VLOOKUP(A1,ComTbl,3)


--ron

 

[Dana DeLouis]

Just another option where "x" is your cell reference:

=MAX(0,2%*x-600,3%*x-1300,4.5%*x-3100,5.5%*x-4600)