+ Reply to Thread
Results 1 to 2 of 2

Calculating Commissions

  1. #1
    Registered User
    Join Date
    03-24-2015
    Location
    SF, CA
    MS-Off Ver
    2010
    Posts
    1

    Calculating Commissions

    Hello, having some trouble figuring out how to do this calculation. Everything is setup.

    Two variables I need to take into account: Quota and Current Bookings.

    To calculate commission, we have 3 tiers, 0-60% of quota, 60-100% and >100%

    The rates are as follows
    0-60% of Quota, pays out 6.5%
    60-100% of Quota, pays out 14%
    >100% pays out 18%

    Say for example, you have a quota of $100,000 and current bookings of $150,000.
    0-60% of Quota ($60,000), pays out 6.5% ($3,900)
    60-100% of Quota ($60,000-100,000), pays out 14% ($5,600)
    >100% (>$100,000), pays out 18% ($9,000)

    Also, further complicated is that if you have over 75% of quota, you get $5000 bonus, if over 100% you get $10,000.

    Any help to create a formula for % of quota and % commission would be greatly appreciated. Thank you!!!

  2. #2
    Forum Contributor
    Join Date
    06-21-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    191

    Re: Calculating Commissions

    Assuming quota in in A, Bookings in B, then C would look like:
    =IF(B2/A2<=0.6,0.065*B2,IF(AND(B2/A2>0.6,B2/A2<=1),IF(B2/A2>=0.75,(B2*0.14)+5000,B2*0.14),(B2*0.18)+9000))

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Need help with a formula for calculating commissions
    By Rushmore in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-02-2015, 01:20 AM
  2. Calculating Commissions
    By COBBCITY1 in forum Excel General
    Replies: 12
    Last Post: 06-27-2010, 12:53 PM
  3. Excel 2007 : Calculating Total Commissions
    By newsgirl in forum Excel General
    Replies: 7
    Last Post: 12-09-2009, 04:54 PM
  4. Calculating Commissions for Different Pay Rates
    By ems.payroll in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-11-2008, 03:14 PM

Tags for this Thread

Bookmarks

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