If Then Statement in Excel

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

Hi!

See this:

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

Biff

"Scudang" <[email protected]> wrote in message
news:[email protected]...
>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
>

On 15 Sep 2005 19:36:42 -0700, "Scudang" <[email protected]> wrote:

>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

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)

--
Dana DeLouis
Win XP & Office 2003


"Scudang" <[email protected]> wrote in message
news:[email protected]...
>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
>