# If Then Statement in Excel

1. ## 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

2. ## Re: If Then Statement in Excel

Hi!

See this:

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

Biff

"Scudang" <scudang@gmail.com> wrote in message
>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
>

3. ## Re: If Then Statement in Excel

On 15 Sep 2005 19:36:42 -0700, "Scudang" <scudang@gmail.com> 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

4. ## Re: If Then Statement in Excel

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" <scudang@gmail.com> wrote in message
>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
>

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1