Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Step Based Commission Formula

1. I agreed to these rules
Join Date
7th September 2006
Posts
8

## Step Based Commission Formula

I am having trouble with the code for this stepped scale commission structure.

Net Service
\$1,1400

Step Scale Comm. %
\$0-500 40%
\$500-750 45%
\$750-1000 47%
\$1000-9999.99 50%

Final Commission Paid \$ ?
Last edited by saint3232; September 7th, 2006 at 12:38.

Excel Video Tutorials / Excel Dashboards Reports

2. ## Re: step based commission formula

Are you looking for a formula?
if so
=IF(A1<500,A1*0.4,IF(A1<750,A1*0.45,IF(A1<1000,A1*0.47,IF(A1<=9999.99,A1*0.5,"Over \$10,000"))))

Ross

Excel Video Tutorials / Excel Dashboards Reports

3. I agreed to these rules
Join Date
7th September 2006
Posts
8

## Re: step based commission formula

Hey Ross,

Thanks a bunch. However I think there is supposed to be a another piece to the code. I am not sure I explained myself well. For example;

If a service provider makes \$1500 for the week and they are on a stepped scale then it would break out accordingly.

on the first \$500 They would be paid 40%
on the \$500 up to \$750 they would paid 45% (on that \$250)
on the \$750 up to \$1000 they would be paid 47% (on that \$250)
on the remaining amount above \$1000 up to \$9999.99 they would be paid at 50%.

I am including a line of code for a three tier commission structure so you can see what I am talking about. The only difference between the two types of tiers is the extra tier (47%)

=IF(C7<=1000,C7*0.45,IF(AND(C7>1000,C7<=1500),(450+(C7-1000)*0.5),IF(AND(C7>1500,C7<10000),450+250+(C7-1500)*0.6,"oops")))

Excel Video Tutorials / Excel Dashboards Reports

4. Super Moderator
Join Date
18th November 2004
Location
God's Own Country
Posts
4,678

## Re: step based commission formula

Hi,

=IF(C7<10000,IF((C7>1000)*(C7<10000),430+(C7-1000)*0.5,LOOKUP(C7,{0,500,750},{200,312.5,430})),"oops")

HTH

5. I agreed to these rules
Join Date
7th September 2006
Posts
8

## Re: step based commission formula

Hi HTH,

Thanks for the post. However that's not exactly what I am looking for. When I inserted the formula you supplied the answer did not come out right.
Here is what I have so far. I know that I am missing something simple. Could you take a look to see if you can figure out where I am going wrong?

=IF(C25<=500,C25*0.40,IF(AND(C25>500,C25<=750),(200+(C25-750)*0.45),IF(AND(C25>750,C25<=1000),200+113+(C25-1000)*0.47),IF(AND(C25>1000,C25<10000),200+113+113(C25-10000)*0.5,"oops"))))

Excel Video Tutorials / Excel Dashboards Reports

6. Super Moderator
Join Date
18th November 2004
Location
God's Own Country
Posts
4,678

## Re: step based commission formula

Hi,

Sorry! See this revised one.

=IF(C7<10000,40%*MIN(C7,500)+45%*MAX(MIN(C7-500,250),0)+47%*MAX(MIN(C7-750,250),0)+50%*MAX(C7-1000,0),"oops")

HTH

BTW, HTH=Hope This Helps!

7. ## Re: step based commission formula

8. I agreed to these rules
Join Date
7th September 2006
Posts
8

## Re: Step Based Commission Formula

It worked! Thank you very much. I appreciate your continued effort.
Thanks also for the "HTH" lingo. I am still a bit of a novice in this forum.

Excel Video Tutorials / Excel Dashboards Reports

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
•
porno