# Commission Schedule

1. ## Commission Schedule

I need formula assistance so as to calculate the correct commission based on the following schedule:

\$0 - \$49,999 5%
\$50,000 - \$99,999 10%
\$100,000 - \$149,999 15%
\$150,000 - \$199,999 20%
\$200,000 - \$249,999 25%
\$250,000 - \$299,999 30%
\$300,000 - \$300,000 35%

Commission are retroactive to dollar one. For example, if sales for the first period are \$54,023.06 that would equate to a commission of \$5,402.31 calculated as such:

(49,999*.05) = \$2,500
(54,023.06 - 49,999) = \$4,023 then: 4,023 *.10 = \$402.31

Since the salesperson is in the 10% tier, I need to go back and add an additional 5% on all sales up to that point which would be an additional \$2,500 in this example. That would equate to (2,500 + 402.31 + 2,500) = \$5,402.31.

I'm having difficulty with the third period. It calculates 5% no matter where you are in the grid. Any help would be appreciated. File attached. Thx.

2. Suggest you simplify your table and use a VLOOKUP statement. The attached should give you some ideas.

3. ## Commission Schedule

Thanks Martin. I do understand the LOOKUP function, I'm actually trying to put together a formula for cells D24:G24 that would correctly calculate the commission to be paid based on whatever sales value is in cells D9:G9.

4. Hi

A couple more formulae for you to ponder. This time I've still gone with VLOOKUPs, but added SUM functions to pick off the correct value.

I've given you two different versions as I think I know what you're asking for - but just in case...

HTH

5. Hi
For the commission of , say Quarter1, use
=INDEX(\$F\$13:\$F\$19;MATCH(D7;\$D\$13:\$D\$19;1))*D7
and drag to the right for the other results
Cheers

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