+ Reply to Thread
Results 1 to 5 of 5

Help needed - IF Statements, trying to calculate commissions

  1. #1
    Registered User
    Join Date
    06-27-2013
    Location
    nyc, usa
    MS-Off Ver
    Excel 2003
    Posts
    3

    Help needed - IF Statements, trying to calculate commissions

    Hi,

    I am trying to calculate commissions at various levels and am new to IF statements, after trying several times I thought I had it solved, but I just cant get it to work properly.

    I just need a fully working example for one and I can do the rest!

    Can anyone help?

    Spreadsheet is attached.

    Basically I just want to get row 39 working properly, so for this instance we only calculate it using Rows 21,22,23.

    You take the fee billed (B26) and then for the various tiers you work out how much you earnt.

    Fee = $30,000

    The 1st tier; $0-$4,000 you get 0% on, so nothing.
    The 2nd tier; $4,001 - $12,000, you get 45% on. So $7,999 * 45% = $3,599.55
    Then you still left over $30,000 - $12,000 = $18,000 which gets calculated against the highest tier at 50% = $9,000

    so total comm's = $9,000 + $3599.55 = $12599.55

    but if your fee = 0, then comm's should = 0.

    Anyways I have tried heaps of different ways to get it to work, i got the highest tier working, then the second and even the third. but if i just put 0 i was getting a negative returned.

    Help please!
    Attached Files Attached Files

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,447

    Re: Help needed - IF Statements, trying to calculate commissions

    Hi and welcome to the forum

    A few of the things you say/show dont make any sense (to me at least)...
    The 2nd tier; $4,001 - $12,000, you get 45% on
    Yet your table shows 20% for this range? how do you get 45%?

    Also, I dont understand how the Threasholds column B:C fid into this?

    If you set the tables up properly, you should be able to use vlookup() instead of nested IF()'s
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    06-27-2013
    Location
    nyc, usa
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Help needed - IF Statements, trying to calculate commissions

    Hi,

    You are looking at rows 6-9, which i would try and solve in B36 - you can solve that if you want, but it is slightly more complicated.

    I was trying to solve B39, which you use Rows 21,22,23 for.

    The thresholds are basically over the course of the year as you earn money for the company, the commission you earn on the fee's you brought in goes up.

    So for the first $200k you earn the business you earn Tier 1 levels of comm's. Then $201k - $400k, you earn Tier 2 level comm's, and it increases again $401 - $500k, then aything over $500k at the highest tier.

    then within each Tier the comm's are calculated as per columns B & E, at the % in G. F is simply the amount between the levels to make it easier to calculate in a formula.

    does that make sense?

  4. #4
    Registered User
    Join Date
    06-27-2013
    Location
    nyc, usa
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Help needed - IF Statements, trying to calculate commissions

    if you think a vlookup is easier / works better please let me know as well, i am open to any solution as long as it works

    so yeah - i just want a working solution for B36, B37, B38 or B39. Any would be fine.

    thanks!

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,447

    Re: Help needed - IF Statements, trying to calculate commissions

    I will have to take a look at this a bit later, my time is up here for now

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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