# Sales Commission Formula

1. ## Sales Commission Formula

I am trying to build a spreadsheet for my sales reps for their commission but get stuck on the percentage formulas. in this specific case im looking to do the following

15 sales is monthly quota and should be 100% of the quota each sale is paid at \$50.00 each at 100% rate. if the sales rep only sells 5 they would be paid whatever the % of he \$ amount is of the 15 minimum quota. now if the sell anything over the 100% minimum quota the \$50.00 rate should change to reflect the increase over 100%. Any help would be greatly appreciated. Thanks

2. ## Re: Sales Commission Formula

should be able to do with an IF statement , but would need to know the full rules, or attach an example spreadsheet

something like
=if( cell with sales quantity <15 , cell with sales quantity/15 *50 , if ( cell with sales quantity > 15 , calculate the over 100% rate, 750))

3. ## Re: Sales Commission Formula

Try:

Formula:
`Please Login or Register  to view this content.`

Change the SalesQty and SalesTarget references as necessary.

4. ## Re: Sales Commission Formula

im sorry but im still alittle confused ill try to explain better.

15 is minimum sales quota (but equals 100% of their sales quota)
if they hit 15 sales they would be paid 100% of their commission for each one which is \$50.00 each.
if they fall below that quota for example they sold 7 i would need to figure out what % that 7 is of the 15 100% quota and adjust the pay to be that % of the \$50.00 ( so if it happens to be 20% they pay would adjust to be 20% of \$50.00 but if they sold 150% of their quota the pay would adjust to be 150% of \$50.00

i hope this better explains what im trying to do. thank you again for your help

5. ## Re: Sales Commission Formula

in your example
6 sales would be at 40% of \$50
so 50*40% = \$20
*6 sales = \$120
and

assuming 160%
ie sold 24 items

\$50 * 160% = \$80 per item
24 * \$80
= \$1920

if so , then , the formula by

OllyXLS

is correct ?

6. ## Re: Sales Commission Formula

i will try to put a spreadsheet together using that formula and see if it works. if not ill post my sheet here and hopefully you can help if you see exactly what im trying to do. thank you so much for your help

7. ## Re: Sales Commission Formula

ok so i think that worked but how can i show the percentage that the commission is being paid at? i dont know how to show the spreadsheet on this post but below is just a copy and paste.

monthly goal 15
Commission \$50.00
sales 16

Percentage Commission Paid At ???

Commission \$853.33

8. ## Re: Sales Commission Formula

sales quantity / sales target *100

16/15
=1.0666667
*100

=106.6666%

9. ## Re: Sales Commission Formula

that works great just one more question when i change the cell to show percentage it shows up like this 10667% . how do i fix that to show 106%

10. ## Re: Sales Commission Formula

dont multiply by 100 , that will fix it

#### Thread Information

##### Users Browsing this Thread

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